CODEWITHSUNDEEP
sql-servert-sql
DenStudent
DenStudent

Reputation: 928

SQL Server - Replace datatime field with another date

I am trying to replace a particular datetime value with another one. I tried it with: 

SELECT REPLACE(t.date,'1999-01-01 00:00:00.000','1900-01-01 00:00:00.000')
FROM table t

But that results into 

Jan  1 1999 12:00AM

What am I doing wrong here?

Upvotes: 1

Views: 3824

Answers (1)

SqlZim
SqlZim

Reputation: 38023

You can't use replace() on a datetime datatype, but you can use a combination of isnull() and nullif(), or a case expression.

using isnull(nullif()):

select isnull(nullif(t.date,'1999-01-01 00:00:00.000'),'1900-01-01 00:00:00.000')
from table t

using a case expression:

select case when t.date = '1999-01-01 00:00:00.000'
            then '1900-01-01 00:00:00.000'
            else t.date
            end
from table t

rextester demo: http://rextester.com/QMI12865

Upvotes: 2

Related Questions