How do I Compare Dates without the time component in an SQL qeury?
I have a datatype datetime that stores a ledger date. It does also store the time of the transaction, so
15-10-2008 13:45 etc
I need to get all transactions between 2 dates ignoring the time, so
SELECT [LedgerDate]
,[Quantity]
,[Description]
FROM [MyDb].[dbo].[vw_Ledger]
WHERE [ledgerdate]>= @FromDate AND
[ledgerdate]<= @ToDate
Where @FromDate are passed in format 01-10-2008 and 30-10-2008, no times
How do I make it ignore the time in the [Ledgerdate]?
15-10-2008 13:45 etc
I need to get all transactions between 2 dates ignoring the time, so
SELECT [LedgerDate]
,[Quantity]
,[Description]
FROM [MyDb].[dbo].[vw_Ledger]
WHERE [ledgerdate]>= @FromDate AND
[ledgerdate]<= @ToDate
Where @FromDate are passed in format 01-10-2008 and 30-10-2008, no times
How do I make it ignore the time in the [Ledgerdate]?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Aneesh
if you are concerned abt the performance, dont go for casting, try my solution
ASKER
In the cast what is the difference between 101 and 102? I am in the UK and use the format dd-mm-yy
alexking,
did u try my answer?
aneeshattingal
did u try my answer?
aneeshattingal
Alex, if you need to ignore the time part, you may have to use a cast or convert function with the datetime filed. you may try the following code:
SELECT [LedgerDate]
,[Quantity]
,[Description]
FROM [MyDb].[dbo].[vw_Ledger]
WHERE CONVERT(VARCHAR,[ledgerdat
AND CONVERT(VARCHAR,[ledgerdat
SELECT [LedgerDate]
,[Quantity]
,[Description]
FROM [MyDb].[dbo].[vw_Ledger]
WHERE CONVERT(VARCHAR,[ledgerdat
AND CONVERT(VARCHAR,[ledgerdat
ASKER
Can someone tell me if it will make any difference if I use Convert with 101 or 102 for the purpose of this comparison if I use the format dd-mm-yyyy (UK format)?
is the same...
select convert(datetime, getdate(),101)
select convert(datetime, getdate(),102)
The more important is the format you have configured in your SQL..
select convert(datetime, getdate(),101)
select convert(datetime, getdate(),102)
The more important is the format you have configured in your SQL..
ASKER
Thankyou you all gave me the answer and all solutions seem to work - most important I have also learnt more about it so thankyou all.