Home » SQL & PL/SQL » SQL & PL/SQL » Is it possible to get 1st day of the month based on (11)
Is it possible to get 1st day of the month based on [message #600721] Mon, 11 November 2013 10:11 Go to next message
cplusplus1
Messages: 58
Registered: October 2012
Location: usa
Member
I am doing the following to get year and then month number from gl_accts table, is it possible to get 1st day of the month value as a new column.

If period is 3 and year is 2011, then the FirstDay_col, should have 20110301

select substr(DATA_SET_ID,7,10) YEAR,
month_number from GL_ACCTS

Thanks a lot for the helpful info.
Re: Is it possible to get 1st day of the month based on [message #600722 is a reply to message #600721] Mon, 11 November 2013 10:19 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
The first day of every month is '01' & can be hard coded as such.
Re: Is it possible to get 1st day of the month based on [message #600723 is a reply to message #600722] Mon, 11 November 2013 10:35 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Another option would be (if you work with date values) to truncate to month.


SQL> SELECT SYSDATE, Trunc(SYSDATE,'MM') FROM dual;

SYSDATE   TRUNC(SYS
--------- ---------
11-NOV-13 01-NOV-13

SQL>
Re: Is it possible to get 1st day of the month based on [message #600730 is a reply to message #600721] Mon, 11 November 2013 13:04 Go to previous message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
How does a ten character string represent a year, which is what you are doing with the SUBSTR?
Previous Topic: check constraint on column
Next Topic: does a query can use more than one index on table at a time
Goto Forum:
  


Current Time: Fri Mar 29 08:52:45 CDT 2024