VarChar to Date

General
Highlighted
Enthusiast

VarChar to Date

Hi All,

 

I have recently imported an excel file with the Date and Time columns coming in as VarChar (due to so many issues) and I am hoping to convert them to their respective formats.

 

Currently I have, as VarChar(20):

  • Date: 20180627
  • Time: 125627

They should look like

  • Date: 2018-06-27
  • Time: 12:56

 

I have tried the following variations for the two columns to no avail:

  • CAST(datecol AS DATE FORMAT 'YYYY-MM-DD')
  • Cast((cast(timecol as char(4))) as time format 'hh:mi')

 Neither codes work for either Date nor Time.

Any help would be fantastic

 

Thank you in advance.

Tags (4)

Accepted Solutions
Teradata Employee

Re: VarChar to Date

The FORMAT needs to describe the format found in the VARCHAR string:

  • CAST(datecol AS DATE FORMAT 'YYYYMMDD')
  • CAST(SUBSTRING(timecol FROM 1 for 4) as time format 'hhmi')

Use of CAST to truncate a string only works in Teradata mode; SUBSTRING is preferred, and works the same in both ANSI and Teradata modes.

 

 

1 ACCEPTED SOLUTION
2 REPLIES
Teradata Employee

Re: VarChar to Date

The FORMAT needs to describe the format found in the VARCHAR string:

  • CAST(datecol AS DATE FORMAT 'YYYYMMDD')
  • CAST(SUBSTRING(timecol FROM 1 for 4) as time format 'hhmi')

Use of CAST to truncate a string only works in Teradata mode; SUBSTRING is preferred, and works the same in both ANSI and Teradata modes.

 

 

Enthusiast

Re: VarChar to Date

That was beautiful!

 

Thank you very much, Fred.