Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2012-04-12
4
Medium Priority
?
418 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

Cloud Training Guides

FREE GUIDES: In-depth and hand-crafted Linux, AWS, OpenStack, DevOps, Azure, and Cloud training guides created by Linux Academy instructors and the community.

Question has a verified solution.

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

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 …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

688 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