CODEWITHSUNDEEP
t-sqlcasting
sennett
sennett

Reputation: 8424

Why does this TSQL cast datetime -> varchar -> datetime?

I recently came across this:

SELECT
    'Y',
    ltrim(rtrim(upper(Newly_Eligible)))
FROM Table
WHERE 
    Id = 1
AND
    (Convert(datetime, Convert(varchar, GETDATE(),103),103)  
    BETWEEN
        Convert(datetime,Convert(varchar, [Start_Date],103),103)
    AND
        Convert(datetime, Convert(varchar, [End_Date] ,103),103))

Start_Date, End_Date and obviously GETDATE() are all datetime types. Why does he cast to a varchar and then back again?

Upvotes: 4

Views: 940

Answers (3)

t-clausen.dk
t-clausen.dk

Reputation: 44326

I really don't like that slow dateconversion into varchar and back.

I surgest 2 other ways of writing your code:

sql-server 2005:

SELECT
    'Y'
    ltrim(rtrim(upper(Newly_Eligible)))
FROM table
WHERE 
    Id = 1
AND
    dateadd(day, datediff(day, 0,  getdate()), 0)
    BETWEEN
        dateadd(day, datediff(day, 0,  [Start_Date], 0)
    AND
    [End_Date] -- *

*Since the compared getdate has has been stripped for the time part, it will never be larger than the end_date for the same day

sql-server 2008:

SELECT
    'Y',
    ltrim(rtrim(upper(Newly_Eligible)))
FROM table
WHERE 
    Id = 1
AND
    cast(GETDATE() as date)
BETWEEN
    cast([Start_Date] as date)
AND
    [End_Date]

Upvotes: 2

RBarryYoung
RBarryYoung

Reputation: 56735

This truncates any excess time-of-day value out of the datetime leaving just the date value. (The key is the conversion code "103".)

Upvotes: 4

TheRealTy
TheRealTy

Reputation: 2429

It is done to remove the time, so you can compare only dates in the the same format. There are a few ways of dong this and the above is one of them.

Another is 

select cast(floor(cast(getdate() as float)) as datetime)

Upvotes: 4

Related Questions