2

I have a column in database from imported file in the format Friday June 1, 2018 and trying to format to a valid date conversion from varchar to date.

I have tried cast and convert with no success

SELECT CAST([Course date] as DATETIME)
FROM [dbo].[Test]

SELECT CONVERT(datetime, [Course date], 103)
FROM [dbo].[Test]

I expected conversion to type 103 UK dd/mm/yyyy

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
nenwmn
  • 89
  • 1
  • 1
  • 5
  • `I expected conversion to type 103 UK` - but [date time values have no display format](https://stackoverflow.com/questions/30032915/how-to-cast-the-datetime-to-time/30033028#30033028) - the conversion will return a value of type `DateTime`, which later you can represent as a string with whatever format you like. – Zohar Peled Dec 23 '18 at 06:30
  • Yes thank you, Tim helped convert to a date field and was then able to query by date. Type 103 was achieved later in the SQL query returning 01/06/2018 CONVERT (varchar(10), [Course date], 103) AS 'Date' – nenwmn Dec 23 '18 at 09:29
  • You should also take into consideration the comment I wrote on Tim's answer. Make sure the language is set to English otherwise try_convert might return null. – Zohar Peled Dec 23 '18 at 09:31

1 Answers1

2

TRY_CONVERT seems to be able to handle your date string. Note that the name of the day is superfluous, and is not needed to determine exactly what the date is. So, we can remove it using base string functions.

WITH yourTable AS (
    SELECT 'Friday June 1, 2018' AS datecol
)

SELECT
    datecol AS input,
    TRY_CONVERT(datetime, STUFF(datecol, 1, CHARINDEX(' ', datecol), '')) AS output
FROM yourTable;

enter image description here

Demo

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Thank you successfully converted. – nenwmn Dec 22 '18 at 15:32
  • Please note that this solution is dependent on language settings of the current session. Try adding `SET LANGUAGE Italian;` before your script and see how `try_convert` returns null. [Here's an online demo.](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=e8644f95ea1679d9685a73b1f52af0df) – Zohar Peled Dec 23 '18 at 06:28