How do i convert a date to mm/dd/yyyy in my where clause

Question

I have thousands of records that were added today but at different hours of the day, The only way I'm able to see them is if I do ORDER BY ADDdate DESC

However, i need to make an update statement where I want to update a particulr field using WHERE clause with the date

The problem is, since they were all entered at different times, how am i able to CAST/CONVERT this field so that I can only do something like....

Basically every added record has a AddDate field in datetime format, i want it converted to mm/dd/yyyy format.

Update SpecialField
SET SpecialField = NULL
Where ADDdate = '09/18/2017'

Please help. Thanks.

Possible duplicate of [sql server convert date to string MM/DD/YYYY](https://stackoverflow.com/questions/11854775/sql-server-convert-date-to-string-mm-dd-yyyy) — Tab Alleman, Sep 18 '17 at 17:49

score 2 · Answer 1 · answered Sep 18 '17 at 17:50

2

If you are trying to update all records where ADDdate is on 9/18/2017 (regardless of time), you could do something like this:

UPDATE SpecialField, SpecialField2
SET SpecialField = NULL
WHERE ADDdate > '09/18/2017' AND ADDdate < '09/19/2017'

answered Sep 18 '17 at 17:50

Jeff Gardner

301
1
2
7

score 1 · Accepted Answer · answered Sep 18 '17 at 18:01

You should always use standard date formats. I prefer YYYY-MM-DD, although SQL Server has a slight preference for YYYYMMDD.

Here are two ways:

Update SpecialField
    set SpecialField = NULL
    where ADDdate >= '2017-09-18' and ADDdate < '2017-09-19';

The the >= for the first value and < for the second.

or

Update SpecialField
    set SpecialField = NULL
    where cast(ADDdate as date) = '2017-09-18';

In both cases, SQL Server will use an index, so they are both reasonable ways of expressing the logic.

If you specifically want today's date, then use getdate(), such as:

Update SpecialField
    set SpecialField = NULL
    where cast(ADDdate as date) = cast(getdate() as date);

score 1 · Answer 3 · answered Sep 18 '17 at 18:06

If it's a frequent requirement, you could simply not worry about typing dates.

where addDate >= cast(getdate() as date)
and addDate < dateadd(day, -1, cast(getdate() as date))

Edit

This has some similarities to Gordon's answer in that it uses cast(getdate as date). The difference is that he filters on a function result which tends to slow down performance.

How do i convert a date to mm/dd/yyyy in my where clause

3 Answers3