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]?
alexkingAsked:
Who is Participating?
 
BinuthConnect With a Mentor Commented:

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)

Open in new window

0
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
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
 
PedroCGDConnect With a Mentor Commented:
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Aneesh RetnakaranDatabase AdministratorCommented:
if you are concerned abt the performance, dont go for casting, try my solution
0
 
alexkingAuthor Commented:
In the cast what is the difference between 101 and 102? I am in the UK and use the format dd-mm-yy
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
alexking,

did u try my answer?

aneeshattingal
0
 
rameshvelayudhanData ArchitectCommented:
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
 
alexkingAuthor Commented:
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
 
PedroCGDCommented:
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
 
alexkingAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.