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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase 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
BinuthCommented:

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PedroCGDCommented:
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.