Important alert: (current site time 11/27/2014 11:39:35 AM EDT)
 

VB icon

Get First Day of Month (SQL/Oracle versions)

Email
Submitted on: 11/6/2001 6:37:07 AM
By: James Travis  
Level: Beginner
User Rating: By 4 Users
Compatibility: SQL Server 7.0, SQL Server 6.5 and earlier, Oracle
Views: 35294
author picture
(About the author)
 
     Get the first day of the month more efficiently.
 
code:
Can't Copy and Paste this?
Click here for a copy-and-paste friendly version of this code!
 
Terms of Agreement:   
By using this code, you agree to the following terms...   
  1. You may use this code in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.
  2. You MAY NOT redistribute this code (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.   
  3. You may link to this code from another website, but ONLY if it is not wrapped in a frame. 
  4. You will abide by any additional copyright restrictions which the author may have placed in the code or code's description.
				
--**************************************
-- Name: Get First Day of Month (SQL/Oracle versions)
-- Description:Get the first day of the month more efficiently.
-- By: James Travis
--
-- Returns:First day of month with 00hr as time.
--
-- Assumes:I write this just to take 
-- what has been done a step further.
-- This will demonstrait a more 
-- efficient way to do the find the 
-- first of the month calculation that
-- was published by RObot X5. They used
-- the standard "select convert(
-- datetime,(convert(varchar,month(
-- getdate())) + convert(varchar,'/1/')
-- + convert(varchar,(year(getdate())))
-- ))" that most people are used to. 
-- However This takes 8 functions to
-- complete and you are changing the
-- data type mulitple times. In the
-- post by RObot X5 a post was made by
-- coolguan, who was going down the
-- right path, but just did not have
-- all the pieces to finish the work.
-- Anyway what I will show does the
-- same thing in SQL in 5 functions
-- (which in the long run saves CPU
-- Cycles and also makes fewer data
-- type changes which has an overall
-- memory effect). Also included is
-- the same thing for Oracle.
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=360&lngWId=5--for details.--**************************************

SQL Server:
SELECT DATEADD(day,-DAY(GETDATE()) + 1,CONVERT(VARCHAR,GETDATE(),101))
Oracle:
to_date(to_char((sysdate - to_char(sysdate, 'dd') + 1),'dd-mon-yyyy'))


Other 20 submission(s) by this author

 


Report Bad Submission
Use this form to tell us if this entry should be deleted (i.e contains no code, is a virus, etc.).
This submission should be removed because:

Your Vote

What do you think of this code (in the Beginner category)?
(The code with your highest vote will win this month's coding contest!)
Excellent  Good  Average  Below Average  Poor (See voting log ...)
 

Other User Comments

11/8/2001 2:44:03 PMSironbi

Don't forget to change the value of the convert function's last parameter to 103 if your date format is 'dd/mm/yyyy'
(If this comment was disrespectful, please report it.)

 
12/24/2001 12:48:29 AMSelahattin TASPINAR

In Oracle :
select trunc(sysdate,'MM') from dual ;
(If this comment was disrespectful, please report it.)

 
1/31/2002 9:36:03 AMAlexei

Only 4 functions
SELECT DATEADD(day,1,DATEADD(m,-1,CONVERT(VARCHAR,GETDATE(),101)))
(If this comment was disrespectful, please report it.)

 
10/15/2002 9:20:31 AM

It is so simple in Oracle :
select trunc(sysdate, 'month')
from dual;

(If this comment was disrespectful, please report it.)

 
11/10/2004 4:28:15 PMAndy Archer

...and first day of next month (Oracle)

select last_day (sysdate) + 1 from dual;
(If this comment was disrespectful, please report it.)

 

Add Your Feedback
Your feedback will be posted below and an email sent to the author. Please remember that the author was kind enough to share this with you, so any criticisms must be stated politely, or they will be deleted. (For feedback not related to this particular code, please click here instead.)
 

To post feedback, first please login.