Convert bigint to datetime
Does this work for you? It returns 30-8-2010 13:21:42 at the moment on SQL Server 2005:
select dateadd(s, convert(bigint, 1283174502729) / 1000, convert(datetime, '1-1-1970 00:00:00'))
I've divided by 1000 because the dateadd function won't work with a number that large. So you do lose a little precision, but it is much simpler to use.
Convert Bigint to Datetime SQL Server
For your example you would need to do something like this.
select convert(datetime, convert(char(8), 20190108))
I can't for the life of me figure out what you are trying to do with your DATEADD logic there.
How can I convert bigint (UNIX timestamp) to datetime in SQL Server?
This worked for me:
Select
dateadd(S, [unixtime], '1970-01-01')
From [Table]
In case any one wonders why 1970-01-01, This is called Epoch time.
Below is a quote from Wikipedia:
The number of seconds that have elapsed since 00:00:00 Coordinated Universal Time (UTC), Thursday, 1 January 1970,[1][note 1] not counting leap seconds.
The Year 2038 Problem
Furthermore the DataAdd function takes an int
for the seconds to add. So if you are trying to add more than 2147483647
seconds you'll get an arithmetic overflow error. To resolve this issue you can break the addition into two calls to DateAdd one for the years and one for the remaining seconds.
Declare @t as bigint = 4147483645
Select (@t / @oneyear) -- Years to add
Select (@t % @oneyear) -- Remaining seconds to add
-- Get Date given a timestamp @t
Declare @oneyear as int = 31622400
Select DateAdd(SECOND, @t % @oneyear, DateAdd(YEAR, @t / @oneyear, '1970-01-01'))
This will let you convert a timestamp that represents a year greater than 2038.
Conversion of bigint to date only in SQL Server?
You can use CAST
to DATE
like following.
SELECT Cast(Dateadd(second, Cast(sr.executed_on AS BIGINT) / 1000, '19700101')
AS DATE)
AS ExecutedOn
FROM table1
Converting bigint to datetime in sqlserver
This isn't pretty, but I would CONVERT
it to a varchar
, inject some of the needed characters, and then CONVERT
to a datetime
(as you have 5 decimal places, I assume it's actually meant to be a datetime2(5)
):
DECLARE @starttime as bigint;
SET @starttime = 2021021209295600000;
SELECT CONVERT(datetime2(5),STUFF(STUFF(STUFF(STUFF(STUFF(STUFF(CONVERT(varchar(20),@starttime),15,0,'.'),13,0,':'),11,0,':'),9,0,'T'),7,0,'-'),5,0,'-'));
Javascript Convert BigInt to Datetime and Format
With that timestamp you could just use regular number, it's size is 15 and with millisecond persision you only use size 13.
Here's some example code that might work for you:
const time = 1630017759934;
const timeInSeconds = Math.floor(time/1000);
const d = new date(timeInSeconds);
const dateFormatted = `${date.getFullYear()}${(date.getMonth() + 1).toString().padStart(2, '0')}${date.getDate().toString().padStart(2, '0')}`;
How to convert BIGINT to DATE in Redshift?
You just want the to_date()
part:
select to_date(date_column::text, 'YYYYMMDD')
Explicit conversion of bigint to date
Have you considered using IsDate(MyFieldValue) to check for valid dates, rather than parsing out over several case statements to check for date validity? That may make the process a little cleaner and easier to understand and debug, and may help you get the output you need. I just quick-checked and it works "as is" with values formatted like those you've shared.
Related Topics
SQL Table Aliases - Good or Bad
Pros/Cons of Storing Serialized Hash VS. Key/Value Database Object in Activerecord
How to Handle Table Column Named with Reserved SQL Keyword
How to Use a Ring Data Structure in Window Functions
SQL Server Displaying Missing Dates
With (Nolock) VS Set Transaction Isolation Level Read Uncommitted
Can SQLite Handle 90 Million Records
SQL Update to the Sum of Its Joined Values
Work Around SQL Server Maximum Columns Limit 1024 and 8Kb Record Size
Truncate Table Within Transaction
How to Get the Last Day of Month in Postgres
Best Way in MySQL or Rails to Get Avg Per Day Within a Specific Date Range
Oracle - Update Join - Non Key-Preserved Table
Issue When Comparing Result of To_Char(Mydate, 'Day') to a String
Function Return Sys_Refcursor Call from SQL with Specific Columns
Best Way to Store Working Hours and Query It Efficiently
How to Use Multiple with Statements in One Postgresql Query
How to Delete Rows in Tables That Contain Foreign Keys to Other Tables