April 29, 2013 at 12:41 pm
I believe this is saying Last day of previous month and first day of current month ? Can someone please confirm ?
Steve
DATEADD(month, -1,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),101))
and
DATEADD(month, -1,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))),DATEADD(mm,1,GETDATE())),101))
April 29, 2013 at 12:43 pm
steve4134 (4/29/2013)
I believe this is saying Last day of previous month and first day of current month ? Can someone please confirm ?Steve
DATEADD(month, -1,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),101))
and
DATEADD(month, -1,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))),DATEADD(mm,1,GETDATE())),101))
run this in SSMS
select DATEADD(month, -1,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),101))
select DATEADD(month, -1,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))),DATEADD(mm,1,GETDATE())),101))
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 29, 2013 at 12:47 pm
good link here
http://qa.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 29, 2013 at 1:04 pm
From the original post:
select
GETDATE() CurrentDate,
DATEADD(month, -1,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),101)) FirstOfPreviousMonth,
DATEADD(month, -1,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))),DATEADD(mm,1,GETDATE())),101)) [LastOfPreviousMonth-NOT]
Results:
CurrentDate FirstOfPreviousMonth LastOfPreviousMonth-NOT
----------------------- ----------------------- -----------------------
2013-04-29 12:58:22.217 2013-03-01 00:00:00.000 2013-03-30 00:00:00.000
What you probably want:
select
getdate() CurrentDate,
dateadd(month, datediff(month, 0, getdate()) - 1, 0) FirstOfPreviousMonth,
dateadd(month, datediff(month, 0, getdate()), -1) LastOfPreviousMonth
Results:
CurrentDate FirstOfPreviousMonth LastOfPreviousMonth
----------------------- ----------------------- -----------------------
2013-04-29 12:58:22.217 2013-03-01 00:00:00.000 2013-03-31 00:00:00.000
And if you are generating dates to bracket a month for a WHERE clause I would use the following:
select
getdate() CurrentDate,
dateadd(month, datediff(month, 0, getdate()) - 1, 0) FirstOfPreviousMonth,
dateadd(month, datediff(month, 0, getdate()), 0) FirstOfCurrentMonth
And use it this way:
...
WHERE
MyDateCol >= FirstOfPreviousMonth and
MyDateCol < FirstOfCurrentMonth
April 29, 2013 at 1:42 pm
I can not express the joy that comes to me when I see a community come out and help someone so quick. I thank you again. I was correct in my assumptions last month first start date to last date of last month. Thanks again
steve
April 29, 2013 at 1:50 pm
steve4134 (4/29/2013)
I can not express the joy that comes to me when I see a community come out and help someone so quick. I thank you again. I was correct in my assumptions last month first start date to last date of last month. Thanks againsteve
You did look at the results of the code you posted, right? What you posted returned 3/1/2013 and 3/30/2013 using today (4/29/2013). Not the first and last day of the previous month.
If that is what you need, look at the code I provided.
April 29, 2013 at 2:19 pm
Yep Lynn
I just wanted to confirm that what I had wrote was indeed from last month threw the end of the month ran today. So if I ran this in May it would be April 1st to last day of April.
Thanks again
steve
April 29, 2013 at 2:30 pm
steve4134 (4/29/2013)
Yep LynnI just wanted to confirm that what I had wrote was indeed from last month threw the end of the month ran today. So if I ran this in May it would be April 1st to last day of April.
Thanks again
steve
Just to be sure you actually understand, the code you posted will NOT always give you the last day of the previous month.
If you don't believe me, try it yourself with various dates using the following code changing the value for the variable @ThisDate:
declare @ThisDate datetime;
set @ThisDate = '20130430 14:25';
select
@ThisDate CurrentDate,
DATEADD(month, -1,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@ThisDate)-1),@ThisDate),101)) FirstOfPreviousMonth,
DATEADD(month, -1,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@ThisDate))),DATEADD(mm,1,@ThisDate)),101)) [LastOfPreviousMonth-NOT]
Modified for SQL Server 2005.
April 29, 2013 at 2:33 pm
Also, if the column(s) you are testing the dates against have time values other than 00:00:00.000, you will miss data at the end of the month.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply