Solved

Last day of the month

Posted on 2002-04-21
12
522 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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

708 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now