February 23, 2009 at 8:35 am
I've been given some odd data and was wondering if anyone has had any experience converting things like this normal values? Here is an example of the two that I am looking at:
2009044 - I have been told that this equals the 44th day of 2009.
55228 - This number is the seconds past midnight.
Does anyone have any functions or TSQL that can easily translate these into normal dates/times?
Thanks
February 23, 2009 at 8:44 am
Hi, the code below should give you some ideas at least:
declare @dint int set @dint = 2009044
declare @secs int set @secs = 55228
declare @datenotime datetime
declare @datewithtime datetime
declare @days int set @days = cast(right(cast(@dint as char(7)), 3) as int)
select @days
set @datenotime = dateadd(dd, @days, cast(cast(@dint/1000 as char(4)) + '-01-01 00:00:00.000' as datetime))
select @datenotime
set @datewithtime = dateadd(ss, @secs, @datenotime)
select @datewithtime
February 23, 2009 at 8:47 am
Here are a couple of threads that discuss converting julian dates to regular dates:
http://204.9.76.233/Community/forums/p/20619/114975.aspx
http://www.sqlservercentral.com/Forums/Topic438807-8-1.aspx
Assuming you want to create a true datetime value from the date and time portions then once you have the date (which will have a time of midnight), based on the above threads, you just need to do a DateAdd(seconds, [seconds_column], [date created in step one]) to get the datetime value.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 23, 2009 at 8:47 am
Use DateAdd. Like this:
declare @String char(7), @Year char(4), @Day int
select @String = '2009044'
select @Year = left(@String, 4), @Day = right(@String, 3)
select dateadd(day, @Day, '1/1/' + @Year)
You can use seconds in DateAdd too, which will solve your other one.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 23, 2009 at 8:49 am
Seconds past midnight is pretty common and relatively easily handled:
DATEADD(Second,55228,'1/1/1900') --assuming 1/1/1900 as a base date
I have never seen the other date format used, but I suppose it could be parsed:
DECLARE @MyDateStr VARCHAR(7)
SET @MyDateStr = '2009034'
SELECT DATEADD(Day,CONVERT(INT,SUBSTRING(@MyDateStr,5,LEN(@MyDateStr))),CONVERT(DATETIME,'1/1/' + LEFT(@MyDateStr,4)))
February 23, 2009 at 11:41 am
Given that January 1st is the first ordinal day of a year, the example TSQL scripts above are 1 day out.
If the date and time fields are stored as integers here is a method of converting to the datetime data type that avoids string manipulation, and therefore should be more efficient.
DECLARE @ordinalDate int
DECLARE @sec int
SELECT @ordinalDate = 2009044, @sec = 55228
SELECT DATEADD(second, @sec,
DATEADD(dy, @ordinalDate % 1000,
DATEADD(year, @ordinalDate / 1000 - 1900, -1)))
EDIT: Corrected terminology - changed Julian date to Ordinal date.
February 23, 2009 at 12:22 pm
select
[Date] = dateadd(year,(Ordinal_Date/1000)-1900,((Ordinal_Date%1000)-1)) ,
[Time_of_Day] = dateadd(ss,Second_of_Day,0)
from
( -- Test Data
select Ordinal_Date = 2008044, Second_of_Day = 55228
) a
Date Time_of_Day
----------------------- -----------------------
2008-02-13 00:00:00.000 1900-01-01 15:20:28.000
(1 row(s) affected)
FYI, a date in the format YYYYDDD is not a Julian Date, it is an Ordinal Date.
http://en.wikipedia.org/wiki/Julian_Day
"The Julian day or Julian day number (JDN) is the number of days that have elapsed since 12 noon Greenwich Mean Time (UT or TT) on Monday, January 1, 4713 BC in the proleptic Julian calendar 1. That day is counted as Julian day zero. The Julian day system was intended to provide astronomers with a single system of dates that could be used when working with different calendars and to unify different historical chronologies.
...
The term Julian date is also used to refer to:
Julian calendar dates
ordinal dates (day-of-year)
The use of Julian date to refer to the day-of-year (ordinal date) is usually considered to be incorrect..."
February 23, 2009 at 12:40 pm
The use of Julian date to refer to the day-of-year (ordinal date) is usually considered to be incorrect...
Quite right - apologies for my sloppy terminology.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply