CODEWITHSUNDEEP
sql-servert-sqlsql-server-2005
Netrix
Netrix

Reputation: 13

SQL Server - I need to see correct date/time and not a number

I have a table in a SQL Server 2005 database that I would like to get a report on. If I simply use "Script Table as" in SQL Server Management Studio, I can get the information I need but the column I am interested in, which should contain a date and time, just contains 558262380.

Are there some SQL commands I could add to the bottom of the "script" to convert that number to something more meaningful?

Below is a screenshot - it's the "Delayed Until" data I need to display as a correct date and time.

enter image description here

Upvotes: 0

Views: 61

Answers (1)

Andrea
Andrea

Reputation: 12405

Apparently this date is calculated as elapsed seconds starting from 01/01/2000.

You should be able to get the corresponding datetime using DATEADD() as follows:

select DATEADD(SECOND, 558262380 , '2000-01-01')

This is the output of this command:

enter image description here

Your original query should become:

SELECT  [Trader_Number]
       ,[Start_Trading_Hours] 
       ,[End_Trading_Hours] 
       ,[Inactivity_Period] 
       ,DATEADD(SECOND, [Delayed_Until], '2000-01-01') as [Delayed_Until] 
FROM [EMDC_1].[dbo].[Autologoff_Settings]

Upvotes: 1

Related Questions