CODEWITHSUNDEEP
sqldateformatteradatavarchar
Julie
Julie

Reputation: 1

Varchar m/d/yyyy to yyyy/mm/dd sql

I have a file that inputs data in the format m/d/yyyy or m/dd/yyyy depending on whether or not the date value is 2 digits (example 4/1/2015 or 4/14/2015). I need to convert this to a date with the format yyyy/mm/dd (example 2015/04/01 or 2015/04/14). I have tried multiple way but I get the error "Invalid date" every time. Please help.

Things I have tried: 

cast((CASE WHEN CHAR_LENGTH(RSA_dt) <10 THEN 0 || TRIM(RSA_dt) end) AS DATE) 
CAST( CAST( RSA_dt AS DATE FORMAT 'DD-MMM-YY') AS DATE FORMAT 'YYYY-MM-DD')
cast(RSA_dt as date format 'YYYY-MM-DD')

Upvotes: 0

Views: 10687

Answers (9)

Jie
Jie

Reputation: 1284

The function substring cannot take a CASE statement as its parameter, so we have to use a nested SELECT statement.

select x,  case when substring(dt00, 5,1) = '/' then concat(left(dt00, 3), '0', substring(dt00, 4)) else dt00 end dtf
from (
select x,  case when substring(x, 2, 1) = '/' then CONCAT('0', x) else x end dt00 
    from (
    select '05/01/2019' x union all
    select '5/1/2019' union all
    select '05/1/2019' union all
    select '5/01/2019' union all
    select '12/1/2019' union all
    select '12/12/2019' 
) q0
) as a

Upvotes: 0

splashout
splashout

Reputation: 553

This works in SQL Server and Sybase IQ:

select 
    x original_value,
    substring(x, charindex('/', substring(x, 4, len(x)-4))+4, 4)
    +'/'+
    right('00'+substring(x, 1, charindex('/', x)-1),2)
    +'/'+
    right('00'+substring(x, charindex('/', x)+1, charindex('/', substring(x, 4, len(x)-4))+3 - charindex('/', x) - 1),2) yyyymmdd
from (
    select '05/01/2019' x union all
    select '5/1/2019' union all
    select '05/1/2019' union all
    select '5/01/2019'
) q0

Upvotes: 0

Vetteman
Vetteman

Reputation: 1

This will work, though not pretty. Specifically converting M/D/YYYY to YYYYMMDD

select substr(YOURDATE,-4,4) || 
substr('00'||SUBSTR(YOURDATE,1,to_number(regexp_instr(YOURDATE,'\/'))-1),-2,2) ||
case when substr(YOURDATE,2,1) = '/' then
    case when substr(YOURDATE,4,1) = '/' then
        '0' || substr(YOURDATE,3,1)
    else substr(YOURDATE,3,2)
    end
else case when substr(YOURDATE,5,1) = '/' then
        '0' || substr(YOURDATE,4,1)
    else substr(YOURDATE,4,2)
    end
end as NEWDATE                                                                                              
from YOURTABLE

Upvotes: 0

T L
T L

Reputation: 11

Here's another solution. It uses a regular expression to add a leading zero to any single-digit number, then standard casting to convert it to a date using existing format, then back to standard format. I have included several examples:

SELECT CAST(CAST(TD_SYSFNLIB.REGEXP_REPLACE('4/1/2015','(?<!\d)(\d)(?!\d)','0\1',1,0,'i') AS DATE FORMAT 'MM/DD/YYYY') AS DATE FORMAT 'YYYY-MM-DD');

SELECT CAST(CAST(TD_SYSFNLIB.REGEXP_REPLACE('4/14/2015','(?<!\d)(\d)(?!\d)','0\1',1,0,'i') AS DATE FORMAT 'MM/DD/YYYY') AS DATE FORMAT 'YYYY-MM-DD');

SELECT CAST(CAST(TD_SYSFNLIB.REGEXP_REPLACE('12/1/2015','(?<!\d)(\d)(?!\d)','0\1',1,0,'i') AS DATE FORMAT 'MM/DD/YYYY') AS DATE FORMAT 'YYYY-MM-DD');

Upvotes: 1

Jagan
Jagan

Reputation: 11

This will work for TPT.

Select '1/1/2014' as date1, 
TO_DATE (
case   
    when strtok(date1, '/', 1) between 1 and 9  and strtok(date1, '/', 2) between 1 and 9 then strtok(date1,'/', 3)||'/0'||strtok(date1, '/', 1)||'/0'||strtok(date1,'/', 2)
    when strtok(date1, '/', 1) between 1 and 9  and strtok(date1, '/', 2) > 9 then strtok(date1,'/', 3)||'/0'||strtok(date1, '/', 1)||'/'||strtok(date1,'/', 2)
    when strtok(date1, '/', 1) > 9 and strtok(date1, '/', 2) between 1 and 9  then strtok(date1,'/', 3)||'/'||strtok(date1, '/', 1)||'/0'||strtok(date1,'/', 2)
    else  strtok(date1, '/', 3)||'/'||strtok(date1, '/', 1)||'/'||strtok(date1,'/', 2)
end , 'YYYY/MM/DD') as req_date

Upvotes: 1

Dinesh
Dinesh

Reputation: 124

This should work.

Select '1/2/2014' as date1,
TO_CHAR(TO_DATE (
case    
    when strtok(date1, '/', 2) between 1 and 9 then strtok(date1,'/', 3)||'/0'||strtok(date1, '/', 1)||'/'||strtok(date1,'/', 2)
    else  strtok(date1, '/', 3)||'/'||strtok(date1, '/', 1)||'/'||strtok(date1,'/', 2)
end , 'YYYY/MM/DD'), 'YYYY/MM/DD') as "YYYY/MM/DD"

Upvotes: 0

Andrew
Andrew

Reputation: 8758

If you aren't using TPT, then you're stuck playing some awful substring games.

SELECT
 SUBSTR(chardate,INSTR(chardate,'/',1,2)+ 1,4) AS theYear,
'00' || SUBSTR(chardate,1,INSTR(chardate,'1',1,1)-2) AS theMonth,
'00' || SUBSTR(chardate,INSTR(chardate,'/',1,1)+ 1,INSTR(chardate,'/',1,2)  - INSTR(chardate,'/',1,1)-1) AS theDate,
CAST (theYear || '-' || SUBSTRING(theMonth,LENGTH(theMonth) -1,2) || '-' || SUBSTR(theDate,LENGTH(thedate)-1,2) AS DATE) AS ItsADate

    FROM
    <yourtable>

Really ugly, but it should work.

Upvotes: 0

dnoeth
dnoeth

Reputation: 60482

Which tool do you use for loading? The easiest way to load this data is to define that column as a VARDATE in a TPT job:

VARDATE(10) FORMATIN 'MM/DD/YY' FORMATOUT 'YYYY-MM-DD'

Otherwise you got a problem as Teradata's CAST doesn't like single digit day/month. Starting with TD14 there's Oracle's TO_DATE, which still doesn't like a single digit month, but at least tolerates single digit day:

TO_DATE(CASE 
          WHEN RSA_dt LIKE '_/%' 
          THEN '0' || RSA_dt
          ELSE RSA_dt
        END
        ,'mm/dd/yyyy')

Upvotes: 2

Adam V
Adam V

Reputation: 6356

It looks like you're providing the format you want, instead of the format you've got. Try this instead:

cast(RSA_dt as date format 'MM/DD/YYYY')

Upvotes: -1

Related Questions