How to convert SQL Server s timestamp column to datetime format

Question

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

User · Answer

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

User · Answer

My coworkers helped me with this   select CONVERT VARCHAR 10    lt tms column gt   112   count    from table where  lt tms column gt   gt   2012-09-10  group by CONVERT VARCHAR 10    lt tms column gt   112     or  select CONVERT DATE   lt tms column gt   112   count    from table where  lt tms column gt   gt   2012-09-10  group by CONVERT DATE   lt tms column gt   112

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Using cast you can get date from a timestamp field   SELECT CAST timestamp field AS DATE  FROM tbl name

User · Answer

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

User · Answer

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

User · Answer

SQL Server s TIMESTAMP datatype has nothing to do with a date and time   It s just a hexadecimal representation of a consecutive 8 byte integer - it s only good for making sure a row hasn t change since it s been read   You can read off the hexadecimal integer or if you want a BIGINT  As an example   SELECT CAST  0x0000000017E30D64 AS BIGINT    The result is   400756068   In newer versions of SQL Server  it s being called RowVersion - since that s really what it is  See the MSDN docs on ROWVERSION      Is a data type that exposes automatically generated  unique binary numbers within a database  rowversion is generally used as a mechanism   for version-stamping table rows  The   rowversion data type is just an incrementing number and does not   preserve a date or a time  To record a date or time  use a datetime2   data type    So you cannot convert a SQL Server TIMESTAMP to a date time - it s just not a date time   But if you re saying timestamp but really you mean a DATETIME column - then you can use any of those valid date formats described in the CAST and CONVERT topic in the MSDN help  Those are defined and supported  out of the box  by SQL Server  Anything else is not supported  e g  you have to do a lot of manual casting and concatenating  not recommended    The format you re looking for looks a bit like the ODBC canonical  style   121    DECLARE  today DATETIME   SYSDATETIME    SELECT CONVERT VARCHAR 50    today  121    gives   2011-11-14 10 29 00 470   SQL Server 2012 will finally have a FORMAT function to do custom formatting

User · Answer

Why not try FROM UNIXTIME unix timestamp  format

User · Answer

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

User · Answer

for me works  TO DATE  19700101    yyyymmdd      TIME   24   60   60   oracle DB

User · Answer

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

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

Examples related to sql-server

Examples related to datetime

Examples related to timestamp