Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 673
  • Last Modified:

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]?
0
alexking
Asked:
alexking
  • 3
  • 3
  • 2
  • +2
3 Solutions
 
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
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
rameshvelayudhanCommented:
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now