Select Dates Since 1st Day of August

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 2005 Forums

Transact-SQL (2005)

Select Dates Since 1st Day of August

Author

Topic

Veronica
Starting Member

6 Posts

Posted - 2009-02-11 : 05:58:36

Hi Everyone,

Say I have a date field Table1.OpDate

What do I put in the WHERE clause to bring in all the days since the 1st of August ?. Now, if say we are in February, then it would bring in all the days since the 1st of August of last year, otherwise, if we are say in October, it should bring in all the days since the 1st of August of the current year. Any ideas anyone ?

Thanks in advance,
Veronica

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-11 : 06:01:25

declare @fromDate DATETIME,
@toDate DATETIME

select @fromdate = '1/8/2008',@todate= GETDATE()

SELECT ROW_NUMBER() OVER(ORDER BY number) AS Id,DATEADD(DAY, number, @fromDate) AS 'DatesToWork'
FROM Master..spt_values
WHERE type = 'P'
AND DATEADD(DAY, number, @fromDate) <= @toDate

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-02-11 : 06:13:13

[code]
declare @date datetime
select @date = case when datepart(m,getdate()) < 8 then '08/01/2008' else '08/01/2009' end
select * from urtable where createdon >= @date

[/code]

Veronica
Starting Member

6 Posts

Posted - 2009-02-11 : 06:15:30

That's wonderful thank you bklr and Jai, and apologies I'm not well versed in SQL, but I have a table with a date field in it (say Table1.OpDate) that I need it to use getdate() or something similar in the WHERE clause so it works out the dates since the 1st of August in a dynamic way regardless of what year I'm in (the above queries return it from 2008 as I understand, but I need it to be dynamic to run for any year)

Ideally to be in this way :

SELECT Table1.OpDate
FROM Table1
WHERE Table1.OpDate >= <insert logic here>

... is this possible at all ?.

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-02-11 : 06:16:33

Dynamic SQL is not efficient

Jai Krishna

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-11 : 06:19:09

I believe the requirement was to calculate the breakpoint?

SELECT		v.Number AS DaysAgo,
		DATEADD(DAY, -v.Number, d.dtToday) AS theDate
FROM		(
			SELECT	DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()) - DATEDIFF(MONTH, '19000801', GETDATE()) % 12, '19000101') AS dtThen,
				DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()), '19000101') AS dtToday
		) AS d
INNER JOIN	master..spt_values AS v ON v.Type = 'P'
WHERE		v.Number <= DATEDIFF(DAY, d.dtThen, d.dtToday)

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-11 : 06:20:54

[code]DECLARE @FromDate DATETIME,
@ToDate DATETIME

SELECT @FromDate = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()) - DATEDIFF(MONTH, '19000801', GETDATE()) % 12, '19000101'),
@ToDate = DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()), '19000102')

SELECT *
FROM Table1
WHERE opDate >= @FromDate
AND opDate < @ToDate[/code]

E 12°55'05.63"
N 56°04'39.26"

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-11 : 06:21:24

SELECT Table1.OpDate
FROM Table1
WHERE Table1.OpDate >= '8/1/2008' and table1.opdate <= convert(varchar(32),getdate(),101)

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-02-11 : 06:28:23

[code]
Try this

select * from urtable where opdate >= case when datepart(m,getdate()) < 8 then '08/01/'+convert(varchar(66),datepart(yy,getdate())-1) else '08/01/'+convert(varchar(66),datepart(yy,getdate())) end

Jai Krishna

[/code]

Veronica
Starting Member

6 Posts

Posted - 2009-02-11 : 06:42:31

Just a big thank you to ALL !! it works ! I now managed to get the results I wanted back !.

Regards,
Veronica

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-02-11 : 06:43:48

Welcome

Jai Krishna

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources