Steffee
asked on
Datetime - extracting date part in sql server only
I am having a lot of problems extracting all records with a despatch date of today. It is a date time field and so far after a lot of searching I can not get ot to match on the date half only. This is my query....
SELECT dbo.tblJob.jbID, dbo.tblJob.jbQuantity, dbo.tblJob.jbPriority,
dbo.tblJob.jbDestWH, dbo.tblJob.jbDestShelf,
dbo.tblJob.jbISBN, dbo.tblJob.jbDateIn,
dbo.tblJob.jbDespatched, dbo.tblJob.jbOnManifest,
dbo.tblJob.jbFinalQuantity
dbo.tblJob.jbDespatchedWhe
dbo.tblBook.bkAuthor, dbo.tblBook.bkTitle
FROM dbo.tblJob INNER JOIN
dbo.tblBook ON
dbo.tblJob.jbISBN = dbo.tblBook.bkISBN
WHERE (dbo.tblJob.jbDespatched = 1) AND (dbo.tblJob.jbID > 200)
AND (dbo.tblJob.jbOnManifest = 0) AND
(dbo.tblJob.jbDespatchedWh
103))
I have tried so many things, but with no luck so far. What is wrong with the where part of this query. Your help would be much appreciated.
Thank You
SELECT dbo.tblJob.jbID, dbo.tblJob.jbQuantity, dbo.tblJob.jbPriority,
dbo.tblJob.jbDestWH, dbo.tblJob.jbDestShelf,
dbo.tblJob.jbISBN, dbo.tblJob.jbDateIn,
dbo.tblJob.jbDespatched, dbo.tblJob.jbOnManifest,
dbo.tblJob.jbFinalQuantity
dbo.tblJob.jbDespatchedWhe
dbo.tblBook.bkAuthor, dbo.tblBook.bkTitle
FROM dbo.tblJob INNER JOIN
dbo.tblBook ON
dbo.tblJob.jbISBN = dbo.tblBook.bkISBN
WHERE (dbo.tblJob.jbDespatched = 1) AND (dbo.tblJob.jbID > 200)
AND (dbo.tblJob.jbOnManifest = 0) AND
(dbo.tblJob.jbDespatchedWh
103))
I have tried so many things, but with no luck so far. What is wrong with the where part of this query. Your help would be much appreciated.
Thank You
ASKER
The column has datetime which is 24/07/2003 00:00:00 format. I need to extract everything with todays date 24/07/2003 ignoring the time part of the field as I can't specifically match that.
When I try to use sysdate() it says it is an unrecognised function.
Thanks
When I try to use sysdate() it says it is an unrecognised function.
Thanks
use sysdate without the brackets .
This should work :
SELECT dbo.tblJob.jbID, dbo.tblJob.jbQuantity, dbo.tblJob.jbPriority,
dbo.tblJob.jbDestWH, dbo.tblJob.jbDestShelf,
dbo.tblJob.jbISBN, dbo.tblJob.jbDateIn,
dbo.tblJob.jbDespatched, dbo.tblJob.jbOnManifest,
dbo.tblJob.jbFinalQuantity
dbo.tblJob.jbDespatchedWhe
dbo.tblBook.bkAuthor, dbo.tblBook.bkTitle
FROM dbo.tblJob INNER JOIN
dbo.tblBook ON
dbo.tblJob.jbISBN = dbo.tblBook.bkISBN
WHERE (dbo.tblJob.jbDespatched = 1) AND (dbo.tblJob.jbID > 200)
AND (dbo.tblJob.jbOnManifest = 0) AND (trunc(dbo.tblJob.jbDespat
This should work :
SELECT dbo.tblJob.jbID, dbo.tblJob.jbQuantity, dbo.tblJob.jbPriority,
dbo.tblJob.jbDestWH, dbo.tblJob.jbDestShelf,
dbo.tblJob.jbISBN, dbo.tblJob.jbDateIn,
dbo.tblJob.jbDespatched, dbo.tblJob.jbOnManifest,
dbo.tblJob.jbFinalQuantity
dbo.tblJob.jbDespatchedWhe
dbo.tblBook.bkAuthor, dbo.tblBook.bkTitle
FROM dbo.tblJob INNER JOIN
dbo.tblBook ON
dbo.tblJob.jbISBN = dbo.tblBook.bkISBN
WHERE (dbo.tblJob.jbDespatched = 1) AND (dbo.tblJob.jbID > 200)
AND (dbo.tblJob.jbOnManifest = 0) AND (trunc(dbo.tblJob.jbDespat
ASKER
This gives the error that trunc is not recognised...
which version of Oracle are you using ?
ASKER
I am using sql server, has this question gone into the wrong section?
My apologies if this is so.
My apologies if this is so.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
convert(varchar(10),getdat
Do you need everything in one day ?
you can use AND (trunc(dbo.tblJob.jbDespat
using GETDATE() is wrong, you need to use SYSDATE