Display date as an empty string rather than 01/01/1900

[nimishauk]

Hi

In the database if a date field is set to null, the vlue returned is still 01/01/1900. I would like to be displayed as an empty string. The field is set to allow nulls and there is not trigger which sets the date.

Very frustrating. Hoping to hera some good news.

Regards
Nimisha

 

[meydz]

maybe there is a default value in your table. please check.

meydz

 

[nimishauk]

No default value is set.
Ta

 

[SQLBill]

It's a feature of SQL Server. You CAN NOT enter a NULL for date or time. The default date is 1/1/1900 and the default time is 00:00:00.000.

Refer to the Books OnLine, use the Index tab and enter datetime data type for more information.

If you only want to display the time, you can use this:

select right(convert(varchar,getdate(),120),8)

You can replace the getdate() with your column of datetime.
The 120 is the format to convert the datetime into. Again refer to the BOL, enter CONVERT.

This is what the above query is doing:

convert(varchar,getdate(),120
returns this as a varchar
2003-07-03 08:00:00

Then it does the outer part of the query:
right(convert(varchar,getdate(),120),8)

and returns:
08:00:00

-SQLBill

 

[SonOfEmidec1100]

I was intrigued by this so I did a quick test (obviously nothing better to do) heres the results.

UPDATE table1 set mydate = null WHERE RecID = 1

SELECT mydate as mydate FROM Table1

Returns NULL (recid 1)
2003-03-20 00:00:00.000 (recid 2)


SELECT isnull(mydate,'') FROM Table1

Returns 1900-01-01 00:00:00.000
2003-03-20 00:00:00.000

SELECT case when mydate is null then convert (varchar(10),'') ELSE left(convert(varchar,mydate,120),10) end AS Mydate FROM Table1

Returns ''
2003-03-20 (as strings)

 

[nimishauk]

HI SonOfEmidec1100

I guess what your are saying is what I'm looking for but how to I incorporate into my statement below. Thanks again guys, really appreciate the input.

SELECT 'INSERT INTO User(Surname, DOB) values
( ''' + Name + ''', ''' + REPLACE(CONVERT(CHAR(11), Date_Of_Birth, 106), ' ', '-') + ''');'
FROM User1

What this returns is an Insert statement

INSERT INTO User(Surname, DOB,) values ('WHITE','20-Jul-1955');

Here if the DOB is null , I want it returned null rather than 01/01/1900.

Nimisha