Date Range for current year to date 1

[titans4771]

I have created a report and currently entering the date range needed, what I want to do is be able to run this report for the current year as below:

checkdate between '2009-01-01' and getdate()

How can I have the date range entered without having to enter a date in for the (2009-01-01)?

Thanks!

 

[RiverGuy]

checkdate BETWEEN CONVERT(DATETIME, CONVERT(CHAR(4), YEAR(GETDATE())) + '0101') AND GETDATE()

 

[titans4771]

Thanks!!

 

[r937]

checkdate BETWEEN DATEADD(year,DATEDIFF(year,'1969-07-20', GETDATE()),'1969-07-20')
AND GETDATE()

datetime arithmetic is a lot faster than converting to string and converting back to datetime

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[RiverGuy]

checkdate BETWEEN DATEADD(year,DATEDIFF(year,'1969-07-20', GETDATE()),'1969-07-20')
AND GETDATE()

datetime arithmetic is a lot faster than converting to string and converting back to datetime

a. Your formula is incorrect, unless you want your start date to be July 20 2009.

b. The two methods won't make a bit of difference as noticeable to the end-user. We're talking about a single comparison here.

 

[JeffModen]

But, if a uses finds that formula and used it for something that isn't a single comparison, there will be a marked difference. The current user isn't always the one affected. ;-)

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row

 

[r937]

Your formula is incorrect, unless you want your start date to be July 20 2009.

in the immortal words of phil mickelson, i am such an idiot


of course, i meant to write this --

checkdate BETWEEN DATEADD(year,DATEDIFF(year,'1969-01-01', GETDATE()),'1969-01-01')
AND GETDATE()

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[JeffModen]

Why not just use the SQL Base Date for this?

checkdate BETWEEN DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)
              AND GETDATE()

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row

 

[r937]

Why not just use the SQL Base Date for this?

because it's not apparent that 0 is a date


i prefer self-evident code (when i don't botch it up)




r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[JeffModen]

What's self evident about "1969-01-01"?

"0" is the known base date for SQL Server. It's usage is no more obtuse than using a combination of DateAdd/DateDiff to arrive at the first of the year.

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row

 

[r937]

jeff, i don't want to argue with you, because i agree with you

however, i would like to respond to your points


> What's self evident about "1969-01-01"?

for one thing, it is evident that it is a date

maybe it was a poor choice of year (you might have noticed i was going for a different base date)


> "0" is the known base date for SQL Server.

only by those who know this

it took me by surprise when i first learned about it, and i had been using sql server for years


> It's usage is no more obtuse than using a combination of DateAdd/DateDiff to arrive at the first of the year.

i disagree here, i believe "0" as a date is more obtuse than understanding that DATEDIFF counts boundaries






r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[JeffModen]

Heh... Ok. Then let's agree to disagree. Wait... you said you agree. Ok... we'll just disagree on Tuesdays. ;-)

Thanks for taking the time to post the feedback. Seriously. A lot of folks don't take the time.

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row