October 20, 2008 at 4:12 am
Hi,
I want to insert blank value in my table for datatime column.
I am getting 1900-1-1 for blank value?
So please tell me how it will work?
Thanks.
October 20, 2008 at 4:17 am
Do you want a NULL value, or a default value, when no data is specified?
If you allow NULL values, check/alter the table definition
For default value, add a default constraint to this column.
Wilfred
The best things in life are the simple things
October 20, 2008 at 4:24 am
I want my result like that :
insert into t1 (date) values ('')
it should show with no value or null
date
October 20, 2008 at 4:42 am
Some tests is did:
create table wdi(datum datetime null)
insert into wdi(datum) values ('')
insert into wdi(datum) values (NULL)
select * from wdi
drop table wdi
output: 1900-01-01 00:00:00.000
NULL
create table wdi(datum datetime not null)
insert into wdi(datum) values ('')
select * from wdi
drop table wdi
output: 1900-01-01 00:00:00.000
create table wdi(datum datetime not null default '1967-06-30')
insert into wdi(datum) values ('')
insert into wdi(datum) values (DEFAULT)
select * from wdi
drop table wdi
output: 1900-01-01 00:00:00.000
1967-06-30 00:00:00.000
So if you insert an empty value (NULL) into your datefields, you'll have to specify NULL
If you add a blank value, you'll get the SQL default 1900-01-01
If you want your own default, add a constraint and use the word DEFAULT
Wilfred
The best things in life are the simple things
October 24, 2008 at 9:04 am
Wilfred,
In addition to what everyone else has mentioned, you can also set up a case statement to populate the null or blank fields after they have been inserted.
SELECT
CASE
WHEN dateum IS NULL OR dateum = ' ' THEN '1990-01-01 00:00:00.000'
ELSE dateum
END AS dateum
-Chris
October 24, 2008 at 9:30 am
Just some inside info on what's happening here. When you attempt to insert a '' into a datetime column, SQL Server must CAST the blank value to datetime. CASTing a varchar blank results in a value of zero. The date value you are seeing is the result of casting a zero to datetime.
DECLARE @datetime datetime
SET @datetime = 0
SELECT @datetime
SET @datetime = ''
SELECT @datetime
October 24, 2008 at 10:01 am
I also found that implicit CAST. You can define a default value of (0) for a datetime column and it enters the 'zero' datetime. You can insert a 0 using T-SQL. But you can't enter a 0 directly into a datetime column, as I guess a CAST can't intervene between your data and the column.
October 24, 2008 at 10:08 am
Im sorry for my halfass post earlier. Here is the correction version. substitute values of course
SELECT
CASE
WHEN CONVERT(varchar(10), B.Date, 121) IS NULL THEN '1900-01-01'
ELSE CONVERT(varchar(10), B.Date, 121)
END AS Date
FROM Table B
-Chris
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply