0

I have a date/ time field in my database with the type set to 'decimal'.

When querying the date/ time field using the below query:

SELECT my_date_time_field
FROM my_table

It returns the following result (an example):

1323977278

This is meant to represent the following date and time (UK date format):

30/11/2011 02:50:19 AM

I have been told that the result I get from the database is how many seconds have passed since:

01-JAN-1970 00:00:00

I would like to know how I can get my SQL query result to display the date in the proper UK date format.

I have tried the following and it did not work from a similar question:

CONVERT(DATETIME, CAST(my_date_time_field AS VARCHAR(8)), 112)

Should I be converting my_date_time_field to the YYYYMMDDHHMMSS format first before doing a DATEDIFF function?

Or is there a function(s) to convert my_date_time_field straight to a DD-MMM-YYYY HH:MM:SS or DD-MM-YYYY HH:MM:SS format?

Vadim Kotov
  • 8,084
  • 8
  • 48
  • 62
Didar Cern
  • 3
  • 2

2 Answers2

0

You can convert the value to a datetime using:

select dateadd(second, 1323977278, '1970-01-01')

You can then format this however you like using convert() or format().

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

Edited:

Although the explanation below would be best, for SQL 2008 I would go with:

SELECT  
CONCAT (CONVERT(VARCHAR, dateadd( S, 1323977278, '1970-01-01'), 103), ' ',  
CONVERT(varchar(15),CAST(dateadd( S, 1323977278, '1970-01-01') AS TIME),100))

It would be handy to create a function since I'm sure you'll need it often.

CREATE FUNCTION Decimal_To_Date (
@ctimestamp BIGINT
)
RETURNS datetime
AS
BEGIN
  declare @return datetime

  SELECT @return = FORMAT(CAST(dateadd( S, @ctimestamp, '1970-01-01') AS DATETIME),'dd/mm/yyyy hh:mm tt')

  return @return
END
GO

Then just use it as 

SELECT dbo.Decimal_To_Date(1323977278)

and you'll get 15/27/2011 07:27 PM

ultimately (if you don't want a function) just use:

FORMAT(CAST(dateadd( S, 1323977278, '1970-01-01') AS DATETIME),'dd/mm/yyyy hh:mm tt')

PS: Format will work from SQL Server 2012. If you have a previous version you'll need to use CONVERT

PS2: The 131 code uses Hijiri (Gregorian) calendar which is not what you want...

Carlos Alves Jorge
  • 1,919
  • 1
  • 13
  • 29
  • Thank you! But unfortunately I do not have authority to CREATE functions in this server. I will however pass this on as it will be useful! – Didar Cern Sep 28 '17 at 11:08
  • Just use FORMAT(CAST(dateadd( S, 1323977278, '1970-01-01') AS DATETIME),'dd/mm/yyyy hh:mm tt') – Carlos Alves Jorge Sep 28 '17 at 11:15
  • I am running from SQL Server 2008. So I used the CONVERT function. This is how it looks: convert(datetime, cast(dateadd(second, [SUBMIT_DATE], '1970-01-01') as datetime), 113) But it still displays the date/ time in 'YYYY-MM-DD HH:MM:SS.000' format. I also tried 'dd/mm/yyyy hh:mm tt' (as you mentioned above), but it returns the following error: Argument data type varchar is invalid for argument 3 of convert function. I tried changing my CONVERT and CAST functions to return VARCHAR but it still returns the same error message. But of course, I am after a DATETIME data type. – Didar Cern Sep 28 '17 at 12:09
  • SELECT CONCAT (CONVERT(VARCHAR, dateadd( S, 1323977278, '1970-01-01'), 103), ' ', CONVERT(varchar(15),CAST(dateadd( S, 1323977278, '1970-01-01') AS TIME),100)) – Carlos Alves Jorge Sep 28 '17 at 12:26
  • I tried this and it works, but the data type of this field changes and I need it to be a DATETIME data type. Thank you, Carlos :) – Didar Cern Sep 28 '17 at 13:27
  • DATETIME format is YYYY-MM-DD HH:MM:SS.000. Either you want a DATETIME or a format of DATETIME – Carlos Alves Jorge Sep 28 '17 at 13:55