Last day of Month

Question

Post reply

Last day of Month

Viewing 15 posts - 31 through 44 (of 44 total)

You must be logged in to reply to this topic. Login to reply

praneshram Mr or Mrs. 500 Points: 571 More actions · Answer 1

Finding Dates like first day and last day etc

----Today

SELECT GETDATE() 'Today'

----Yesterday

SELECT DATEADD(d,-1,GETDATE()) 'Yesterday'

----First Day of Current Week

SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) 'First Day of Current Week'

----Last Day of Current Week

SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6) 'Last Day of Current Week'

----First Day of Last Week

SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) 'First Day of Last Week'

----Last Day of Last Week

SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6) 'Last Day of Last Week'

----First Day of Current Month

SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) 'First Day of Current Month'

----Last Day of Current Month

SELECT DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))) 'Last Day of Current Month'

----First Day of Last Month

SELECT DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) 'First Day of Last Month'

----Last Day of Last Month

SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))) 'Last Day of Last Month'

----First Day of Current Year

SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0) 'First Day of Current Year'

----Last Day of Current Year

SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0))) 'Last Day of Current Year'

----First Day of Last Year

SELECT DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) 'First Day of Last Year'

----Last Day of Last Year

SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))) 'Last Day of Last Year'

Koen Verbeeck SSC Guru Points: 259015 More actions · Answer 2

Koen Verbeeck

SSC Guru

Points: 259015

December 14, 2011 at 5:30 am

#1421188

praneshram (12/14/2011)

Finding Dates like first day and last day etc

...

Using DATEADD(ms,-3,expression) is sooooo SQL Server 2005 😉

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

ashley.wardell SSC Eights! Points: 983 More actions · Answer 3

ashley.wardell

SSC Eights!

Points: 983

December 14, 2011 at 5:47 am

#1421201

if you upgrade to new SQL 2012 you can use EOMONTH()

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

praneshram (12/14/2011)

----Last Day of Last Month

SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))) 'Last Day of Last Month'

Gosh no. Always find the first day of the next month and use "less than" instead of "between" to make your code "bullet proof".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Koen Verbeeck SSC Guru Points: 259015 More actions · Answer 5

Jeff Moden (12/14/2011)

praneshram (12/14/2011)

----Last Day of Last Month

SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))) 'Last Day of Last Month'

Gosh no. Always find the first day of the next month and use "less than" instead of "between" to make your code "bullet proof".

And use SYSDATETIME() instead of GETDATE() to get rid of that silly 3ms truncation.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

Koen Verbeeck (12/14/2011)

Jeff Moden (12/14/2011)

praneshram (12/14/2011)

----Last Day of Last Month

SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))) 'Last Day of Last Month'

Gosh no. Always find the first day of the next month and use "less than" instead of "between" to make your code "bullet proof".

And use SYSDATETIME() instead of GETDATE() to get rid of that silly 3ms truncation.

Nah... use whole dates for criteria and you don't have to worry about it. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

subbu1 Hall of Fame Points: 3221 More actions · Answer 7

subbu1

Hall of Fame

Points: 3221

December 17, 2011 at 11:41 pm

#1422884

pls try below code

declare @Year int

declare @mon int

declare @Main datetime

set @year=2012

set @mon=3

set @Main=convert(datetime,conert(varchar(10),@year)+convert(varchar(10),@mon)+'01')

select convert(varchar(10),dateadd(ss,-1,dateadd(mm,1,@Main)),120)

subbu1 Hall of Fame Points: 3221 More actions · Answer 8

subbu1

Hall of Fame

Points: 3221

December 17, 2011 at 11:47 pm

#1422885

declare @Year int

declare @mon int

declare @Main datetime

set @year=2012

set @mon=3

set @Main=convert(datetime,conert(varchar(10),@year)+'-'+convert(varchar(10),@mon)+'-'+'01')

select convert(varchar(10),dateadd(ss,-1,dateadd(mm,1,@Main)),120)

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 9

subbareddy542 (12/17/2011)

declare @Year int

declare @mon int

declare @Main datetime

set @year=2012

set @mon=3

set @Main=convert(datetime,conert(varchar(10),@year)+'-'+convert(varchar(10),@mon)+'-'+'01')

select convert(varchar(10),dateadd(ss,-1,dateadd(mm,1,@Main)),120)

Your code will produce an error, syntax error on conert(varchar(10)

Modified code:



declare @Year int

declare @mon int

declare @Main datetime

set @year=2012

set @mon=3

set @Main=convert(datetime,convert(varchar(10),@year)+'-'+ convert(varchar(10),@mon)+'-'+'01')

select convert(varchar(10),dateadd(ss,-1,dateadd(mm,1,@Main)),120)

But in most practical case you would not know the year or the month and you would need to calculate form the current date or a date in a table, i.e. OrderDate etc

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

VIG SSC-Addicted Points: 488 More actions · Answer 10

VIG

SSC-Addicted

Points: 488

December 18, 2011 at 10:25 pm

#1422954

declare @y int

declare @m int

select @y=2012,@m=2

select dateadd(month,(@y-1900)*12+@m,0)-1

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 11

VIG (12/18/2011)

declare @y int

declare @m int

select @y=2012,@m=2

select dateadd(month,(@y-1900)*12+@m,0)-1

Because there's no string conversions in that, that's one of the fastest was to do this... nicely done.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 12

Just thought I'd throw these in... they get rid of the behind-the-scenes operation of parenthetical grouping and remove one extra subtraction (for the month end calculation) for a tiny bit more speed (it would a fair number of rows to see a real difference). The "22800" number is 1900*12. The 22801 is similar and isn't a mistake. It gives one month less in the subtraction to find the first day of the month.

DECLARE @Year INT, @Month INT;



 SELECT @Year = 2012, @Month = 2;

 SELECT FirstDayOfMonth = DATEADD(MONTH,@Year*12-22801+@Month, 0),

        LastDayOfMonth  = DATEADD(MONTH,@Year*12-22800+@Month,-1)

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

VIG SSC-Addicted Points: 488 More actions · Answer 13

Jeff Moden (12/19/2011)

Just thought I'd throw these in... they get rid of the behind-the-scenes operation of parenthetical grouping and remove one extra subtraction (for the month end calculation) for a tiny bit more speed (it would a fair number of rows to see a real difference). The "22800" number is 1900*12. The 22801 is similar and isn't a mistake. It gives one month less in the subtraction to find the first day of the month.

DECLARE @Year INT, @Month INT;
SELECT @Year = 2012, @Month = 2; SELECT FirstDayOfMonth = DATEADD(MONTH,@Year*12-22801+@Month, 0), LastDayOfMonth = DATEADD(MONTH,@Year*12-22800+@Month,-1) ;

Cool ! But I do not like such solutions with "magic" numbers. It's a little bit faster, but more unclear

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 14

VIG (12/19/2011)

Jeff Moden (12/19/2011)

Just thought I'd throw these in... they get rid of the behind-the-scenes operation of parenthetical grouping and remove one extra subtraction (for the month end calculation) for a tiny bit more speed (it would a fair number of rows to see a real difference). The "22800" number is 1900*12. The 22801 is similar and isn't a mistake. It gives one month less in the subtraction to find the first day of the month.

DECLARE @Year INT, @Month INT;
SELECT @Year = 2012, @Month = 2; SELECT FirstDayOfMonth = DATEADD(MONTH,@Year*12-22801+@Month, 0), LastDayOfMonth = DATEADD(MONTH,@Year*12-22800+@Month,-1) ;

Cool ! But I do not like such solutions with "magic" numbers. It's a little bit faster, but more unclear

Absolutely understood on the "magic" numbers thing. Thanks for the feedback, Vig.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Koen Verbeeck SSC Guru Points: 259015 More actions · Answer 15

Jeff Moden (12/19/2011)

VIG (12/19/2011)

Jeff Moden (12/19/2011)

Just thought I'd throw these in... they get rid of the behind-the-scenes operation of parenthetical grouping and remove one extra subtraction (for the month end calculation) for a tiny bit more speed (it would a fair number of rows to see a real difference). The "22800" number is 1900*12. The 22801 is similar and isn't a mistake. It gives one month less in the subtraction to find the first day of the month.

DECLARE @Year INT, @Month INT;
SELECT @Year = 2012, @Month = 2; SELECT FirstDayOfMonth = DATEADD(MONTH,@Year*12-22801+@Month, 0), LastDayOfMonth = DATEADD(MONTH,@Year*12-22800+@Month,-1) ;

Cool ! But I do not like such solutions with "magic" numbers. It's a little bit faster, but more unclear

Absolutely understood on the "magic" numbers thing. Thanks for the feedback, Vig.

And that's why they invented comment lines, to explain the magic numbers 🙂

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP