October 3, 2005 at 1:51 am
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
October 3, 2005 at 4:20 am
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?
October 3, 2005 at 4:47 am
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
October 3, 2005 at 5:42 am
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?
October 3, 2005 at 6:25 am
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,' ')
October 7, 2005 at 2:06 pm
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
October 7, 2005 at 2:35 pm
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.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy