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