Solved

Last day of the month

Posted on 2002-04-21
12
524 Views
Last Modified: 2008-02-01
How do I find out the last day of the month using SQL command in MS SQL?

Thanks in advance.
0
Comment
Question by:hocheeming
  • 4
  • 3
  • 2
  • +2
12 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 6958973
Hi,

To find the last day of the previous month try this:

declare @LastDay datetime
set @LastDay = dateadd( day, -day( getdate()), getdate())

To find the last day of this month try this:
declare @LastDay datetime
set @LastDay = getdate()
set @LastDay = dateadd( month, +1, @LastDay )
set @LastDay = dateadd( day, -day( @LastDay ), @LastDay )

Regards
  David
0
 

Expert Comment

by:sparcu
ID: 6959948
select cast(convert(varchar(8),getdate()+ 30 , 20 )+ '01' as datetime)-1  

Instead of getdate you can put any date
0
 

Expert Comment

by:sparcu
ID: 6960002
select cast(convert(varchar(8),getdate()+ 30 , 20 )+ '01' as datetime)-1  

Instead of getdate you can put any date
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Accepted Solution

by:
gchavezr earned 5 total points
ID: 6960189
Hi Hocheeming:

Try whis this

a) if you want the date with hour try with this sentence:

   select (dateadd(m,1,getdate()-(day(getdate())-1))-1)


b)if you dont want the date with hour try with this sentence:

select (dateadd(m,1,convert(datetime,convert(varchar(11),getdate()),112)-(day(convert(datetime,convert(varchar(11),getdate()),112))-1))-1).






0
 

Author Comment

by:hocheeming
ID: 6960299
How do I change the current month to another month? Which variable should I change?
0
 
LVL 35

Expert Comment

by:David Todd
ID: 6961414
Hi,

You should look up the date functions in BOL.

For instance DateAdd which several of us have suggested ...

DATEADD
Returns a new datetime value based on adding an interval to the specified date.

Syntax
DATEADD ( datepart , number, date )

Arguments
datepart

Is the parameter that specifies on which part of the date to return a new value. The table lists the dateparts and abbreviations recognized by Microsoft® SQL Server™.

Datepart Abbreviations
Year yy, yyyy
quarter qq, q
Month mm, m
dayofyear dy, y
Day dd, d
Week wk, ww
Hour hh
minute mi, n
second ss, s
millisecond ms


number

Is the value used to increment datepart. If you specify a value that is not an integer, the fractional part of the value is discarded. For example, if you specify day for datepart and1.75 for number, date is incremented by 1.

date

Is an expression that returns a datetime or smalldatetime value, or a character string in a date format. For more information about specifying dates, see datetime and smalldatetime.

If you specify only the last two digits of the year, values less than or equal to the last two digits of the value of the two digit year cutoff configuration option are in the same century as the cutoff year. Values greater than the last two digits of the value of this option are in the century that precedes the cutoff year. For example, if two digit year cutoff is 2049 (default), 49 is interpreted as 2049 and 2050 is interpreted as 1950. To avoid ambiguity, use four-digit years.

Return Types
Returns datetime, but smalldatetime if the date argument is smalldatetime.

Examples
This example prints a listing of a time frame for titles in the pubs database. This time frame represents the existing publication date plus 21 days.

USE pubs
GO
SELECT DATEADD(day, 21, pubdate) AS timeframe
FROM titles
GO
Here is the result set:

timeframe                  
---------------------------
Jul 3 1991 12:00AM          
Jun 30 1991 12:00AM        
Jul 21 1991 12:00AM        
Jul 13 1991 12:00AM        
Jun 30 1991 12:00AM        
Jul 9 1991 12:00AM          
Mar 14 1997  5:09PM        
Jul 21 1991 12:00AM        
Jul 3 1994 12:00AM          
Mar 14 1997  5:09PM        
Nov 11 1991 12:00AM        
Jul 6 1991 12:00AM          
Oct 26 1991 12:00AM        
Jul 3 1991 12:00AM          
Jul 3 1991 12:00AM          
Nov 11 1991 12:00AM        
Jul 3 1991 12:00AM          
Jul 3 1991 12:00AM          

(18 row(s) affected)

See Also

CAST and CONVERT

Data Types

Date and Time Functions

Time Formats

©1988-2000 Microsoft Corporation. All Rights Reserved.
0
 

Expert Comment

by:gchavezr
ID: 6961436
The variable that you MUST change is the FUNCTION GETDATE()

Example:
 
Declare @dtparamdate datetime  -- parameter date

Set @dtparamdate = '20020422 10:20:31.257' -- today or the date that you want

--With hh:mm:ss

select (dateadd(m,1,@dtparamdate-(day(@dtparamdate)-1))-1)

-- Without  hh:mm:ss
select (dateadd(m,1,convert(datetime,convert(varchar(11),@dtparamdate),112)-(day(convert(datetime,convert(varchar(11),@dtparamdate),112))-1))-1)



0
 

Author Comment

by:hocheeming
ID: 6967481
The codes works great but some how I was unable to piece up eveything together. One final question and that is...

How do I use IF.. ELSE statement within the SQL statement?

For exp.

I want to have some like

startdate is the present field in the schema.

==============================================

select * from TABLE where isrecurring = 3 AND
if (today_date > 
select (dateadd(m,1,CAST(startdate AS smalldatetime)-(day(CAST(startdate AS smalldatetime))-1))-1))
begin
startdate = select (dateadd(m,1,CAST(startdate AS smalldatetime)-(day(CAST(startdate AS smalldatetime))-1))-1)
end

========================================

Something like this. My main aim is to get the current date and compare with the date in the database and if the current date is greater than the last date of the month, the current date will be reassigned to the last date of the month.

Thanks.


0
 
LVL 35

Expert Comment

by:David Todd
ID: 6969934
Hi,

If ... else is used for program flow in stored procedures and scripts, rather than in select statements.

Case can be used in select statements. eg

select category = case
     when Quantity < 0 then 'Return'
     when Quantity > 0 then 'Sale'
     end,
     abs( Quantity ) as Quantity
from SO_Sales

so your code could look something like this:

declare @DBDate DateTime
select @DBDate = max( StartDate )
from Table
where ...

declare @Now  DateTime
set @Now = getdate()

if @Now > @DBDate begin
    set @Now = dateadd( day, -day( @Now ), @Now )
    set @Now = dateadd( month, 1, @Now )
    set @Now = dateadd( hour, -( hour( @now ) + 1 ), @Now )

    update Table
    set StartDate = @Now
    where ...
end else begin
    print 'no need to update date ...'
end

Regards
  David

0
 

Author Comment

by:hocheeming
ID: 6979201
Hi,

I got this error...

'hour' is not a recognized function name.

When I try to run:

if @Now > @DBDate begin
   set @Now = dateadd( day, -day( @Now ), @Now )
   set @Now = dateadd( month, 1, @Now )
   set @Now = dateadd( hour, -( hour( @now ) + 1 ), @Now )
0
 
LVL 35

Expert Comment

by:David Todd
ID: 6979623
Hi,

For hour, minute, second, millisecond use datepart function

ie set @Now = dateadd( hour, -( datepart( hour, @Now ) + 1 ), @Now )

Regards
  David
0
 

Expert Comment

by:dhanush
ID: 6984175
To get the last day of any month, add one month to the  date and minus the day.

Set @LastDay=Dateadd(dd, -1 * datepart(dd, @Date), Dateadd( mm, 1, @Date))

By
Dhanushkodi
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question