May 22, 2008 at 11:27 pm
While inserting null value (passed from front end) in a table (through SP), it is inserting 1/1/1900 instead of Null value. How to avoid inserting 1/1/1900?
Nagesh
May 23, 2008 at 12:03 am
[font="Verdana"]Post your table structure. It seems, you nothing (Null) is passed to the date column, insert Default value. Eigther you need to alter table structure or let it as it is. By the way, is it creating any problem?
Mahesh[/font]
MH-09-AM-8694
May 23, 2008 at 12:28 am
Hi,
In table structure, If the column is NOT NULL, then it will take default Date.
If it is ALLOW NULL, then try the following:
use pubs;
GO
if exists(select name
from sysobjects
where name = 'ChangeDate' and type = 'p')
drop procedure ChangeDate;
GO
create procedure ChangeDate
@NewDate datetime
as
begin try
if( @NewDate = '')
begin
set @NewDate = NULL
end
update employee set hire_date = @NewDate where fname = 'Paolo'
select hire_date from employee where fname = 'Paolo'
end try
begin catch
select ERROR_NUMBER() as ErrorNumber,
ERROR_MESSAGE() as ErrorMessage
end catch
exec ChangeDate ''
OUTPUT:
hire_date
NULL
May 23, 2008 at 1:12 am
Hi
Is there any chance that the front-end supplies a default date - instead of the NULL?
Thanks
Kevin
May 23, 2008 at 1:18 am
Hi,
If u r passing argument through the textbox, then change do the following.
if (textBox1.Text == "" || textBox1.Text == null)
{
textBox1 .Text = "03/23/2008";
}
You can use like this, Right?
Is this useful for you?
May 23, 2008 at 1:35 am
hi, thanks for reply
My table is like
CREATE TABLE [dbo].[custom_table](
[id] [int] IDENTITY(1,1) NOT NULL,
[int] NULL,
[description] [varchar](3000) NULL,
[Due_date] [datetime] NULL,
[completed_date] [datetime] NULL,
[isactiveflag] [int] NULL)
and when we insert null values from sql query editor it is inserting null values. But, when insert procedure called from application and the null value (DBNull) passed for the date fields it is inserting 1/1/1900 (the insert query is a dynamic query).
when again view that record on application it is showing the 1/1/1900 in the date fields in the application. For this we have to check for "1/1/1900".
When I try to update the table again with null values where that date field is equal to 1/1/1900, it is updating with null value.
May 23, 2008 at 1:40 am
[font="Verdana"]
...and when we insert null values from sql query editor it is inserting null values. But, when insert procedure called from application and the null value (DBNull) passed for the date fields it is inserting 1/1/1900 (the insert query is a dynamic query). ...
If so, then you have to check out from where the date value is being passed to the date parameter, and have to change it.
Mahesh[/font]
MH-09-AM-8694
May 23, 2008 at 2:05 am
Yes maybe the stored proc is declaring a date variable and it assigns a default value for you?
May 23, 2008 at 2:13 am
I tried like this
-------
CREATE TABLE [dbo].[custom_table](
[id] [int] IDENTITY(1,1) NOT NULL,
[int] NULL,
[description] [varchar](3000) NULL,
[Due_date] [datetime] NULL,
[completed_date] [datetime] NULL,
[isactiveflag] [int] NULL)
declare @insertsql varchar(2000), @dt1 datetime,@dt2 datetime, @desc varchar(100),@dt3 datetime
set @desc = '123455descript'
set @dt1 = ''
set @dt2 = null
set @dt3 = getdate()
set @insertsql = 'insert into customer_table select 455,'+@desc+','+
cast(isnull(@dt1,'') as varchar)+','+cast(isnull(@dt2,'') as varchar)+','+cast(1 as varchar)
print @insertsql
set @insertsql = 'insert into customer_table select 455,'+@desc+','+
cast(isnull(@dt1,'') as varchar)+','+cast(isnull(@dt3,'') as varchar)+','+cast(1 as varchar)
print @insertsql
RESULT :
insert into customer_table select 455,123455descript,Jan 1 1900 12:00AM,Jan 1 1900 12:00AM,1
insert into customer_table select 455,123455descript,Jan 1 1900 12:00AM,May 23 2008 1:39PM,1
May 23, 2008 at 2:20 am
[font="Verdana"]
set @insertsql = 'insert into customer_table select 455,'+@desc+','+
cast(isnull(@dt1,'') as varchar)+ ','+cast(isnull(@dt2,'') as varchar)+','+cast(1 as varchar)
why are you converting date variables to varchar as you are supposed to insert date?
Mahesh[/font]
MH-09-AM-8694
May 23, 2008 at 2:24 am
declare @Date datetime
select @Date
select convert(varchar, @Date)
select cast(isnull(@Date,'') as varchar)
NULL
NULL
Jan 1 1900 12:00AM
The above query illustrates what is happening - the cast - to a varchar and '' blank - sets the date to the Jan 1 1900 12:00AM - value
Thanks
Kevin
May 23, 2008 at 2:45 am
[highlight=#ffff11]why are you converting date variables to varchar as you are supposed to insert date?
Mahesh [/highlight]
If I don't convert date variables to varchar in that dynamic query it will give error (I am assigning total query to a variable @insertsql which is varchar)
Msg 241, Level 16, State 1, Line 11
Conversion failed when converting datetime from character string.
May 23, 2008 at 3:00 am
[font="Verdana"]
If I don't convert date variables to varchar in that dynamic query it will give error (I am assigning total query to a variable @insertsql which is varchar)
Think, if you will pass the date value instead of null, what will happen? Your query will still convert the date to varchar, and how it will insert such character value into date column? I think, prev developer have encountered the same problem, to resolv this he had made such changes so that in case of NULL, it will insert some constant date value.
Mahesh[/font]
MH-09-AM-8694
May 23, 2008 at 3:06 am
Had a similar situation recently, where source usually sent nulls, and I cast the date as integer (YYYYMMDD).
Suddenly they started sending blanks (''), and it defaulted to the date that you supplied.
I got around this by using a case statement
Case when fieldname = '' then Null else fieldname end
Let me know if this helps
~PD
May 23, 2008 at 3:32 am
yes, the only 2 values that you want to insert is:
* NULL - if no date supplied
* the date supplied
The above case statement would cater for the scenario that is being experienced
Thanks
Kevin
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply