Solved

# How do I Compare Dates without the time component in an SQL qeury?

Posted on 2008-11-12
663 Views
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]?
0
Question by:alexking

LVL 75

Assisted Solution

Hello alexking,

add a day to the end date
SET @ToDate = @ToDate+1

SELECT  [LedgerDate]
,[Quantity]
,[Description]
FROM [MyDb].[dbo].[vw_Ledger]
WHERE [ledgerdate]>= @FromDate AND
[ledgerdate]<  @ToDate

GOD BLESS,

Aneesh R.
0

LVL 14

Accepted Solution

``````SELECT

[LedgerDate]

,[Quantity]

,[Description]

FROM [MyDb].[dbo].[vw_Ledger]

WHERE

cast(convert(varchar,[ledgerdate],101) as datetime) >= cast(convert(varchar,@FromDate,101) as datetime) AND

cast(convert(varchar,[ledgerdate],101) as datetime) <= cast(convert(varchar,@ToDate,101) as datetime)
``````
0

LVL 22

Assisted Solution

What I use and it works for me is:

SELECT  [LedgerDate]
,[Quantity]
,[Description]
FROM [MyDb].[dbo].[vw_Ledger]

WHERE CONVERT(datetime, CONVERT(varchar(10), ledgerdate, 102), 102)) >= (CONVERT(datetime, @FromDate, 102) AND
CONVERT(datetime, CONVERT(varchar(10), ledgerdate, 102), 102)) < (CONVERT(datetime, @ToDate, 102)

Helped?
regards!
0

LVL 75

Expert Comment

if you are concerned abt the performance, dont go for casting, try my solution
0

Author Comment

In the cast what is the difference between 101 and 102? I am in the UK and use the format dd-mm-yy
0

LVL 75

Expert Comment

alexking,

aneeshattingal
0

Expert Comment

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,[ledgerdate],112) >= CONVERT(VARCHAR,@FromDate,112)
AND CONVERT(VARCHAR,[ledgerdate],112) < CONVERT(VARCHAR,@ToDate+1,112)
0

Author Comment

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)?
0

LVL 22

Expert Comment

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..
0

Author Closing Comment

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.
0

## Featured Post

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Auâ€¦
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.