1

How do I display SQL column in date format which is in bigint?

I am not looking to convert each row for example

select dateadd(second, 1358523245140/1000+8*60*60,  '19700101') from tablename
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user2856752
  • 21
  • 5
  • then what are you looking to? – hago Oct 08 '13 at 01:51
  • hago. What I want is an automated sql query which displays all rows within the bigint column in date format. currently, I am running the table then picking each row and converting it individually like above. Hope this makes sense. So should I create procedure within select statement. If yes, then I am looking at an example as to how I can insert the above within the procedure. – user2856752 Oct 08 '13 at 15:07

1 Answers1

0

To display in a recognizable date format just add the desired format style on top of that calculation. e.g.

select
    dateadd(second, 1358523245140/1000+8*60*60,  '19700101') 
  , convert(varchar, dateadd(second, 1358523245140/1000+8*60*60,  '19700101') ,121)
;

The first column will display in the database default format (e.g. January, 18 2013 23:34:05+0000) , the second column will display is style 121 (2013-01-18 23:34:05.000)

There are many tables of these styles available, or in SQL 2012 you can use the FORMAT()

e.g. http://msdn.microsoft.com/en-us/library/ms187928.aspx

http://www.sql-server-helper.com/sql-server-2012/format-function-vs-convert-function.aspx

ps: there is another method for converting that 13 digit unix timestamp to TSQL datetime here: Convert unix epoch timestamp to TSQL timestamp

Community
  • 1
  • 1
Paul Maxwell
  • 33,002
  • 3
  • 32
  • 51
  • by the way, I assume that when you sat "I am not looking to convert" you mean you don't want to change the column to datetime or add another column. (but: It would be possible to use a computed column so you don't have to do this for each query) – Paul Maxwell Oct 08 '13 at 02:46
  • Thank you. So should I use the above select statement within the procedure? currently, I run the table and pick values each time (example, 1358523245140) then convert it. I want to display it for all rows in one query. Also, these values are 18 in number and I reduce it to 13 everytime. – user2856752 Oct 08 '13 at 14:59
  • 1358523245140 is originally 135852324514000000 – user2856752 Oct 08 '13 at 15:01
  • your question isn't very detailed - you may want to edit it? there was no mention of procedure or truncation of field for example - so it was a guess on what you really wanted. Seems like yo are working through a cursor so I'm not sure what you really want now. You may use a query like I proposed but how it sits with a proc I've never seen I don't know. Sorry. – Paul Maxwell Oct 09 '13 at 05:59