Menu
I have a problem in converting the Unix timestamp to sql server timestamp.
I have a data in excel sheet and I will import that data through a tool. So I am looking for a code or syntax which can convert that Epoch timestamp to sql server timestamp.
I have 3 different columns with the same format. How can I change the values in those columns.
For Example:
422
Convert from epoch to human-readable dateString date = new java.text.SimpleDateFormat("MM/dd/yyyy HH:mm:ss").format(new java.util.Date (epoch*1000)); Epoch in seconds, remove '*1000' for milliseconds. myString := DateTimeToStr(UnixToDateTime(Epoch)); Where Epoch is a signed integer. Replace 1526357743 with epoch.
Because our Epoch time is specified in milliseconds, we may convert it to seconds. To convert milliseconds to seconds, first, divide the millisecond count by 1000. Later, we use DATEADD() to add the number of seconds since the epoch, which is January 1, 1970 and cast the result to retrieve the date since the epoch.
To get epoch time, we will define our start_endtime as: '1970-01-01 00:00:00' and our part parameter as s (to get epoch time in seconds). After this, we convert our result to BIGINT datatype using CAST().
use strict; use warnings; use Time::Piece; my $datestring = '07-06-2019 21:13:00'; my $time = localtime->strptime($datestring, '%d-%m-%Y %H:%M:%S'); my $epoch = $time->epoch; ... my $time = localtime($epoch); my $datestring = $time->strftime('%d-%m-%Y %H:%M:%S');
I have 3 different columns with the same format. How can I change the values in those columns.
To update 3 columns in a table, you can pair DATEADD seconds to the epoch (1 Jan 1970) with the column name, i.e.
update tbl set datetimecol1 = dateadd(s, epochcol1, '19700101'), datetimecol2 = dateadd(s, epochcol2, '19700101'), datetimecol3 = dateadd(s, epochcol3, '19700101') You can't update in place since a bigint column cannot also be a datetime column. You have to update them into 3 other columns.
78
Use the DATEADD function:
SELECT DATEADD(ss, 1291388960, '19700101') ...specifying a date of January 1st, 1970. In this example, it was provided in the YYYYMMDD format.
DATEADD will return a DATETIME data type, so if you have a table & column established -- you can use the function to INSERT/UPDATE depending on your needs. Provide details, and I'll clarify. Once you have a DATETIME to work with, you can use CAST or CONVERT to format the date in TSQL.
24
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
