SQL server date fields - 1/1/1900

[sodakotahusker]

I am using direct sql statements to insert into and update a sql table. When I have a date value where nothing has been input I am sending in an empty string. What gets stored in the database is 1/1/1900. I have been tweaking stored procedures for years changing that to an empty string. There must be a more elegant solution than massaging values. What can I pass to SQL as a value to have it store a null? I assigned a vbnull and it stored as 1/2/1900.

 

[557]

insert the date field as null (without quotes)
eg:-
insert into test_dates values('aa',null)

 

[SqueakinSweep]

It would be advisable to store set a default value for the date fields, to save having to insert any values for NULL dates. This way the SQL insert takes care of itself.

Rather than tweaking every stored procedure, simply write a function to handle the string conversion, which you can use every time in every stored procedure. Something like this should do the trick

CREATE FUNCTION [dbo].[fxEmptyDate]
(@dDate AS DATETIME)
RETURNS VARCHAR(10)
AS  

BEGIN 

--Function to Return a String Date
--Returns Empty String if Date is 01/01/1900

DECLARE @lcDate CHAR(10)
SET @lcDate=space(10)
IF YEAR(@dDate)=YEAR(0) AND MONTH(@dDate)=MONTH(0) AND DAY(@dDate)=DAY(0) RETURN @lcDate

SET @lcDate = CONVERT(CHAR,@dDate,103)
RETURN @lcDate

END

Sweep
...if it works dont f*** with it
...if its f****ed blame someone else
...if its your fault that its f***ed, say and admit nothing.

 

[sodakotahusker]

Ok. Got the problem under control. Thanks guys!!