Post reply

Converting a date

  • April 19, 2010 at 12:08 am

    #1153600

    I'm now getting that incorrect syntax near the keyword 'GROUP' again.

    Thanks,

    Kris

  • April 19, 2010 at 12:30 am

    #1153606

    Kris-155042 (4/18/2010)

    The data type is bigint and does go down to the seconds. I've run that script and I now get no results.

    Sorry to be such a pain but I'm really struggling with this bigint thimg

    In order to figure this out, we're going to need an example of a BIGINT datetime and its exact match for a real DATETIME so we can figure out the correct BASE DATE to use for the conversions.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • April 19, 2010 at 12:40 am

    #1153608

    Nabha (4/18/2010)

    Yes, sorry, completely off the track. I dint know about timestamp clearly. And it looks like its down to 'seconds' that you are suggesting. Thanks Jeff.

    No problem. I think you're on to it now with...

    convert(bigint, datediff(ss, '1970-01-01', getdate()))

    ... but I think you need to multiply that answer times 1000 to put it in the same range as what we found in the original post. If we take one of the numbers from the original post and convert it to a DATETIME, I believe you'll see what I'm saying...

    SELECT DATEADD(ss,1267575553839/1000,'19700101')

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • April 19, 2010 at 1:01 am

    #1153617

    Bits and pieces here and there makes it sound like we are dealing with MySQL on Unix/Linux.

  • April 19, 2010 at 1:31 am

    #1153624

    This is the date when done on the 19th April 2010 at 17:29pm

    wo.COMPLETEDTIME <= 1271685599935

    Hope this helps

    Thanks,

    Kris

  • April 19, 2010 at 7:15 am

    #1153793

    EDITED: Ooops. Missed page 2 so my post isn't relevant.

  • April 19, 2010 at 8:33 am

    #1153874

    Kris-155042 (4/19/2010)

    This is the date when done on the 19th April 2010 at 17:29pm

    wo.COMPLETEDTIME <= 1271685599935

    Hope this helps

    My guess is that COMPLETEDTIME represents the difference in milliseconds from 1970-01-01 00:00:00.000. The code below produces a time on April 19 before 17:29. That is a very common way to represent dates in UNIX and Java.

    It is also possible that is is a UTC time with offset in milliseconds from 1970-01-01. You haven't really supplied enough data for us to figure out exactly how it is calculated.

    select [SQL Date] =

    dateadd(ms,convert(bigint,a.DT)%86400000,dateadd(dd,a.DT/86400000,'19700101'))

    from

    ( -- Test Data

    select DT = 1271685599935

    ) a

    Results:

    SQL Date

    -------------------------

    2010-04-19 13:59:59.937

    (1 row(s) affected)

  • April 19, 2010 at 8:46 am

    #1153887

    That looks great. Thanks. How would I put that in my script? Sorry to be so simple

    Thanks,

    Kris

  • April 19, 2010 at 9:14 am

    #1153909

    Kris-155042 (4/19/2010)

    That looks great. Thanks. How would I put that in my script? Sorry to be so simple

    First, you need to figure out what datetime you actually want your query to use. It isn't really obvious from the code you supplied.

    Then you can use the DATEDIFF function to find the difference in milliseconds between that time and 1970-01-01 00:00:00.00, and then use that number in the where clause of your query.

    I would also suggest creating user defined scalar functions to convert to and from the two different time formats to make future work easier.

    -- Convert to SQL Date and Back to UNIX Date

    select

    [Diff MS] = convert(int,DT-[UNIX Date]),

    *

    from

    (

    select

    DT,

    [UNIX Date] =

    convert(bigint,datediff(dd,'19700101',[SQL Date]))*86400000+

    datediff(ms,dateadd(dd,datediff(dd,0,[SQL Date]),0),[SQL Date]),

    [SQL Date]

    from

    (

    select

    *,

    [SQL Date] =

    dateadd(ms,convert(bigint,a.DT)%86400000,dateadd(dd,a.DT/86400000,'19700101'))

    from

    ( -- Test Unix Date

    select DT = 1271685599935

    ) a

    ) aa ) aaa

    Results:

    Diff MS DT UNIX Date SQL Date

    ----------- --------------- -------------------- -----------------------

    -1 1271685599935 1271685599936 2010-04-19 13:59:59.937

    (1 row(s) affected)

  • April 19, 2010 at 9:25 am

    #1153919

    ok. I'm trying to get the current date. Any jobs closed today etc it changes day to day.

    Thanks,

    Kris

  • April 19, 2010 at 11:05 am

    #1154004

    Jeff Moden (4/19/2010)

    Nabha (4/18/2010)

    Yes, sorry, completely off the track. I dint know about timestamp clearly. And it looks like its down to 'seconds' that you are suggesting. Thanks Jeff.

    No problem. I think you're on to it now with...

    convert(bigint, datediff(ss, '1970-01-01', getdate()))

    ... but I think you need to multiply that answer times 1000 to put it in the same range as what we found in the original post. If we take one of the numbers from the original post and convert it to a DATETIME, I believe you'll see what I'm saying...

    SELECT DATEADD(ss,1267575553839/1000,'19700101')

    Ah! that makes sense, yes thanks Jeff. But again i think we are beaten by the OP as what he says does not fit into what we understand

    This is the date when done on the 19th April 2010 at 17:29pm

    wo.COMPLETEDTIME <= 1271685599935

    Hope this helps

    --------------------------------------------------------------------------------

    Thanks,

    Kris

    Edit: ah! I missed the third page sorry 🙂 (reading it now what Mike has posted)

    ---------------------------------------------------------------------------------

  • April 19, 2010 at 7:44 pm

    #1154243

    I only require the current date so 20/04/2010. I don't need the time or seconds at all. Ultimately the result of the query is just a number. So simply put how many jobs were closed today = 12 (for example)

    Thanks,

    Kris

  • April 22, 2010 at 3:58 am

    #1155950

    Just to let you know I found the solution as shown below. Thanks for everyones help

    /*display the amount of problems that have been open today*/

    SELECT count(*)

    FROM WorkOrder_Threaded wot INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID LEFT JOIN ModeDefinition mdd

    ON wo.MODEID=mdd.MODEID LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau

    ON sdu.USERID=aau.USER_ID LEFT JOIN DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID LEFT JOIN WorkOrderStates wos

    ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID

    WHERE (((wo.CREATEDTIME >= CAST(DATEDIFF(S, '19700101', CONVERT(varchar,GETDATE(),112)) AS BIGINT) * 1000 - 36000000

    AND wo.CREATEDTIME < CAST(DATEDIFF(S, '19700101', CONVERT(varchar,GETDATE()+1,112)) AS BIGINT) * 1000 - 36000000)))

    AND (((wo.CREATEDTIME != 0) AND (wo.CREATEDTIME IS NOT NULL)) AND (wo.CREATEDTIME != -1)) AND wot.THD_WOID=wot.WORKORDERID ORDER BY 1

    Thanks,

    Kris

Viewing 13 posts - 16 through 27 (of 27 total)

You must be logged in to reply to this topic. Login to reply