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

Question

question

cdurham asked Apr 30, '12

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

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

datetime dateformat

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2012-04-30T14:34:50Z

Kev Riley answered Apr 30, '12

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).

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

cdurham commented · Apr 30, 2012 at 02:57 PM

@Kev Riley I used the cast phrase in the where statement and got exactly what I needed. Thank you once again!!

0 ·

Answer 2 · 2012-04-30T14:22:15Z

JohnM answered Apr 30, '12

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.

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

cdurham commented · Apr 30, 2012 at 02:31 PM

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!

0 ·

question