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 used between 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!


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.