Search code examples
sql-serverdatet-sqlcasting

Extract Date from a comment string

I am wondering what the best way is to extract the date and year from the following string structure:

Example: 01/01/2023: This is the comment which can vary in length

I am attempting to extract everything from the ":" which would be the date and the yyyy which would be the year (2023)

I have attempted to use the following syntax:

YEAR(CAST(CAST(LEFT(CD.Comments,10) AS DATE) AS varchar)) = ('2023')

to extract the year which returns the error:

Conversion failed when converting date and/or time from character string.

Solution

  • Instead of CAST(...) AS DATE, do CONVERT(DATETIME,LEFT(CD.Comments,10),101). The MM/DD/YYYY format is format number 101.

    https://www.w3schools.com/sql/func_sqlserver_convert.asp