Click here to Skip to main content
15,351,508 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi all, I have a SQL Server table where dates are stored as integer values such as 1473861331 how do I convert them to a Date / DateTime ?

What I have tried:

SQL
declare @mydate integer

select @mydate = 1473861331

SELECT CAST(CAST(@mydate AS VARCHAR) AS DATETIME)

But I receive this error

Conversion failed when converting date and/or time from character string.
Posted
Updated 7-Oct-16 18:23pm
Comments
Member 11137292 7-Oct-16 8:51am
   
What is the integer value? number of days? number of hours?
Member 11137292 7-Oct-16 8:54am
   
If its a number of time units you could add them to a base datetime, although the @mydate value causes an overflow.

declare @mydate integer

select @mydate = 1473861331

select DATEADD(hour,@mydate,'1 jan 1900' )
Afzaal Ahmad Zeeshan 7-Oct-16 8:56am
   
Why cannot you cast the integer to date time using a programming language, such as C# or C++?
Member 11137292 7-Oct-16 9:01am
   
good point, it all depends on what is consuming the data. OP makes no reference to programming language, just SQL.
pkfox 7-Oct-16 12:05pm
   
Sorry I should have been clearer, this needs to be done in a SQL Server stored procedure
Patrice T 7-Oct-16 14:51pm
   
How the date is supposed to be encoded in the integer ?

Assuming your datetime stored in Epoch & Unix Timestamp format, please ignore if this is not the case
declare @mydate integer
set @mydate = 1473861331
select DATEADD(hour,-5,(dateadd(second ,@mydate, '1/1/1970')))

--Result: 2016-09-14 08:55:31.000
   
v2
Comments
pkfox 8-Oct-16 3:47am
   
That's similar to what I've done now and it seems to be OK , why are you doing the -5 part though ?
Moving onward from the comment I left on your question, basically SQL can let you convert data from one format to another, but for that you would need to learn how to do that: Every SQL language developer (Oracle, SQL Server, MySQL) have their own implementations for these functions. That is why, it is sometimes hard to help you out, anyways try the following codes, http://stackoverflow.com/questions/3855867/convert-int-to-datetime-sql[^].

Finally, since you tagged it with SQL Server (with a special comma, intentionally, perhaps?) see this MSDN guide for more on CAST and CONVERT functions of SQL: https://msdn.microsoft.com/en-us/library/ms187928.aspx[^]
   
Comments
pkfox 7-Oct-16 12:07pm
   
This needs to be done in a SQL Server stored procedure
Afzaal Ahmad Zeeshan 7-Oct-16 13:51pm
   
See the first link and the second one for more. I provided the links for code in SQL Server.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900