Cant select on date - 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 2005 Forums

Transact-SQL (2005)

Cant select on date

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2009-03-06 : 04:59:16

I wanted to find all records from yesterday

SELECT q_ID, q_userID, q_quoteref, q_name, q_vehicleID, q_dealParamID, q_salesCode, q_actualSalesCode, q_targetRental, q_date, q_dateSubmitted, q_actionedDate, q_status, q_genusAdminID, q_price, q_funderID, q_deleted, q_residualvalue, q_excessmileage, q_internalNotes, q_notes, q_hasbeenqueried, q_franchiseeID, q_hasbeenread
FROM tblQuotes
WHERE (q_actionedDate = '5/3/2009')

But it returns 0 rows even though there are some present.

Have I got the query wrong, q_actioneddate is type datetime

Thanks

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-06 : 05:03:07

Try this once

SELECT q_ID, q_userID, q_quoteref, q_name, q_vehicleID, q_dealParamID, q_salesCode, q_actualSalesCode, q_targetRental, q_date, q_dateSubmitted, q_actionedDate, q_status, q_genusAdminID, q_price, q_funderID, q_deleted, q_residualvalue, q_excessmileage, q_internalNotes, q_notes, q_hasbeenqueried, q_franchiseeID, q_hasbeenread
FROM tblQuotes
WHERE (q_actionedDate = '3/5/2009')

matty
Posting Yak Master

161 Posts

Posted - 2009-03-06 : 05:03:40

SELECT q_ID, q_userID, q_quoteref, q_name, q_vehicleID, q_dealParamID, q_salesCode, q_actualSalesCode, q_targetRental, q_date, q_dateSubmitted, q_actionedDate, q_status, q_genusAdminID, q_price, q_funderID, q_deleted, q_residualvalue, q_excessmileage, q_internalNotes, q_notes, q_hasbeenqueried, q_franchiseeID, q_hasbeenread
FROM tblQuotes
WHERE q_actionedDate >= '5/3/2009' AND q_actionedDate < '6/3/2009'

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-06 : 05:05:38

[code]SELECT q_ID, q_userID, q_quoteref, q_name, q_vehicleID, q_dealParamID, q_salesCode, q_actualSalesCode, q_targetRental, q_date, q_dateSubmitted, q_actionedDate, q_status, q_genusAdminID, q_price, q_funderID, q_deleted, q_residualvalue, q_excessmileage, q_internalNotes, q_notes, q_hasbeenqueried, q_franchiseeID, q_hasbeenread
FROM tblQuotes
WHERE (DATEADD(Day, DATEDIFF(Day, 0, q_actionedDate), 0) = '20090305')[/code]

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-06 : 05:06:25

Hi Matty, the dateformat is incorrect , I think He want Mar-5-2009 Records

matty
Posting Yak Master

161 Posts

Posted - 2009-03-06 : 05:11:20

quote:
Originally posted by Nageswar9

Hi Matty, the dateformat is incorrect , I think He want Mar-5-2009 Records

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-06 : 05:17:33

try like this
declare @date datetime
select @date ='3/5/2009'

SELECT q_ID, q_userID, q_quoteref, q_name, q_vehicleID, q_dealParamID, q_salesCode, q_actualSalesCode, q_targetRental, q_date, q_dateSubmitted, q_actionedDate, q_status, q_genusAdminID, q_price, q_funderID, q_deleted, q_residualvalue, q_excessmileage, q_internalNotes, q_notes, q_hasbeenqueried, q_franchiseeID, q_hasbeenread
FROM tblQuotes
WHERE q_actionedDate>=DATEADD(dd,DATEDIFF(dd,0,@date),0)
AND q_actionedDate<DATEADD(dd,DATEDIFF(dd,0,@date),1)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources