Conversion FUN
-
Just one of the little gems found in one of the DB's i'm maintaining
SELECT DISTINCT T1.Ordernumber FROM dbo.OrderEdi T1 WHERE CAST(CONVERT(VARCHAR, t1.ProcessingDate, 106) AS SMALLDATETIME) = CAST(CONVERT(VARCHAR, GETDATE(), 106) AS SMALLDATETIME) )
b.t.w. the t1.ProcessingDate is already a SMALLDATETIME field ...
-
This is not necessarily a WTF. The code could be used to compare only the date part of two dates, ignoring the time component, since there is no date-only datatype prior to SQL Server 2008.
-
@lilakuh said:
This is not necessarily a WTF. The code could be used to compare only the date part of two dates, ignoring the time component, since there is no date-only datatype prior to SQL Server 2008.
I stand corrected. You are absolutely right, I was jumping to conclusions here based on previous experiences with this code. Should have thought a little longer before posting.
-
Don't worry, that's still a pretty awful way to do it. I usually just do this:
[code]DATEADD(dy, DATEDIFF(dy, 0, @date), 0)[/code]
You can also cast it to an int and then back to a datetime, since datetime is actually stored as a float type internally.
-
@db2 said:
Don't worry, that's still a pretty awful way to do it. I usually just do this:
<font face="Lucida Console" size="2">DATEADD(dy, DATEDIFF(dy, 0, @date), 0)</font>
You can also cast it to an int and then back to a datetime, since datetime is actually stored as a float type internally.
Still ugly indeed but no longer a WTF.
-
@coentje said:
@db2 said:
Don't worry, that's still a pretty awful way to do it. I usually just do this:
<font size="2" face="Lucida Console">DATEADD(dy, DATEDIFF(dy, 0, @date), 0)</font>
You can also cast it to an int and then back to a datetime, since datetime is actually stored as a float type internally.
Still ugly indeed but no longer a WTF.
Of course, this is all assuming you aren't running 2008 and can't just do CAST(@date AS date).
-
@db2 said:
@coentje said:
@db2 said:
Don't worry, that's still a pretty awful way to do it. I usually just do this:
<font size="2" face="Lucida Console">DATEADD(dy, DATEDIFF(dy, 0, @date), 0)</font>
You can also cast it to an int and then back to a datetime, since datetime is actually stored as a float type internally.
Still ugly indeed but no longer a WTF.
Of course, this is all assuming you aren't running 2008 and can't just do CAST(@date AS date).
Nope, this customer still on 2005
-
Out of curiosity (and for anyone interested) I just did a quick performance check on a table containing 1,000,000 rows:
- CAST(CONVERT(VARCHAR, D.Date, 106) AS SMALLDATETIME: 1732ms
- DATEADD(dy, DATEDIFF(dy, 0, D.Date), 0: 171ms
- CAST(D.Date AS date): 110ms
So the CAST(D.Date AS date) is about 15x faster than the CONVERT(VARCHAR, D.Date, 106) method.
-
@db2 said:
You can also cast it to an int and then back to a datetime, since datetime is actually stored as a float type internally.
^- Relying on an implementation detail like this is a huge WTF.
-
@lilakuh said:
This is not necessarily a WTF. The code could be used to compare only the date part of two dates, ignoring the time component, since there is no date-only datatype prior to SQL Server 2008.
It is necessarily a WTF, because it means a full table scan while if they simply generated the range from previous to next midnight and usedbetween
the database would easily be able to use index.
-
In this particular case, yes. However I can see other situations (such as a join on a date column) where the BETWEEN is not really feasible.
-
Then you just create a persisted computed column for the date only, and index it. Presto!