Solved

Where clasue with julian date or convert julian date to date time

Posted on 2012-04-12
4
404 Views
Last Modified: 2012-04-13
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!
0
Comment
Question by:wiggy353
  • 2
4 Comments
 
LVL 23

Expert Comment

by:wdosanjos
ID: 37838994
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.
0
 
LVL 14

Expert Comment

by:nishant joshi
ID: 37839045
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

Open in new window

0
 
LVL 23

Accepted Solution

by:
wdosanjos earned 500 total points
ID: 37839072
Sorry I take that back.  It seems there are two types of Julian Dates and the one you are referring to is the Astronomy one.  If that's the case, the following SQL should do the trick:
select *
from table1
where ((juliandate - round(juliandate, 0, 1)) * 24) between 6.0 and 16.0

Open in new window


Julian day
http://en.wikipedia.org/wiki/Julian_day
0
 
LVL 1

Author Closing Comment

by:wiggy353
ID: 37842733
Perfect, thanks!
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question