November 11, 2018 at 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
November 12, 2018 at 12:11 am
ferdie - Sunday, November 11, 2018 11:40 PMHi,
Anyone can help on sql how to display milliseconds to a date format?
DateCreated
-----------------
1539453603609
1539453605407
1540436130996
1542004320409
...
...I need an output format
YYYYMMDD_HHMMThank 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.
November 12, 2018 at 12:24 am
Eirikur Eiriksson - Monday, November 12, 2018 12:11 AMferdie - Sunday, November 11, 2018 11:40 PMHi,
Anyone can help on sql how to display milliseconds to a date format?
DateCreated
-----------------
1539453603609
1539453605407
1540436130996
1542004320409
...
...I need an output format
YYYYMMDD_HHMMThank you in advanced;
FSPaleroYou 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
November 12, 2018 at 4:00 am
ferdie - Monday, November 12, 2018 12:24 AMEirikur Eiriksson - Monday, November 12, 2018 12:11 AMferdie - Sunday, November 11, 2018 11:40 PMHi,
Anyone can help on sql how to display milliseconds to a date format?
DateCreated
-----------------
1539453603609
1539453605407
1540436130996
1542004320409
...
...I need an output format
YYYYMMDD_HHMMThank you in advanced;
FSPaleroYou 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?
November 12, 2018 at 5:43 am
george_at_sql - Monday, November 12, 2018 4:00 AMferdie - Monday, November 12, 2018 12:24 AMEirikur Eiriksson - Monday, November 12, 2018 12:11 AMferdie - Sunday, November 11, 2018 11:40 PMHi,
Anyone can help on sql how to display milliseconds to a date format?
DateCreated
-----------------
1539453603609
1539453605407
1540436130996
1542004320409
...
...I need an output format
YYYYMMDD_HHMMThank you in advanced;
FSPaleroYou 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
Change is inevitable... Change for the better is not.
November 12, 2018 at 7:07 am
Jeff Moden - Monday, November 12, 2018 5:43 AMgeorge_at_sql - Monday, November 12, 2018 4:00 AMferdie - Monday, November 12, 2018 12:24 AMEirikur Eiriksson - Monday, November 12, 2018 12:11 AMferdie - Sunday, November 11, 2018 11:40 PMHi,
Anyone can help on sql how to display milliseconds to a date format?
DateCreated
-----------------
1539453603609
1539453605407
1540436130996
1542004320409
...
...I need an output format
YYYYMMDD_HHMMThank you in advanced;
FSPaleroYou 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 🙂
😎
November 12, 2018 at 7:16 am
Eirikur Eiriksson - Monday, November 12, 2018 7:07 AMJeff Moden - Monday, November 12, 2018 5:43 AMgeorge_at_sql - Monday, November 12, 2018 4:00 AMferdie - Monday, November 12, 2018 12:24 AMEirikur Eiriksson - Monday, November 12, 2018 12:11 AMferdie - Sunday, November 11, 2018 11:40 PMHi,
Anyone can help on sql how to display milliseconds to a date format?
DateCreated
-----------------
1539453603609
1539453605407
1540436130996
1542004320409
...
...I need an output format
YYYYMMDD_HHMMThank you in advanced;
FSPaleroYou 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
Change is inevitable... Change for the better is not.
November 12, 2018 at 7:30 am
Eirikur Eiriksson - Monday, November 12, 2018 7:07 AMJeff Moden - Monday, November 12, 2018 5:43 AMActually, 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.
November 12, 2018 at 7:33 am
Jeff Moden - Monday, November 12, 2018 7:16 AMEirikur Eiriksson - Monday, November 12, 2018 7:07 AMOoops, 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:
😎
November 12, 2018 at 8:36 am
ferdie - Sunday, November 11, 2018 11:40 PMHi,
Anyone can help on sql how to display milliseconds to a date format?
DateCreated
-----------------
1539453603609
1539453605407
1540436130996
1542004320409
...
...I need an output format
YYYYMMDD_HHMMThank 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
Change is inevitable... Change for the better is not.
November 12, 2018 at 6:23 pm
Eirikur Eiriksson - Monday, November 12, 2018 7:07 AMJeff Moden - Monday, November 12, 2018 5:43 AMgeorge_at_sql - Monday, November 12, 2018 4:00 AMferdie - Monday, November 12, 2018 12:24 AMEirikur Eiriksson - Monday, November 12, 2018 12:11 AMferdie - Sunday, November 11, 2018 11:40 PMHi,
Anyone can help on sql how to display milliseconds to a date format?
DateCreated
-----------------
1539453603609
1539453605407
1540436130996
1542004320409
...
...I need an output format
YYYYMMDD_HHMMThank you in advanced;
FSPaleroYou 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