wiggy353
asked on
Where clasue with julian date or convert julian date to date time
I have a user that need a report that pulls transactions between a certain time, say 0600 - 1600. The problem I have is that the date/time in the table is julian date. How can I select records just within those hours? Do I need to convert the julian date to date/time?
For example, here is one of the julian dates in the table: 2456020.5005
I would want to run a query similar to:
select *
from table1
where juliandate between 0600 and 1600
Thanks!
For example, here is one of the julian dates in the table: 2456020.5005
I would want to run a query similar to:
select *
from table1
where juliandate between 0600 and 1600
Thanks!
2456020.5005 does not seem to be a Julian Date. Julian Dates are in the format YYDDD or YYYYJJJ, where YY or YYYY is the year and JJJ is the number of days since the beginning of the year.
below is a function to find date enter your date and find using between within hours,day,...
ALTER FUNCTION [dbo].[fnConvertJulianToDate]
(
@JD DECIMAL(18,5)
)
RETURNS DATETIME
AS
BEGIN
DECLARE @j INT
DECLARE @g INT
DECLARE @dg INT
DECLARE @c INT
DECLARE @dc INT
DECLARE @b INT
DECLARE @db INT
DECLARE @a2 INT
DECLARE @da INT
DECLARE @y2 INT
DECLARE @m2 INT
DECLARE @d INT
DECLARE @year INT
DECLARE @month INT
DECLARE @day INT
DECLARE @hr INT
DECLARE @min INT
DECLARE @sec INT
DECLARE @frac DECIMAL(18,5)
DECLARE @f DECIMAL(18,2)
DECLARE @finalDateTime DATETIME
SET @frac = @JD - FLOOR(@JD) + 0.5
IF(@frac >= 1.0)
BEGIN
SET @frac = @frac - 1.0
SET @JD = @JD + 1.0
END
SET @j = FLOOR(@JD) + 32044
SET @g = @j / 146097
SET @dg = @j % 146097
SET @c = (@dg / 36524 + 1) * 3 / 4
SET @dc = @dg - @c * 36524
SET @b = @dc / 1461
SET @db = @dc % 1461
SET @a2 = (@db / 365 + 1) * 3 / 4
SET @da = @db - @a2 * 365
SET @y2 = @g * 400 + @c * 100 + @b * 4 + @a2
SET @m2 = (@da * 5 + 308) / 153 - 2
SET @d = @da - (@m2 + 4) * 153 / 5 + 122
SET @year = @y2 - 4800 + (@m2 + 2) / 12
SET @month = (@m2 + 2) % 12 + 1
SET @day = @d + 1.5
SET @hr = FLOOR(@frac * 24.00)
SET @min = FLOOR((@frac*24.00 - @hr)*60.00)
SET @f = ((@frac*24.00 - @hr)*60.00 - @min)*60.00
SET @sec = FLOOR(@f)
SET @f = @f - @sec
IF( @f > 0.5 )
BEGIN
SET @sec = @sec + 1
END
SET @finalDateTime = CONVERT(VARCHAR(20), CAST(@month AS VARCHAR(2))
+ '/'
+ CAST(@day AS VARCHAR(2))
+ '/'
+ CAST(@year AS VARCHAR(4))
+ ' '
+ CAST(@hr AS VARCHAR(2))
+ ':'
+ CAST(@min AS VARCHAR(2))
+ ':'
+ CAST(@sec AS VARCHAR(2)))
RETURN @finalDateTime
END
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect, thanks!