sql display milliseconds to date format

• ferdie

  SSC Enthusiast

  Points: 186

  More actions

  November 11, 2018 at 11:40 pm

  #409303

  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

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  November 12, 2018 at 12:11 am

  #2013183

  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.

• ferdie

  SSC Enthusiast

  Points: 186

  More actions

  November 12, 2018 at 12:24 am

  #2013184

  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

• george_at_sql

  SSCommitted

  Points: 1967

  More actions

  November 12, 2018 at 4:00 am

  #2013190

  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?

• Jeff Moden

  SSC Guru

  Points: 1003863

  More actions

  November 12, 2018 at 5:43 am

  #2013198

  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)
  

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  November 12, 2018 at 7:07 am

  #2013213

  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 🙂
  😎

• Jeff Moden

  SSC Guru

  Points: 1003863

  More actions

  November 12, 2018 at 7:16 am

  #2013214

  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

  SSC Guru

  Points: 182882

  More actions

  November 12, 2018 at 7:30 am

  #2013216

  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.

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  November 12, 2018 at 7:33 am

  #2013217

  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:
  😎

• Jeff Moden

  SSC Guru

  Points: 1003863

  More actions

  November 12, 2018 at 8:36 am

  #2013223

  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)
  

• ferdie

  SSC Enthusiast

  Points: 186

  More actions

  November 12, 2018 at 6:23 pm

  #2013285

  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