HOWTO - Compare a date string to datetime in SQL Server?

Question

In SQL Server I have a DATETIME column which includes a time element.

Example:

'14 AUG 2008 14:23:019'

What is the best method to only select the records for a particular day, ignoring the time part?

Example: (Not safe, as it does not match the time part and returns no rows)

DECLARE  @p_date DATETIME
SET      @p_date = CONVERT( DATETIME, '14 AUG 2008', 106 )

SELECT   *
FROM     table1
WHERE    column_datetime = @p_date

Note: Given this site is also about jotting down notes and techniques you pick up and then forget, I'm going to post my own answer to this question as DATETIME stuff in MSSQL is probably the topic I lookup most in SQLBOL.

---

Update Clarified example to be more specific.

---

Edit Sorry, But I've had to down-mod WRONG answers (answers that return wrong results).

@Jorrit: WHERE (date>'20080813' AND date<'20080815') will return the 13th and the 14th.

@wearejimbo: Close, but no cigar! badge awarded to you. You missed out records written at 14/08/2008 23:59:001 to 23:59:999 (i.e. Less than 1 second before midnight.)

Answer 1

Technique 1:

 DECLARE @p_date DATETIME
 SET     @p_date = CONVERT( DATETIME, '14 AUG 2008', 106 )

 SELECT  *
 FROM    table1
 WHERE   column_datetime >= @p_date
 AND     column_datetime < DATEADD(d, 1, @p_date)

The advantage of this is that it will use any index on 'column_datetime' is it exists.

Answer 2

Ugly, but basic:

Convert to a date by whatever format it's in.

Convert back to a string using a format that only has YMD.

Look up convert in books online.

Answer 3

SELECT  *
FROM    table1
WHERE   CONVERT(varchar(10),columnDatetime,121) = 
        CONVERT(varchar(10),CONVERT('14 AUG 2008' ,smalldatetime),121)

This will convert the datatime and the string into varchars of the format "YYYY-MM-DD".

This is very ugly, but should work

Answer 4

Technique 2:

DECLARE @p_date DATETIME
SET     @p_date = CONVERT( DATETIME, '14 AUG 2008', 106 )

SELECT  *
FROM    table1
WHERE   DATEDIFF( d, column_datetime, @p_date ) = 0

If the column_datetime field is not indexed, and is unlikely to be (or the index is unlikely to be used) then using DATEDIFF() is shorter.

Answer 5

You can select records with the following WHERE statement: WHERE (date>'20080813' AND date<'20080815')

Indeed you can use the CONVERT function in your sql statement to format your date to whatever format you like.

There is a MSDN article about the CONVERT function right here: http://msdn.microsoft.com/en-us/library/ms187928.aspx

Answer 6

Just compare the year, month and day values.

Declare @DateToSearch DateTime
Set @DateToSearch = '14 AUG 2008'

SELECT * 
FROM table1
WHERE Year(column_datetime) = Year(@DateToSearch)
      AND Month(column_datetime) = Month(@DateToSearch)
      AND Day(column_datetime) = Day(@DateToSearch)

Answer 7

This function Cast(Floor(Cast(GetDate() As Float)) As DateTime) returns a datetime datatype with the time portion removed and could be used as so.

Select
*
Table1
Where
Cast(Floor(Cast(Column_DateTime As Float)) As DateTime) = '14-AUG-2008'

or

DECLARE  @p_date DATETIME
SET      @p_date = Cast('14 AUG 2008' as DateTime)

SELECT   *
FROM     table1
WHERE    Cast(Floor(Cast(column_datetime As Float)) As DateTime) = @p_date

Answer 8

I know this isn't exactly how you want to do this, but it could be a start:

SELECT *
FROM (SELECT *, DATEPART(yy, column_dateTime) as Year, 
      DATEPART(mm, column_dateTime) as Month, 
      DATEPART(dd, column_dateTime) as Day 
      FROM table1)
WHERE Year = '2008'
AND Month = '8'
AND Day = '14'

Answer 9

Something like this?

SELECT  *
FROM    table1
WHERE   convert(varchar, column_datetime, 111) = '2008/08/14'

Answer 10

@Guy: Are any of the above answers correct? If not, could you please clarify your question?

Answer 11

In SQL Server 2008, you could use the new DATE datatype

DECLARE @pDate DATE='2008-08-14'  

SELECT colA, colB
FROM table1
WHERE convert(date, colDateTime) = @pDate

@Guy. I think you will find that this solution scales just fine. Have a look at the query execution plan of your original query.

And for mine:

Answer 12

@GateKiller

This is going to sound arrogant I think my answers are correct and you can't accept your own answer!

What I wanted to do was create a "reminder posting" that would allow me to lookup this answer in the future. It was one of Jeff's original purposes for this site in his podcasts. (I also wanted to see if someone had a better answer than me!)

The answers I have received are technically correct but have the flaw that they are very CPU heavy in having to process and manipulate every record to complete the query (@Jorrit is the exception at the time of writing).

If you have millions of records, you don't want to convert the date field for every record into a string, split it into it's parts and then compare all the parts. It is a classic "Super high CPU" method. It also prevents the SQL Optimiser using any index on that field.

If you can avoid manipulating the "database field" side of the comparison (the LEFT HAND SIE of the expression) and make your predicate (RIGHT HAND SIDE of the expression) the same as the db field then SQL only has to covert the RHS once and then do a native comparison.

This is a simple reminder of the principle that will help you scale SQL Server (any DB server) off of your desktop development environment where EVERY query runs blisteringly fast as you have no user load and subsets of data, into a fully blown production environment with hundreds of concurrent users and large datasets.

Question: Given the explanation above, it is fair to down mod the questions that are clearly unscalable, even if they would technically return the same results? Can I down mod answers?

Answer 13

In SQl Server 2000+

declare @pdate datetime
set @pdate = convert(datetime,'2008-08-01',120)
declare @max datetime
--set @max = dateadd(second,86399,@pdate)
set @max = dateadd(millisecond,86399998,@pdate)

select Xs.X, Xs.Date
from Xs
where Xs.Date between @pdate and @max

Edited in response to @Guy down-mod. Note, you have to use 86399998 milliseconds and not 86399999 as SQL Server sees the latter as equal to 86400000 and returns results for the following day also

Answer 14

How to get the DATE portion of a DATETIME field in MS SQL Server:

One of the quickest and neatest ways to do this is using

DATEADD(dd, DATEDIFF( dd, 0, @DAY ), 0)

It avoids the CPU busting "convert the date into a string without the time and then converting it back again" logic.

It also does not expose the internal implementation that the "time portion is expressed as a fraction" of the date.

Get the date of the first day of the month

DATEADD(dd, DATEDIFF( dd, -1, GetDate() - DAY(GetDate()) ), 0)

Get the date rfom 1 year ago

DATEADD(m,-12,DATEADD(dd, DATEDIFF( dd, -1, GetDate() - DAY(GetDate()) ), 0))

Answer 15

@vzczc

I've upmodded your answer as your the only person to tried to prove the assertion instead of just posting an answer.

In fact, I've been working on a series of test plans that can both prove and disprove my answers! Just need a little more time to finish this off.

Basically, it comes down to what type of index (and you use a clustered index in your proof) and/or which columns you retrieve with the select statement.

It seems that, SQL Server 2005, if the query needs to refer back to the underlying table then it will force a full table scan of the table, regardless of the index or the cardinality of the results. i.e. even if the WHERE clause covers a millisecond of time that you know only matches 1 record in a table of millions, SQL Server will still FTS the millions of records.

I still need to test the above with a clustered index on the date column.

This is different from Oracle (at least 10g) where the optimiser seems to work better in these edge cases

The point about my answer is that, your giving the optimiser the best chance to minimise the work it needs to do. If you wrap the date field in CONVERTS or CASTS then the optimiser is forced to FTS event if a correctly configured index or clustered index is used.

If you can complete the proof for SQL 2005 or SQL 2008 then I'll happily award you the answer!

Answer 16

select convert(varchar(2),datepart("dd",doj))+'/'+convert(varchar(2),datepart("mm",doj))+'/'+convert(varchar(4), datepart("yy",doj)) from emp

Answer 17

DECLARE @Dat

SELECT * from Jai

where
convert(varchar(2),datepart("dd",Date)) +'/'+
convert(varchar(2),datepart("mm",Date)) +'/'+
convert(varchar(4), datepart("yy",Date)) = @Dat

Answer 18

Good point about the index in the answer you accepted.

Still, if you really search only on specific DATE or DATE ranges often, then the best solution I found is to add another persisted computed column to your table which would only contain the DATE, and add index on this column:

ALTER TABLE "table1" 
    ADD "column_date" AS CONVERT(DATE, "column_datetime") PERSISTED

Add index on that column:

CREATE NONCLUSTERED INDEX "table1_column_date_nu_nci"
ON  "table1" ( "column_date" ASC )
GO

Then your search will be even faster:

DECLARE  @p_date DATE
SET      @p_date = CONVERT( DATE, '14 AUG 2008', 106 )

SELECT   *
FROM     table1
WHERE    column_date = @p_date

Answer 19

In sqlserver

Declare @p_date DATE

Select * from tabel1 where column_dateTime=@p_date

In C# Pass the short string of date value using ToShortDateString() function. sample: DateVariable.ToShortDateString();