Last day of the month

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

select cast(convert(varchar(8),getdate()+ 30 , 20 )+ '01' as datetime)-1  

Instead of getdate you can put any date

select cast(convert(varchar(8),getdate()+ 30 , 20 )+ '01' as datetime)-1  

Instead of getdate you can put any date

How do I change the current month to another month? Which variable should I change?

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.

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)

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.

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

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 )

Hi,

For hour, minute, second, millisecond use datepart function

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

Regards
  David

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