Oracle SQL Roll back date to beginning of month best practice CONCAT & SUBSTR or TRUNC

Question

I have a field date which is a date format.

It outputs like this for example.

09-NOV-14

Now what i want the output to be is any date rolled back to the 1st of its month.

01-NOV-14

What i did to achieve this is

CONCAT('01',SUBSTR(table.date,3))

Is this the most efficient/best practice way of doing this?

As a general rule, date functions are pretty good in most decent DBMS. You hardly ever need to do string manipulation. — Álvaro González, Nov 26 '14 at 15:12
@ThomasKrojer Thanks that worked perfectly! Put it in an answer and i will mark it as correct — Matt, Nov 26 '14 at 15:16

score 3 · Accepted Answer · answered Nov 26 '14 at 15:27

3

moved from comment to answer:

trunc(date,'month')

answered Nov 26 '14 at 15:27

Thomas Krojer

1,018
7
9

score 2 · Answer 2 · edited May 23 '17 at 12:12

2

The output format you get when a date is cast to string is configurable:

SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
---------
26-NOV-14

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

Session altered.

SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
-------------------
2014-11-26 16:15:44

... thus your approach is not particularly robust. To use date functions (rather than string manipulation) please check Make date time's first day of its month.

edited May 23 '17 at 12:12

Community

1
1

answered Nov 26 '14 at 15:25

Álvaro González

142,137
41
261
360

A great answer but i used the above as it was purely for a one time report – Matt Nov 26 '14 at 15:28
You're free to accept the answer that better solves your question, but we're both suggesting the same solution. – Álvaro González Nov 26 '14 at 15:32

Oracle SQL Roll back date to beginning of month best practice CONCAT & SUBSTR or TRUNC

2 Answers2