Days and Time

  • 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

  • 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

    ------------------------------------------------------------------------
    Bite-sized fiction (with added teeth) [/url]

  • 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.

  • 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

  • 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)))

  • 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.

  • 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..."

  • 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