Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

What is this saying ? Expand / Collapse
Author
Message
Posted Monday, April 29, 2013 12:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 22, 2013 12:34 PM
Points: 9, Visits: 23
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))
Post #1447698
Posted Monday, April 29, 2013 12:43 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 8:25 AM
Points: 1,943, Visits: 20,160
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
Post #1447700
Posted Monday, April 29, 2013 12:47 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 8:25 AM
Points: 1,943, Visits: 20,160
good link here

http://www.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
Post #1447704
Posted Monday, April 29, 2013 1:04 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:21 AM
Points: 20,857, Visits: 32,875
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





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1447724
Posted Monday, April 29, 2013 1:42 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 22, 2013 12:34 PM
Points: 9, Visits: 23
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
Post #1447740
Posted Monday, April 29, 2013 1:50 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:21 AM
Points: 20,857, Visits: 32,875
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 again

steve


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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1447746
Posted Monday, April 29, 2013 2:19 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 22, 2013 12:34 PM
Points: 9, Visits: 23
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
Post #1447762
Posted Monday, April 29, 2013 2:30 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:21 AM
Points: 20,857, Visits: 32,875
steve4134 (4/29/2013)
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


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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1447771
Posted Monday, April 29, 2013 2:33 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:21 AM
Points: 20,857, Visits: 32,875
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1447772
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse