Date Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Date Question

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-10-17 : 11:11:09

How can I get the first day of the current month - 1 month?
and the first day of the current month?

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-17 : 11:13:46

[code]SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,0) AS PrevMonthStartDay,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) AS CurrMonthStartDay[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-10-17 : 11:47:41

Thanks Visa...!
The following query returns all of "last months" records, which is good. This will work fine in most cases, except for the month of January.

How can I reteive a months records with last years date if January?
Hopefully I am explaining my situation correctly....

select * from test
where LastDate > DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,0)
and LastDate <= DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-10-17 : 12:19:31

If the current day falls within the month of January, you want to get the entire previous year?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-17 : 12:37:55

quote:
Originally posted by qman

Thanks Visa...!
The following query returns all of "last months" records, which is good. This will work fine in most cases, except for the month of January.

How can I reteive a months records with last years date if January?
Hopefully I am explaining my situation correctly....

select * from test
where LastDate > DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,0)
and LastDate <= DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)

sorry not clear

if its january, what should be the period of data returned?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-10-17 : 13:09:35

For instance, if today was Jan 1, 2012.
I would like to retreive the previous months records, from Dec 1, 2011 through Jan 1, 2012.

I was trying to nest the dateadd function, but I am get "Argument data type datetime is invalid for argument 2 of dateadd function."

select * from test
where LastDate > DATEADD(yy, DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,0) -1,0)
and LastDate <= DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-17 : 13:17:24

quote:
Originally posted by qman

For instance, if today was Jan 1, 2012.
I would like to retreive the previous months records, from Dec 1, 2011 through Jan 1, 2012.

I was trying to nest the dateadd function, but I am get "Argument data type datetime is invalid for argument 2 of dateadd function."

select * from test
where LastDate > DATEADD(yy, DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,0) -1,0)
and LastDate <= DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)

you can do the same thing.

see


declare @date datetime

set @date='20120101' -- your date value
select DATEADD(mm,DATEDIFF(mm,0,@date)-1,0) as start,DATEADD(mm,DATEDIFF(mm,0,@date),0) as [end]


output
-----------------------
start	                end
2011-12-01 00:00:00.000	2012-01-01 00:00:00.000

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-17 : 13:18:17

and condition should be


select * from test
where LastDate >= DATEADD(mm,DATEDIFF(mm,0,@date)-1,0)
and LastDate < DATEADD(mm,DATEDIFF(mm,0,@date),0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-10-17 : 14:44:00

Great, thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-17 : 14:51:27

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-10-17 : 15:01:43

Hey Visa,

When using DATEDIFF(mm,0,@date).... Howt does the 0 work?
I expect the datediff function to tell you the difference between the arguments passed to it. Not sure how 0 works?

Thanks
Q

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-17 : 15:06:30

quote:
Originally posted by qman

Hey Visa,

When using DATEDIFF(mm,0,@date).... Howt does the 0 work?
I expect the datediff function to tell you the difference between the arguments passed to it. Not sure how 0 works?

Thanks
Q

0 represents base date which is 1900-01-01 so it gives you number of months elapsed since base date

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-10-17 : 15:11:49

Confusing but interesting...
Thanks again!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-18 : 00:28:03

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources