sql display milliseconds to date format

  • Hi,
    Anyone can help on sql how to display milliseconds to a date format?
    DateCreated
    -----------------
    1539453603609
    1539453605407
    1540436130996
    1542004320409
    ...
    ...

    I need an output format
    YYYYMMDD_HHMM

    Thank you in advanced;
    FSPalero

  • ferdie - Sunday, November 11, 2018 11:40 PM

    Hi,
    Anyone can help on sql how to display milliseconds to a date format?
    DateCreated
    -----------------
    1539453603609
    1539453605407
    1540436130996
    1542004320409
    ...
    ...

    I need an output format
    YYYYMMDD_HHMM

    Thank you in advanced;
    FSPalero

    You will have to divide the values in hours and milliseconds.
    😎

    DECLARE @BMS BIGINT = 1542004320409;
    DECLARE @bh INT  = @BMS / 3600000;
    DECLARE @ims INT  = @BMS % 3600000;
    SELECT DATEADD(MILLISECOND,@IMS,DATEADD(HOUR,@BH,CONVERT(DATETIME2(3),'19700101',112)));

    Note that I put 1530-01-01 as the initial date, no idea what that should be.
    Edit: not enough coffee that morning, corrected the calculation.

  • Eirikur Eiriksson - Monday, November 12, 2018 12:11 AM

    ferdie - Sunday, November 11, 2018 11:40 PM

    Hi,
    Anyone can help on sql how to display milliseconds to a date format?
    DateCreated
    -----------------
    1539453603609
    1539453605407
    1540436130996
    1542004320409
    ...
    ...

    I need an output format
    YYYYMMDD_HHMM

    Thank you in advanced;
    FSPalero

    You will have to divide the values in hours and milliseconds.
    😎

    DECLARE @BMS BIGINT = 1542004320409;
    DECLARE @bh INT  = @BMS / 360000;
    DECLARE @ims INT  = @BMS % 360000;
    SELECT DATEADD(MILLISECOND,@IMS,DATEADD(HOUR,@BH,CONVERT(DATETIME2(3),'15300101',112)));

    Note that I put 1530-01-01 as the initial date, no idea what that should be.

    Hi Eirikur,

    Appreciate your great help, but how can I applied this on may table "system_log" column "DateCreated"?

    Thank you very much

  • ferdie - Monday, November 12, 2018 12:24 AM

    Eirikur Eiriksson - Monday, November 12, 2018 12:11 AM

    ferdie - Sunday, November 11, 2018 11:40 PM

    Hi,
    Anyone can help on sql how to display milliseconds to a date format?
    DateCreated
    -----------------
    1539453603609
    1539453605407
    1540436130996
    1542004320409
    ...
    ...

    I need an output format
    YYYYMMDD_HHMM

    Thank you in advanced;
    FSPalero

    You will have to divide the values in hours and milliseconds.
    😎

    DECLARE @BMS BIGINT = 1542004320409;
    DECLARE @bh INT  = @BMS / 360000;
    DECLARE @ims INT  = @BMS % 360000;
    SELECT DATEADD(MILLISECOND,@IMS,DATEADD(HOUR,@BH,CONVERT(DATETIME2(3),'15300101',112)));

    Note that I put 1530-01-01 as the initial date, no idea what that should be.

    Hi Eirikur,

    Appreciate your great help, but how can I applied this on may table "system_log" column "DateCreated"?

    Thank you very much

    The format looks like a unix timestamp. Although the unix epoch is in "seconds" after Jan-1 -1970. Are you looking to convert from seconds to datetime format?

  • george_at_sql - Monday, November 12, 2018 4:00 AM

    ferdie - Monday, November 12, 2018 12:24 AM

    Eirikur Eiriksson - Monday, November 12, 2018 12:11 AM

    ferdie - Sunday, November 11, 2018 11:40 PM

    Hi,
    Anyone can help on sql how to display milliseconds to a date format?
    DateCreated
    -----------------
    1539453603609
    1539453605407
    1540436130996
    1542004320409
    ...
    ...

    I need an output format
    YYYYMMDD_HHMM

    Thank you in advanced;
    FSPalero

    You will have to divide the values in hours and milliseconds.
    😎

    DECLARE @BMS BIGINT = 1542004320409;
    DECLARE @bh INT  = @BMS / 360000;
    DECLARE @ims INT  = @BMS % 360000;
    SELECT DATEADD(MILLISECOND,@IMS,DATEADD(HOUR,@BH,CONVERT(DATETIME2(3),'15300101',112)));

    Note that I put 1530-01-01 as the initial date, no idea what that should be.

    Hi Eirikur,

    Appreciate your great help, but how can I applied this on may table "system_log" column "DateCreated"?

    Thank you very much

    The format looks like a unix timestamp. Although the unix epoch is in "seconds" after Jan-1 -1970. Are you looking to convert from seconds to datetime format?

    Actually, it's milliseconds after 1 Jan 1970 and the following will easily do the initial conversion thanks to the inherent temporal math capabilities of the DATETIME datatype.

    SELECT CONVERT(DATETIME,1539453603609/86400000.0)+'1970';

    The number 86400000.0 is the number of milliseconds in a day. (1000*60*60*24)

    If you're one of those folks that don't believe in the simplicity of direct temporal math, you could also do this based on the SQL Server epoch of 1 Jan 1900.

    SELECT DATEADD(yy,70,1539453603609/86400000.0) --70 is the number of years since 1 Jan 1900

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

  • Jeff Moden - Monday, November 12, 2018 5:43 AM

    george_at_sql - Monday, November 12, 2018 4:00 AM

    ferdie - Monday, November 12, 2018 12:24 AM

    Eirikur Eiriksson - Monday, November 12, 2018 12:11 AM

    ferdie - Sunday, November 11, 2018 11:40 PM

    Hi,
    Anyone can help on sql how to display milliseconds to a date format?
    DateCreated
    -----------------
    1539453603609
    1539453605407
    1540436130996
    1542004320409
    ...
    ...

    I need an output format
    YYYYMMDD_HHMM

    Thank you in advanced;
    FSPalero

    You will have to divide the values in hours and milliseconds.
    😎

    DECLARE @BMS BIGINT = 1542004320409;
    DECLARE @bh INT  = @BMS / 360000;
    DECLARE @ims INT  = @BMS % 360000;
    SELECT DATEADD(MILLISECOND,@IMS,DATEADD(HOUR,@BH,CONVERT(DATETIME2(3),'15300101',112)));

    Note that I put 1530-01-01 as the initial date, no idea what that should be.

    Hi Eirikur,

    Appreciate your great help, but how can I applied this on may table "system_log" column "DateCreated"?

    Thank you very much

    The format looks like a unix timestamp. Although the unix epoch is in "seconds" after Jan-1 -1970. Are you looking to convert from seconds to datetime format?

    Actually, it's milliseconds after 1 Jan 1970 and the following will easily do the initial conversion thanks to the inherent temporal math capabilities of the DATETIME datatype.

    SELECT CONVERT(DATETIME,1539453603609/86400000.0)+'1970';

    The number 86400000.0 is the number of milliseconds in a day. (1000*60*60*24)

    If you're one of those folks that don't believe in the simplicity of direct temporal math, you could also do this based on the SQL Server epoch of 1 Jan 1900.

    SELECT DATEADD(yy,70,1539453603609/86400000.0) --70 is the number of years since 1 Jan 1900

    Ooops, missed a zero, must drink more coffee 🙂
    😎

  • Eirikur Eiriksson - Monday, November 12, 2018 7:07 AM

    Jeff Moden - Monday, November 12, 2018 5:43 AM

    george_at_sql - Monday, November 12, 2018 4:00 AM

    ferdie - Monday, November 12, 2018 12:24 AM

    Eirikur Eiriksson - Monday, November 12, 2018 12:11 AM

    ferdie - Sunday, November 11, 2018 11:40 PM

    Hi,
    Anyone can help on sql how to display milliseconds to a date format?
    DateCreated
    -----------------
    1539453603609
    1539453605407
    1540436130996
    1542004320409
    ...
    ...

    I need an output format
    YYYYMMDD_HHMM

    Thank you in advanced;
    FSPalero

    You will have to divide the values in hours and milliseconds.
    😎

    DECLARE @BMS BIGINT = 1542004320409;
    DECLARE @bh INT  = @BMS / 360000;
    DECLARE @ims INT  = @BMS % 360000;
    SELECT DATEADD(MILLISECOND,@IMS,DATEADD(HOUR,@BH,CONVERT(DATETIME2(3),'15300101',112)));

    Note that I put 1530-01-01 as the initial date, no idea what that should be.

    Hi Eirikur,

    Appreciate your great help, but how can I applied this on may table "system_log" column "DateCreated"?

    Thank you very much

    The format looks like a unix timestamp. Although the unix epoch is in "seconds" after Jan-1 -1970. Are you looking to convert from seconds to datetime format?

    Actually, it's milliseconds after 1 Jan 1970 and the following will easily do the initial conversion thanks to the inherent temporal math capabilities of the DATETIME datatype.

    SELECT CONVERT(DATETIME,1539453603609/86400000.0)+'1970';

    The number 86400000.0 is the number of milliseconds in a day. (1000*60*60*24)

    If you're one of those folks that don't believe in the simplicity of direct temporal math, you could also do this based on the SQL Server epoch of 1 Jan 1900.

    SELECT DATEADD(yy,70,1539453603609/86400000.0) --70 is the number of years since 1 Jan 1900

    Ooops, missed a zero, must drink more coffee 🙂
    😎

    I missed it or you did?  If I did, where?  I'll fix it.

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

  • Eirikur Eiriksson - Monday, November 12, 2018 7:07 AM

    Jeff Moden - Monday, November 12, 2018 5:43 AM

    Actually, it's milliseconds after 1 Jan 1970 and the following will easily do the initial conversion thanks to the inherent temporal math capabilities of the DATETIME datatype.

    SELECT CONVERT(DATETIME,1539453603609/86400000.0)+'1970';

    The number 86400000.0 is the number of milliseconds in a day. (1000*60*60*24)

    If you're one of those folks that don't believe in the simplicity of direct temporal math, you could also do this based on the SQL Server epoch of 1 Jan 1900.

    SELECT DATEADD(yy,70,1539453603609/86400000.0) --70 is the number of years since 1 Jan 1900

    Ooops, missed a zero, must drink more coffee 🙂
    😎

    Don't know what time of precision is required but the datetime conversion adds one millisecond.
    😎

    DECLARE @BMS BIGINT = 1539453603609;
    DECLARE @bh INT  = @BMS / 3600000;
    DECLARE @ims INT = @BMS % 3600000;
    SELECT
      DATEADD(MILLISECOND,@IMS,DATEADD(HOUR,@BH,CONVERT(DATETIME2(3),'19700101',112))) AS TO_DATETIME2
      ,DATEADD(yy,70,@BMS/86400000.0)                 AS TO_DATETIME
    ;

    Output

    TO_DATETIME2             TO_DATETIME
    -----------------------  -----------------------
    2018-10-13 18:00:03.609  2018-10-13 18:00:03.610

    Hi Eirikur,

    Appreciate your great help, but how can I applied this on may table "system_log" column "DateCreated"?

    Thank you very much

    You can do that either via a view on top of the table or as a calculated column.

  • Jeff Moden - Monday, November 12, 2018 7:16 AM

    Eirikur Eiriksson - Monday, November 12, 2018 7:07 AM

    Ooops, missed a zero, must drink more coffee 🙂
    😎

    I missed it or you did?  If I did, where?  I'll fix it.

    My bad! I missed one zero, hence came up with the wrong initial date:pinch:
    😎

  • ferdie - Sunday, November 11, 2018 11:40 PM

    Hi,
    Anyone can help on sql how to display milliseconds to a date format?
    DateCreated
    -----------------
    1539453603609
    1539453605407
    1540436130996
    1542004320409
    ...
    ...

    I need an output format
    YYYYMMDD_HHMM

    Thank you in advanced;
    FSPalero

    Hopefully, this is just for presentation purposes and you won't actually store the information in any permanent table in the format you requested.

    This will do it if the column name for the original value actually is "DateCreated".

     SELECT DateCreated = REPLACE(REPLACE(REPLACE(
                            CONVERT(CHAR(16),DATEADD(yy,70,DateCreated/86400000.0),121) COLLATE Latin1_General_BIN
                          ,'-',''),':',''),' ','_')
       FROM dbo.YourTable
    ;

    The COLLATE clause can significantly increase the performance of the already fast nested REPLACEs, depending on what your current default collation is.

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

  • Eirikur Eiriksson - Monday, November 12, 2018 7:07 AM

    Jeff Moden - Monday, November 12, 2018 5:43 AM

    george_at_sql - Monday, November 12, 2018 4:00 AM

    ferdie - Monday, November 12, 2018 12:24 AM

    Eirikur Eiriksson - Monday, November 12, 2018 12:11 AM

    ferdie - Sunday, November 11, 2018 11:40 PM

    Hi,
    Anyone can help on sql how to display milliseconds to a date format?
    DateCreated
    -----------------
    1539453603609
    1539453605407
    1540436130996
    1542004320409
    ...
    ...

    I need an output format
    YYYYMMDD_HHMM

    Thank you in advanced;
    FSPalero

    You will have to divide the values in hours and milliseconds.
    😎

    DECLARE @BMS BIGINT = 1542004320409;
    DECLARE @bh INT  = @BMS / 360000;
    DECLARE @ims INT  = @BMS % 360000;
    SELECT DATEADD(MILLISECOND,@IMS,DATEADD(HOUR,@BH,CONVERT(DATETIME2(3),'15300101',112)));

    Note that I put 1530-01-01 as the initial date, no idea what that should be.

    Hi Eirikur,

    Appreciate your great help, but how can I applied this on may table "system_log" column "DateCreated"?

    Thank you very much

    The format looks like a unix timestamp. Although the unix epoch is in "seconds" after Jan-1 -1970. Are you looking to convert from seconds to datetime format?

    Actually, it's milliseconds after 1 Jan 1970 and the following will easily do the initial conversion thanks to the inherent temporal math capabilities of the DATETIME datatype.

    SELECT CONVERT(DATETIME,1539453603609/86400000.0)+'1970';

    The number 86400000.0 is the number of milliseconds in a day. (1000*60*60*24)

    If you're one of those folks that don't believe in the simplicity of direct temporal math, you could also do this based on the SQL Server epoch of 1 Jan 1900.

    SELECT DATEADD(yy,70,1539453603609/86400000.0) --70 is the number of years since 1 Jan 1900

    Ooops, missed a zero, must drink more coffee 🙂
    😎

    Hi Eirikur,

    Excellent! it works....thanks for the helps.

    Cheer's Mate,
    FSPalero

Viewing 11 posts - 1 through 10 (of 10 total)

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