Converting Varchar to Date

• mediacommentry

  Old Hand

  Points: 348

  More actions

  February 13, 2019 at 2:49 am

  #377013

  I have CREATE TABLE myTable
(
    UpdateDate VARCHAR(30)
)
INSERT myTable
VALUES ('17/01/2019')


  And I have this query 
  
  SELECT
    DateColumn    = CAST(UpdateDate AS DATE)
FROM myTable

  SELECT
    DateColumn    = CONVERT(DATE, UpdateDate)
FROM myTable 

  None of the query is working, I'm getting this error Conversion failed when converting date and/or time from character string.
  where am I getting it wrong?

• DesNorton

  SSC-Insane

  Points: 24276

  More actions

  February 13, 2019 at 3:29 am

  #2021897

  mediacommentry - Wednesday, February 13, 2019 2:49 AM

  I have CREATE TABLE myTable
(
    UpdateDate VARCHAR(30)
)
INSERT myTable
VALUES ('17/01/2019')


  And I have this query 
  
  SELECT
    DateColumn    = CAST(UpdateDate AS DATE)
FROM myTable

  SELECT
    DateColumn    = CONVERT(DATE, UpdateDate)
FROM myTable 

  None of the query is working, I'm getting this error Conversion failed when converting date and/or time from character string.
  where am I getting it wrong?

  Your #1 problem is the storing of dates as varchar.  If possible, change it.

  So, in order to convert all of the text to dates, you need to know the format of the dates being converted.
  Are ALL of the dates in the format "dd/mm/yyy"?  If yes, then this should work
  SELECT 
    DateColumn    = CONVERT(DATE, UpdateDate, 103)
FROM myTable

  You can read more about the styles on the CAST and CONVERT page

  ---

  How to post data/code on a forum to get the best help.

• drew.allen

  SSC Guru

  Points: 76944

  More actions

  February 13, 2019 at 9:26 am

  #2021934

  You can also use TRY_CAST() or TRY_CONVERT() to identify which records have issues.  Each of these will produce a NULL value instead of an error when the conversion fails.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA