Solved

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

Posted on 2012-04-12
4
398 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel conversion issue with Sql server 14 46
all records from previous month 6 45
Oracle Pivot Question 8 43
Help Extract Specific in SQL 8 16
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
A short film showing how OnPage and Connectwise integration works.
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…

930 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now