CODEWITHSUNDEEP
sqldateoracle-sqldeveloper
BaluCap
BaluCap

Reputation: 49

How do you convert YYYY_MM to date in Oracle SQL?

I have a column which has dates as texts. Like: '2021_01' (I will reefer this column as TextDate)

I convert it to '2021-01' with this code:
SELECT REPLACE(at.TextDate,'_','-') as DataFormat FROM tableName at

But when I try to cast it to date, or convert it to date, I always get 'Missing expression' errors. I tried this:

SELECT REPLACE(CONVERT(VARCHAR(7), at.TextDate, 126, '_', '-') as date FROM tableName at

But it will give me errors. Any suggestion?

Upvotes: 0

Views: 640

Answers (2)

Alex Poole
Alex Poole

Reputation: 191570

convert means something completely different in Oracle than it does elsewhere. You need the to_date() function:

SELECT TO_DATE(at.textDate, 'YYYY_MM') as DataFormat FROM tableName at

If you want to display it in a particular format then you can either let your client/application do that - most clients by default will use your session's NLS_DATE_FORMAT setting - or explicitly convert it back to a string with the complementary to_char() function.

db<>fiddle

The valid date elements are also in the documentation. You should only convert to a string for display though; while you are manipulating or storing it you should treat it as a date.

How can I filter last 3 months with it?

You need to use Oracle syntax, not SQL Server or other syntax. You also can't refer to a column alias in the same level of query. SO you can recalculate the date value; or as your string format is relatively sane you can convert the target date to a string and compare that, which might allow an index to be used. Something like:

SELECT TO_DATE(at.textDate, 'YYYY_MM') as DataFormat
FROM tableName at
WHERE at.textDate >= TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -3), 'YYYY_MM')

db<>fiddle

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 143083

TO_DATE with appropriate format mask.

Just to know what's what:

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

Code:

SQL> select to_date('2021-01', 'yyyy_mm') from dual;

TO_DATE('2021-01','
-------------------
01.01.2021 00:00:00

SQL>

Upvotes: 1

Related Questions