sql select statement for range of dates

[TechBoyJK]

Hi,

I'm working on the admin section of a website i'm building, and I'm trying to build some queries that get stats of record changes.

Basically I want to query how many records exist that will fall within certain date ranges.

so far, if I want to find records where a datefield has a value within the last month I use something like this

WHERE last_login_date > DATEADD(month,-1,GETDATE())

I'm still trying to figure out the best way to select other dates.

I need the following ranges

within the current day. even if its 1am on tuesday and the day is only 1 hour old, only stuff that matches today/tuesday
within the past hour
within the past 24 hours
within the past week
past month
past year

is dateadd the best function to use?

 

[JavaMomma]

dateadd is probably good, this is a query that will select everything on the current day - this is ugly though.
SELECT * from foo where
last_login_date > CAST(CAST((CAST(getdate() as binary) & CAST(0xffffffffff000000 as bigint)) as binary(8)) as datetime)

 

[TechBoyJK]

Originally posted by: JavaMomma
dateadd is probably good, this is a query that will select everything on the current day - this is ugly though.
SELECT * from foo where
last_login_date > CAST(CAST((CAST(getdate() as binary) & CAST(0xffffffffff000000 as bigint)) as binary(8)) as datetime)

i think that select statement just made baby jesus cry

there has to be a better way?

 

[JavaMomma]

Ya, made me cry as well.. try this

SELECT * from foo
where last_login_date > CONVERT(CHAR(8), GetDate(), 112)

 

[TechBoyJK]

Originally posted by: JavaMomma
Ya, made me cry as well.. try this

SELECT * from foo
where last_login_date > CONVERT(CHAR(8), GetDate(), 112)

whats the 112 for?

 

[JavaMomma]

Style...
http://technet.microsoft.com/e.../ms187928(SQL.90).aspx

 

[KLin]

Dateadd is what you want to use since it can handle different time/day increments. You can also use the style of 101 with a datatype of char(11) too within the convert when seeing logins for the current day.

 

[Hyperblaze]

Am I the only one who uses a Unix time stamp for time/dates?

 

[Hyperblaze]

Originally posted by: JavaMomma
dateadd is probably good, this is a query that will select everything on the current day - this is ugly though.
SELECT * from foo where
last_login_date > CAST(CAST((CAST(getdate() as binary) & CAST(0xffffffffff000000 as bigint)) as binary(8)) as datetime)

please tell me you wouldn't use this on a production environment?

 

[KLin]

Originally posted by: Hyperblaze

Originally posted by: JavaMomma
dateadd is probably good, this is a query that will select everything on the current day - this is ugly though.
SELECT * from foo where
last_login_date > CAST(CAST((CAST(getdate() as binary) & CAST(0xffffffffff000000 as bigint)) as binary(8)) as datetime)

please tell me you wouldn't use this on a production environment?

Hey if it works. But I agree, someone else would come around and see this and would say "what the hell?!?!" :laugh:

 

[Hyperblaze]

Originally posted by: KLin

Originally posted by: Hyperblaze

Originally posted by: JavaMomma
dateadd is probably good, this is a query that will select everything on the current day - this is ugly though.
SELECT * from foo where
last_login_date > CAST(CAST((CAST(getdate() as binary) & CAST(0xffffffffff000000 as bigint)) as binary(8)) as datetime)

please tell me you wouldn't use this on a production environment?

Hey if it works. But I agree, someone else would come around and see this and would say "what the hell?!?!" :laugh:

Okay, there are a few other requirements needed other then "it works!"

such as
1) load time
2) server load
3) efficiency

first suggestion is to get rid of "select *"

you should never EVER use "SELECT *"

unless of course you don't care about preformance

 

[JavaMomma]

Originally posted by: Hyperblaze

Originally posted by: JavaMomma
dateadd is probably good, this is a query that will select everything on the current day - this is ugly though.
SELECT * from foo where
last_login_date > CAST(CAST((CAST(getdate() as binary) & CAST(0xffffffffff000000 as bigint)) as binary(8)) as datetime)

please tell me you wouldn't use this on a production environment?

I wouldn't use it even though it is apparently fast. It is just way to ugly... found it on some blog.

I agree don't use "select *"

 

[Hyperblaze]

Originally posted by: JavaMomma

Originally posted by: Hyperblaze

Originally posted by: JavaMomma
dateadd is probably good, this is a query that will select everything on the current day - this is ugly though.
SELECT * from foo where
last_login_date > CAST(CAST((CAST(getdate() as binary) & CAST(0xffffffffff000000 as bigint)) as binary(8)) as datetime)

please tell me you wouldn't use this on a production environment?

I wouldn't use it even though it is apparently fast. It is just way to ugly... found it on some blog.

I agree don't use "select *"

curious question, what do you find "ugly" about it?

 

[JavaMomma]

The syntax of the casts look ugly yes, but that isn't a big deal.
CAST(getdate() as binary) & CAST(0xffffffffff000000 as bigint)

It isn't very clear that this returns todays date. Also, it makes an assumption that the hardcoded binary value 0xffffffffff000000 when &ed with the getdate() will generate the wanted value... Using a built in API calls would be safer. I feel ashamed for originally posting that code without thinking about it...

 

[Snapster]

Originally posted by: Hyperblaze
Am I the only one who uses a Unix time stamp for time/dates?

Nope : ) I've seen too many nightmare scenarios of differing US/UK server environments ...

 

[Hyperblaze]

Originally posted by: JavaMomma
The syntax of the casts look ugly yes, but that isn't a big deal.
CAST(getdate() as binary) & CAST(0xffffffffff000000 as bigint)

It isn't very clear that this returns todays date. Also, it makes an assumption that the hardcoded binary value 0xffffffffff000000 when &ed with the getdate() will generate the wanted value... Using a built in API calls would be safer. I feel ashamed for originally posting that code without thinking about it...

just a word of precaution.

if you post code like that a few without further information

1) people assume it's your code
2) people assume you approve of this code even if it's ugly (which in turn doesn't reflect good light on you)

 

[awal]

I'd suggest reading the following article...

http://www.databasejournal.com...t-SQL-Server-Dates.htm