How to convert sql server default date value to NULL

Question

How to convert sql server default date value to NULL

Niladri Saha

SSC Veteran

Points: 298
More actions
October 3, 2005 at 1:51 am

#89555

Hi,
I am facing a proble.When I enter space in date field in sql server ,it by default takes 1900-01-01 00:00:00.000.I want when I enter space in date field it will take NULL , not 1900-01-01 00:00:00.000.
Can anyone tell me how to get this??
Thanks & Regards
Niladri Saha

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

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

SQL Server recognises a space as being different from a null (because it is). Why don't you just configure your app to convert a space to a null and write that to SQL Server?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Niladri Saha SSC Veteran Points: 298 More actions · Answer 2

It is not related to any application.I am concer about this when I am entering any data from backend.In that case if I give space in datetime column it takes SQL Server default date.I want to change that.

Thanks

Niladri Saha

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

Using EM to input data, CTRL/0 will enter a NULL for you.

If you are really concerned, I guess you could write a trigger that will always convert space to NULL, but seems a bit unusual.

If you are entering new rows, why not just default the date to NULL?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

AMIT GUPTA-263376 SSCarpal Tunnel Points: 4244 More actions · Answer 4

USE THIS QUERY :

IN THIS QUERY

MYTAB_DATE IS A TABLE WHICH CONTAINS SQL DEFAULT DATE

UPDATE MYTB_DATE SET DT=NULL WHERE DT=CONVERT(DATETIME,' ')

Leah Kats SSC Veteran Points: 271 More actions · Answer 5

in the insert use case statement

insert my_table(col1)

select case when len(date1_char) = 0 THEN NULL ELSE cast(date1_char as smalldatetime)

Leah Kats

Farrell Keough SSCoach Points: 17414 More actions · Answer 6

NULLs are not generally a very good choice as a Default. You may want to consider accepting the 1900 date as NULL does not equal NULL and NULL has numerous meanings when interpretting the data, (i.e., did you not have a data at the time of entry and will get one later, will there never be a date, etc...).

Just my $0.02.

I wasn't born stupid - I had to study.