CODEWITHSUNDEEP
sqloracle-databasedatetimestampsubstr
KassieB
KassieB

Reputation: 135

Convert varchar/timestamp col to Date field

I have a varchar2 datatype field (lmp_date) that can return either null or what looks like a timestamp value. Changing the database data_type to DATE isn't a possibility, so now I'm needing to convert this to a date, but with the values this column returns, I'm having some problems.

Returned values for lmp_date = null or 2021-06-11-00.00.00

Date format needed: MM/DD/YYYY

I've tried cast, convert, substr+instr to no avail

ETA - A couple example attempts (because there have been 10+:

select order_no, to_date(lmp_date) lmp_date from table_a - with error message of 'ORA-01861: literal does not match format string'

select order_no, to_date(substr(lmp_date, 1, instr(lmp_date, '00' -15))) lmp_date from table_a - since lmp_date has null value possibilities, this doesn't work successfully

select order_no, cast(lmp_date as date) lmp_date from table_a - with same error message of 'ORA-01861: literal does not match format string'

select order_no, to_date(lmp_date, 'YYYY-MM-DD') lmp_date from table_a - ORA-01830: date format picture ends before converting entire input string

There have been more attempts, this is all I can remember

Upvotes: 0

Views: 3145

Answers (1)

Alex Poole
Alex Poole

Reputation: 191245

To convert a string to a date, use the to_date() function with a suitable format mask:

to_date(lmp_date, 'YYYY-MM-DD-HH24:MI:SS')

The format model elements are in the documentation.

The result of that is a date data type, which is an internal 7-byte representation. Your client or application will format that for display, which may be based on your NLS_DATE_FORMAT setting, so you can modify that to change hot all dates are displayed; or use to_char() to convert the date back to a string, e.g.:

to_char(to_date(lmp_date, 'YYYY-MM-DD-HH24:MI:SS'), 'MM/DD/YYYY')

although if you want it as that string you can just use string manipulation with substr() and concatenation:

case when lmp_date is not null then
  substr(lmp_date, 6, 2) || '/' || substr(lmp_date, 9, 2) || '/' || substr(lmp_date, 1, 4)
end

db<>fiddle

When you do either of these:

to_date(lmp_date)
cast(lmp_date as date)

this also relies on your session NLS_DATE_FORMAT; and the "literal does not match format string" error indicates that it doesn't match the string, e.g. if you have the still-default 'DD-MON-RR' setting. It would actually work - for you in your current session - if you changed that setting. I've shown that here just for info. But to work for anyone regardless of their session settings, you should use to_date() with an explicit format mask, and don't rely on or assume anything session-specific.

You were nearly there with:

to_date(lmp_date, 'YYYY-MM-DD')

and again the "date format picture ends before converting entire input string" message tells you what is wrong - your string carries on past the YYYY-MM-DD elements. Expanding the format mask to match all of the string, as I did above, means it knows what each part means.

If you were really only interested in the date part then you could cut the end off the string:

to_date(substr(lmp_date, 1, 10), 'YYYY-MM-DD')

but that's only really useful if you have a mix of string values where some have times and some do not. (The resulting date will always have a time; it will just be midnight.) And if you have dates with different formats then it gets a bit complicated - partly why you shouldn't store dates as strings.

Upvotes: 2

Related Questions