1

Server Type: MSSQL 2014

My string(s): VARCHAR '2/05/2017 7:54:51 PM'

I need to convert that to any supported DateTime format (or preferably directly to epoch). It needs to handle NULL and "" (blank) rows.

I've spent a good few hours trawling answers and Google to no avail. The frustrating bit is the stored format is only slightly different to Date Format 131.

I've tried things such as;

FORMAT(CONVERT(DATETIME, '2/05/2017 7:54:51 PM'), 'dd/MM/yyyy hh:mm:ss')
CONVERT(DATETIME, '2/05/2017 7:54:51 PM',22)
CONVERT(DATETIME, '2/05/2017 7:54:51 PM',131)

/* SQL Error (241): Conversion failed when converting date and/or time from character string. */

/* SQL Error (242): The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. */

As well as a whole bunch of muck-arounds with SUBSTRING/CHARINDEX and so on to try and handle each part individually, to no avail.

Vadim Kotov
  • 8,084
  • 8
  • 48
  • 62
Shane A.
  • 13
  • 4

2 Answers2

0
SELECT CONVERT(DATETIME, '2/05/2017 7:54:51 PM',131)
SELECT CONVERT(DATETIME, '2/05/2017 7:54:51 PM',22) -- wrong date time format

22 is wrong input format please see https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql

Wojciech Wojtulewski
  • 187
  • 3
0

Found my solution. Was getting it wrong due to misunderstanding "CONVERT"

Solution:

DECLARE @wrongdate DATETIME = CONVERT(VARCHAR, TRY_PARSE('2/05/2017 7:54:51 PM' AS DATETIME USING 'en-gb'), 121);
SELECT convert(bigint, datediff(ss,  '01-01-1970 00:00:00', @wrongdate));

This needs to be manipulated though if you intend on acting on the results of a select, so instead, change the select to;

(CONVERT(bigint, datediff(ss,  '01-01-1970 00:00:00', CONVERT(VARCHAR, TRY_PARSE(CAST([MyColumn].Object AS VARCHAR) AS DATETIME USING 'en-gb'), 121)))) AS [my_column]
Shane A.
  • 13
  • 4