DateTime 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 2008 Forums

Transact-SQL (2008)

DateTime Query

Author

Topic

Mnemonic
Yak Posting Veteran

58 Posts

Posted - 2011-02-15 : 11:44:56

Hey Guys,

I'm looking to write a stored proc to return rows based on date. The current "ListingDate" column in my db is a datetime data type, and the format is like 2011-01-04 16:52:51.000.

So for something like returning a single day's results, i would have to use LIKE and not have the time in the query from what im guessing.

I tried this:

@DateRange varchar(250)
AS
BEGIN
SET NOCOUNT ON;
SELECT ListingID, ListingDate, ListingSubject, ListingDescriptionPreview, Salary, CityState, ActiveListing, UserName
FROM Listings
WHERE (ActiveListing = 1) AND ListingDate LIKE
CASE WHEN @DateRange = 'Today' THEN '%' + CONVERT( CHAR(8), GetDate(), 112) + '%'
END
Order by Listingdate Desc
END

But the problem is Print CONVERT( CHAR(12), GetDate(), 112) outputs the date like 20110215. I cant seem to find a format that is yyyy-mm-dd, but i guess this raises a bigger question, if this is the best way to do it.

I also need to use the same query with the case of current week, current month, current year, etc. I would appriciate if anyone had any insight in the best way to do that as well.

Thanks so much in advance!

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-02-15 : 11:52:58

If you use

 SELECT dateadd(dd,datediff(dd,0,ListingDate),0)

you can strip off the time component from your column.

then you can compare that to "today"

 SELECT dateadd(dd,datediff(dd,0,GETDATE()),0)

.

For other types (current week, year, etc), you will need to compute them out from GETDATE() and compare.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Mnemonic
Yak Posting Veteran

58 Posts

Posted - 2011-02-15 : 12:11:31

OK, thanks for that, I got the Today one to work by using the query below. However, for the current week (lets just call that todays date minus 7, I'd need to use dateadd(dd,datediff(dd,7,GETDATE()),0) to reverse the date 7 days and use greater than that date. I cant seem to get the case syntax correct though, since it wont allow me to put the LIKE or = or > inside the case statement. Any ideas?

@DateRange varchar(250)
AS
BEGIN
SET NOCOUNT ON;
SELECT ListingID, ListingDate, ListingSubject, ListingDescriptionPreview, Salary, CityState, ActiveListing, UserName
FROM Listings
WHERE (ActiveListing = 1) AND dateadd(dd,datediff(dd,0,ListingDate),0) LIKE
CASE WHEN @DateRange = 'Today' THEN dateadd(dd,datediff(dd,0,GETDATE()),0)
END
Order by Listingdate Desc
END

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-15 : 14:18:42

Well LIKE operator is for searching string datatypes. in your query you are trying to lookup a Date of format (2011-01-01) like 'Today' which is not valid. It seems that you are trying to incorporate a switch the condition on the basis of @dateRange variable .. if so one way to tackle this is to use IF Condition e.g.

If @dateRange = 'Today'
Begin
Select ...... where clause as per @daterange
End

If @dateRange <> 'Today'
Begin
Select ...... where clause as per @daterange
End

Cheers
MIK

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-02-15 : 15:00:52

listingDate >= dateadd(day,datediff(day,0,getdate()),0)
and listingdate < dateadd(day,datediff(day,-1,getdate()),0)

will give you everything that happend today, changing the -1 to -7 will give you everything in a week. Could you make that a parameter? Then you don't have to worry about case and like

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources