Complicated Date Query - 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 2000 Forums

Transact-SQL (2000)

Complicated Date Query

Author

Topic

jefegrande
Starting Member

1 Post

Posted - 2006-08-17 : 10:55:00

I have a complex date query to figure out.
I need to:

1. Pull all of the dates from the current year
And
2. If it is within the first 15 days of the current year I need to pull all of last years dates as well.

I have part of the between statement:

BETWEEN ???? AND dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))

In Oracle I'd use the trunc statement, but I don't know how to do it in TSQL. I really appreciate the assist.

Mark F.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-08-17 : 11:08:56

this should do it.

Year(YouCol) BETWEEN Year(getdate()-15) AND Year(getdate())

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-17 : 11:40:36

Try


Where (Day(YouCol) >   convert(datetime, '01/15/'+ convert(varchar(10),Year(getdate()))) and Year(YouCol) = Year(getdate())) or 
	  (Day(YouCol) <=   convert(datetime, '01/15/'+ convert(varchar(10),Year(getdate()))) and Year(YouCol) in (Year(getdate()), Year(getdate())-1 ))

Srinika

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-17 : 20:12:00

[code]
where ( --within the first 15 days of the current year
getdate() < dateadd(year, datediff(year, 0, getdate()), 15) -- < this year Jan 16
and datecol >= dateadd(year, datediff(year, 0, getdate()) -1, 0) -- last year Jan 01
and datecol <= getdate()
)
or (
getdate() >= dateadd(year, datediff(year, 0, getdate()), 15) -- >= this year Jan 16
and datecol >= dateadd(year, datediff(year, 0, getdate()), 0) -- this year Jan 01
and datecol <= getdate()
)[/code]

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-08-18 : 08:32:50

Why is it nobody likes my simple query??

It seems like you guys are complicating the matter.

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-18 : 08:48:39

I don't think they get it. Maybe if is written like this?

Year(YourCol) BETWEEN YEAR(DATEADD(day, -15, GETDATE()) AND YEAR(GETDATE())

Peter Larsson
Helsingborg, Sweden

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-18 : 08:51:07

Seventhnight,

I doubt ur "simple query" answers the requirement.
urs is considering 15 yrs period, the question is on 2 conditions, the 2nd condition being first 15 days of the current yr.

Srinika

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-18 : 09:24:36

This should do it without having to run the date column through a function, something that prevents using an index.


select
	*
from
	MyTable
where
	-- GE Beginning of year, relative to 15 days ago
	MyDate >= dateadd(year,datediff(year,0,getdate()-15),0) and
	-- LT Beginning of next year
	MyDate < dateadd(year,datediff(year,0,getdate())+1,0)

CODO ERGO SUM

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-08-18 : 09:28:38

quote:
Originally posted by Srinika

Seventhnight,

I doubt ur "simple query" answers the requirement.
urs is considering 15 yrs period, the question is on 2 conditions, the 2nd condition being first 15 days of the current yr.

Srinika

See Peso's explanation of my post. I followed both conditions.

Maybe it would have been better to write it that way Peso... thanks for the backup

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-08-18 : 09:32:01

quote:
Originally posted by Michael Valentine Jones

This should do it without having to run the date column through a function, something that prevents using an index.

select
*
from
MyTable
where
-- GE Beginning of year, relative to 15 days ago
MyDate >= dateadd(year,datediff(year,0,getdate()-15),0) and
-- LT Beginning of next year
MyDate < dateadd(year,datediff(year,0,getdate())+1,0)

CODO ERGO SUM

Yeah... I suppose that would probably be a good idea

...

see color above - see he did it to...

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-18 : 09:36:47

quote:
Originally posted by Srinika

I doubt ur "simple query" answers the requirement.
urs is considering 15 yrs period, the question is on 2 conditions, the 2nd condition being first 15 days of the current yr.

No, since 15 is subtracted from GETDATE() before the year of the subtracted date is calculated.
Keep the count of parenthesis!

Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-18 : 09:39:37

quote:
Originally posted by Seventhnight

quote:
Originally posted by Srinika

Seventhnight,

I doubt ur "simple query" answers the requirement.
urs is considering 15 yrs period, the question is on 2 conditions, the 2nd condition being first 15 days of the current yr.

Srinika

See Peso's explanation of my post. I followed both conditions.

Maybe it would have been better to write it that way Peso... thanks for the backup

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

Your code should work, but it is almost always better to do date range queries in the form show below, because it allows SQL server to use an index, and it doesn't have to run each table date through a function.

select
	*
from
	Nytable a
where
	a.MyDate >= @StartDate(or funciton result) and
	a.MyDate <  @EndDate(or funciton result)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources