[sql-server] How to convert SQL Server's timestamp column to datetime format

As SQL Server returns timestamp like 'Nov 14 2011 03:12:12:947PM', is there some easy way to convert string to date format like 'Y-m-d H:i:s'.

So far I use

date('Y-m-d H:i:s',strtotime('Nov 14 2011 03:12:12:947PM'))

This question is related to sql-server datetime timestamp

The answer is


I had the same problem with timestamp eg:'29-JUL-20 04.46.42.000000000 PM'. I wanted to turn it into 'yyyy-MM-dd' format. The solution that finally works for me is

SELECT TO_CHAR(mytimestamp, 'YYYY-MM-DD') FROM mytable;


for me works: TO_DATE('19700101', 'yyyymmdd') + (TIME / 24 / 60 / 60) (oracle DB)


Why not try FROM_UNIXTIME(unix_timestamp, format)?


"You keep using that word. I do not think it means what you think it means." — Inigo Montoya

The timestamp has absolutely no relationship to time as marc_s originally said.

declare @Test table (
     TestId int identity(1,1) primary key clustered
    ,Ts     timestamp
    ,CurrentDt datetime default getdate()
    ,Something varchar(max)
)

insert into @Test (Something)
    select name from sys.tables
waitfor delay '00:00:10'

insert into @Test (Something)
    select name from sys.tables

select * from @Test

Notice in the output that Ts (hex) increments by one for each record, but the actual time has a gap of 10 seconds. If it were related to time then there would be a gap in the timestamp to correspond with the difference in the time.


Some of them actually does covert to a date-time from SQL Server 2008 onwards.

Try the following SQL query and you will see for yourself:

SELECT CAST (0x00009CEF00A25634 AS datetime)

The above will result in 2009-12-30 09:51:03:000 but I have encountered ones that actually don't map to a date-time.


The simplest way of doing this is:

SELECT id,name,FROM_UNIXTIME(registration_date) FROM `tbl_registration`;

This gives the date column atleast in a readable format. Further if you want to change te format click here.


Using cast you can get date from a timestamp field:

SELECT CAST(timestamp_field AS DATE) FROM tbl_name

I will assume that you've done a data dump as insert statements, and you (or whoever Googles this) are attempting to figure out the date and time, or translate it for use elsewhere (eg: to convert to MySQL inserts). This is actually easy in any programming language.

Let's work with this:

CAST(0x0000A61300B1F1EB AS DateTime)

This Hex representation is actually two separate data elements... Date and Time. The first four bytes are date, the second four bytes are time.

  • The date is 0x0000A613
  • The time is 0x00B1F1EB

Convert both of the segments to integers using the programming language of your choice (it's a direct hex to integer conversion, which is supported in every modern programming language, so, I will not waste space with code that may or may not be the programming language you're working in).

  • The date of 0x0000A613 becomes 42515
  • The time of 0x00B1F1EB becomes 11661803

Now, what to do with those integers:

Date

Date is since 01/01/1900, and is represented as days. So, add 42,515 days to 01/01/1900, and your result is 05/27/2016.

Time

Time is a little more complex. Take that INT and do the following to get your time in microseconds since midnight (pseudocode):

TimeINT=Hex2Int(HexTime)
MicrosecondsTime = TimeINT*10000/3

From there, use your language's favorite function calls to translate microseconds (38872676666.7 µs in the example above) into time.

The result would be 10:47:52.677


Works fine, except this message:

Implicit conversion from data type varchar to timestamp is not allowed. Use the CONVERT function to run this query

So yes, TIMESTAMP (RowVersion) is NOT a DATE :)

To be honest, I fidddled around quite some time myself to find a way to convert it to a date.

Best way is to convert it to INT and compare. That's what this type is meant to be.

If you want a date - just add a Datetime column and live happily ever after :)

cheers mac


My coworkers helped me with this:

select CONVERT(VARCHAR(10), <tms_column>, 112), count(*)
from table where <tms_column> > '2012-09-10'
group by CONVERT(VARCHAR(10), <tms_column>, 112);

or

select CONVERT(DATE, <tms_column>, 112), count(*)
from table where <tms_column> > '2012-09-10'
group by CONVERT(DATE, <tms_column>, 112);

Not sure if I'm missing something here but can't you just convert the timestamp like this:

CONVERT(VARCHAR,CAST(ZEIT AS DATETIME), 110)

After impelemtation of conversion to integer CONVERT(BIGINT, [timestamp]) as Timestamp I've got the result like

446701117 446701118 446701119 446701120 446701121 446701122 446701123 446701124 446701125 446701126

Yes, this is not a date and time, It's serial numbers


Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to datetime

Comparing two joda DateTime instances How to format DateTime in Flutter , How to get current time in flutter? How do I convert 2018-04-10T04:00:00.000Z string to DateTime? How to get current local date and time in Kotlin Converting unix time into date-time via excel Convert python datetime to timestamp in milliseconds SQL Server date format yyyymmdd Laravel Carbon subtract days from current date Check if date is a valid one Why is ZoneOffset.UTC != ZoneId.of("UTC")?

Examples related to timestamp

concat yesterdays date with a specific time How do I format {{$timestamp}} as MM/DD/YYYY in Postman? iOS Swift - Get the Current Local Time and Date Timestamp Pandas: Convert Timestamp to datetime.date Spark DataFrame TimestampType - how to get Year, Month, Day values from field? What exactly does the T and Z mean in timestamp? What does this format means T00:00:00.000Z? Swift - iOS - Dates and times in different format Convert timestamp to string Timestamp with a millisecond precision: How to save them in MySQL