replace null or empty values with default date

Question

replace null or empty values with default date

asranantha

SSCarpal Tunnel

Points: 4246
More actions
December 10, 2013 at 6:34 am

#280084

Hi Friends i have small doubt in sql server how to handled null or empty value and replace through default date
table having data like
table:emp
id , name , doj
1 , abc , 3/1/2011
2 , ramu , null
3 , venu , 3/12/2011
4 , vjde , 3/20/2011
5 , jai ,
6 , vanj , 3/20/2011
7 , hari , 11/10/2010
based on above data i want output like below
id , name , doj
1 , abc , 03/1/2011
2 , ramu , 01/01/1900
3 , venu , 3/12/2011
4 , vjde , 3/20/2011
5 , jai , 01/01/1900
6 , vanj , 3/20/2011
7 , hari , 11/10/2010
and i tried below query
select id,name, case coalesce(convert(varchar, doj,101),'')='' then '' else convert(varchar,doj_,101) end from emp
but its showing errore like Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '='.
pleas tell me how to solve this issuse in sql server .

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

first, a reminder: ALWAYS STORE DATES AS DATES.

from your sample data, it looks like you are storing as varchars, and converting varchars to dates and then dates to varchars introduces several problems:

you cannot use an index, so performance sucks, since every row in the table needs to be evaluated.

this is an issue that could haunt you much later when the table gets large, and is a bad practice that might propigate into your future coding.

as for fixing the data (for now)

it's an update stat4emetn:

UPDATE emp

SET doj = '01/01/1900'

WHERE doj IS NULL

OR LTRIM(RTRIM(doh)) = ''

the right thing to do is:

change the datatype to DATE or DATETIME

add a default constraint for when the doj is not provided.

maybe add a trigger for when data is inserted with an explicit default value.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

asranantha SSCarpal Tunnel Points: 4246 More actions · Answer 2

Hi i think here we donot need update statement we need add prefix zero for each mm/dd/yyyy format

doj (data type is varchar(30))having data like change

3/1/2011 to 03/01/2011

null to 01/01/1900

3/12/2011 to 03/12/2011

3/20/2011 to 03/20/2011

to 01/01/1900

3/2/2011 to 03/02/2011

11/10/2010 to 11/10/2011

please can you tell me how to get like out to write query .

Lowell SSC Guru Points: 323518 More actions · Answer 3

based on your response, i guess you cannot consider fixing the datatype.

here's an example based on your sample data;

the column BackToFormattedDate is what you are asking for

;WITH MyCTE([id],[name],[doj])

AS

(

SELECT '1','abc','3/1/2011' UNION ALL

SELECT '2','ramu',NULL UNION ALL

SELECT '3','venu','3/12/2011' UNION ALL

SELECT '4','vjde','3/20/2011' UNION ALL

SELECT '5','jai','' UNION ALL

SELECT '6','vanj','3/20/2011 ' UNION ALL

SELECT '7','hari','11/10/2010'

)

SELECT * ,

CONVERT(datetime,doj) AsToDateTime,

ISNULL(CONVERT(VARCHAR,CONVERT(datetime,doj),101),'01/01/1900') As BackToFormattedDate

FROM MyCTE;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

asranantha SSCarpal Tunnel Points: 4246 More actions · Answer 4

its not given exacetely results and doj format data 0 not added .

2/23/211 change to 02/23/2011 like that remains data and same way null or empty values handled with defautlt date.please tell me query how to solve.

Lowell SSC Guru Points: 323518 More actions · Answer 5

asranantha (12/10/2013)
its not given exacetely results and doj format data 0 not added .
2/23/211 change to 02/23/2011 like that remains data and same way null or empty values handled with defautlt date.please tell me query how to solve.

im not sure what you are asking, there may be a translation issue here. I did edit the TSQL, once, you might have "old code".

you said you did not want to use an UPDATE statement; my code generates a calculation aliased as [BackToFormattedDate]

if you want teh actual DOJ column to have that value, it must be updated.

could you please be explicit about what you don't think is working?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

asranantha SSCarpal Tunnel Points: 4246 More actions · Answer 6

asranantha

SSCarpal Tunnel

Points: 4246

December 10, 2013 at 9:04 pm

#1672487

Thanks.its working.