How do you cast/convert/ or change parts of a datetime field without changing it to a varchar?

Question

Hello all,

The previous DBA at the job that I am currently working for told me that it is better to use an = when selecting a date instead of using the like phrase. He said that this will make queries run faster. He looked at the code that I have written and told me to use = wherever i had selected a particular date. The equal sign works for tables that have hh:mm:ss as 00:00:00. My problem is that this does not work for dates that have actual hours, minutes and seconds. I need to know how to convert or cast (not to varchar) hh:mm:ss to 00:00:00. For example, I need to know how to write a code that will convert this:

2011-06-08 16:52:59.000

into this:

2011-06-08 00:00:00:000

any help would be greatly appreciated

Answer 1

You also need to consider how the data is stored in the column.

If you are storing dates with the time portion, then applying a function to the column will prevent SQL from using an index in the execution, slowing down your queries. So for example (using @JohnM 's good suggestion)

where cast(DateColumn as DATE) = @somedate

To get round this you can either......

Start to store the date as a DATE, rather than with the time portion - but only you will know whether this is viable for your situation. There may be other queries that rely on the time, the data might 'need' the time for other reasons.

Alternatively you could 'bound' the date being searched

where (DateColumn >= @somedate 
     and
       DateColumn < @somedate+1)

which would make use of an index on the DateColumn.

There are lots of ways around this issue, but it is important to leave the column side of the where clause untouched by a function - this is known as SARGability (Search ARGument).

Answer 2

0

What version of SQL Server are you running? Also, do you require the actual '00:00:00:000' or if it was converted to just '2011-06-08' would that be acceptable?

If you are on SQL 2008, you can do the following:

SELECT CAST('2011-06-08 16:52:59.000' AS DATE)

It will drop off the time portion of the value.

answered Apr 30 '12 at 02:22 PM

JohnM
4.5k ● 1 ● 3 ● 7

John,

I am using SQL Server 2008 R2. I would accept the 06-08-2011, just as long as when the code runs the actual date shows up. The working code that I have, for example looks like this (I have shortened it a bit, but I will just use the datetime field):

SELECT DateCreatedatMidnight
FROM iQclerk_SaleInvoices
WHERE DateCreatedAtMidnight = '02/27/2012'

This will give me: 2012-02-27 00:00:00.000

I will give your suggestion a try. Thanks!

Apr 30 '12 at 02:31 PM cdurham

(comments are locked)

How do you cast/convert/ or change parts of a datetime field without changing it to a varchar?

Follow this question

Related Questions