Solved

SQL Query - Compare Date Part of DateTime Value in Table

Posted on 2013-12-06
12
3,551 Views
Last Modified: 2013-12-13
Good Day Experts!

It feels like my little quandry is feasible, but I have been unable to find a solution for it.

I have SQL table data in a date column:
12/6/2013 11:48:03 AM
12/6/2013 10:05:43 AM
12/6/2013 9:25:07 AM

The User enters a Date for Start and End.  
When I build my query, how do I just compare the date part of the above data?

If the User enters 12/6/2013 for both Start and End dates, I want to return all the above values.

Thanks,
jimbo99999
0
Comment
Question by:Jimbo99999
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +3
12 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39701416
This is somewhat dependent on what version of SQL you are using.  SQL 2008 introduced a new DATE datatype that does not have a time component so the fastest way now to strip the time component from a DATETIME is to convert to DATE.

SELECT ...
FROM myTable
WHERE CAST(dateColumn AS DATE) >= @StartDate
   AND CAST(dateColumn AS DATE) <= @EndDate
0
 

Author Comment

by:Jimbo99999
ID: 39701423
Ok, ours is pre-2008.
0
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 250 total points
ID: 39701463
Another option...

SELECT ...
FROM myTable
WHERE DATEADD(DAY, 0, DATEDIFF(DAY, 0, dateColumn)) >= @StartDate
   AND DATEADD(DAY, 0, DATEDIFF(DAY, 0, dateColumn)) <= @EndDate
0
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
LVL 15

Expert Comment

by:JimFive
ID: 39702212
Pre 2008 you can CAST(CAST(datefield as INT) as datetime) to strip off the time.
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39702218
Casting to INT rounds up to the next day if the time is after 12:00PM

DECLARE @date DATETIME
SET @date = '1/1/2013 13:00'

SELECT CAST(CAST(@date AS INT) AS DATETIME)

returns '1/2/2013'
0
 
LVL 15

Assisted Solution

by:JimFive
JimFive earned 75 total points
ID: 39702221
Based on your question you can do this:

WHERE datefield between @StartDate and DateAdd(day, 1, @EndDate)

Adding 1 to the EndDate works because @EndDate has a time of midnight.  If you are concerned about things happening at midnight then use
datefield > @StartDate and Datefield < DateAdd(day,1,@EndDate)
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39702239
If performance is an issue than manipulating your parameters would make more efficient use of an index on the date field.

SELECT @EndDate =
   CASE
      WHEN @StartDate = @EndDate THEN DATEADD(DAY, 2, @EndDate)
      ELSE DATEADD(DAY, 1, @EndDate)
   END

SELECT ...
FROM myTable
WHERE dateColumn >= @StartDate
   AND dateColumn < @EndDate
0
 
LVL 11

Assisted Solution

by:SAMIR BHOGAYTA
SAMIR BHOGAYTA earned 50 total points
ID: 39703937
hi.. can you try this example

Yes you can still be able to compare them but this is certainly a bad practice because you will need to convert this strings into dates data type to be able to compare between them. If you have indexes define on the column, they will not be used anymore since the column will be converted and it will cuase slow performance on large database.

An example on comparing dates is like this:

SELECT *
FROM   tableName
WHERE  CONVERT(DATETIME, dateSTRColumn, XXX) > GETDATE()

where XXX is the current format of the date stored as string.
0
 

Author Comment

by:Jimbo99999
ID: 39706001
Thanks everyone for your repsonses.  We had other trouble last Friday that had to be put out.  So, I am just gettign back to this little project of mine.
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 75 total points
ID: 39706362
You never want to perform a function on a table column unless it's absolutely unavoidable.  Using a function on a column is not a "choice" -- it's an absolute last resort.


DECLARE @Start_Date datetime
DECLARE @End_Date datetime
SET @Start_Date = '<user_entered_value>'
SET @End_Date = '<user_entered_value>'

SELECT
FROM dbo.tablename
WHERE
    date_column >= DATEADD(DAY, DATEDIFF(DAY, 0, @Start_Date), 0) AND
    date_column < DATEADD(DAY, DATEDIFF(DAY, 0, @End_Date) + 1, 0)
0
 
LVL 10

Assisted Solution

by:Monica P
Monica P earned 50 total points
ID: 39713425
Make use of below logic if suits., i have used like this ..

just append the time to the date from the code as like below
'12/9/2013 00:00:00' and '12/9/2013 23:59:59'

Pass this two values to the query as below.

SELECT ...
FROM myTable
WHERE  datecolumn between '12/9/2013 00:00:00' and '12/9/2013 23:59:59' 

Open in new window

0
 

Author Closing Comment

by:Jimbo99999
ID: 39716676
Thanks for all the help everyone. I have learned quite a bit about dates.  I will file this question in my knowledge base for future reference.

Thanks,
jimbo99999
0

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question