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