I have been Googling this for more than an hour now, without any luck.
Imported a MySQL database via CSV into MS SQL Server 2005, where a Tstamp (timestamp) field has been hassling me.
How do I convert the tstamp field into a SQL date field?
DATEADD(SECOND, field8047, 1970/01/01) as datetime_created_calc
New error message: Msg 8116, Level 16, State 1, Line 1
Argument data type varchar is invalid for argument 2 of dateadd function.
I guess that field8047 is a
Select *, DATEADD(SECOND, cast(field8047 as int), '19700101') as datetime_created_calc From [Majestic].[dbo].[hdiyouth]
Assuming you have a VARCHAR() that represents a numeric type, and that numeric type represents the number of seconds from a fixed date.
CAST your VARCHAR() into a numeric type.
Then use that number in DATEADD().
If the number is too big, break it up into DAYS and SECONDS.
SELECT DATEADD( DAYS, CAST([hdiyouth].[Tstamp] AS BIGINT) / (60*60*24), DATEADD( SECONDS, CAST([hdiyouth].[Tstamp] AS BIGINT) % (60*60*24), 0 ) ) FROM [Majestic].[dbo].[hdiyouth]
If it's giving the right time, but the wrong date, change the
0 to whatever date your timestamp should be based from.
cast your field to integer.
Select DATEADD(SECOND, CAST(field8047 AS INTEGER), '1970/01/01') Select DATEADD(SECOND, CAST('60' AS INTEGER), '1970/01/01')