x

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

more ▼

asked Apr 30 '12 at 02:09 PM in Default

cdurham gravatar image

cdurham
230 21 22 24

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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

more ▼

answered Apr 30 '12 at 02:34 PM

Kev Riley gravatar image

Kev Riley ♦♦
50.7k 43 49 76

@Kev Riley

I used the cast phrase in the where statement and got exactly what I needed. Thank you once again!!
Apr 30 '12 at 02:57 PM cdurham
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Apr 30 '12 at 02:22 PM

JohnM gravatar image

JohnM
5.9k 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)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x91
x7

asked: Apr 30 '12 at 02:09 PM

Seen: 873 times

Last Updated: May 01 '12 at 07:09 AM