asked on

'01/01/1900' null value in sql

Hello,

How to write a sql which will save 'Null' instead of '01/01/1900' in date column.

UPDATE [dbo].[SHORT_LET]
SET
       [FlatName] = CAST(@FlatName AS Nvarchar(50))
,[Address]=CAST(@Address AS NVARCHAR(50))
,[From]=CAST(@From AS nvarchar(20))
,[To]=CAST(@To AS nvarchar(20))
,[GuestName]=CAST(@Guestname AS nvarchar(20))
,[Auth]=CAST(@Auth AS Nvarchar(MAX))
,[Agency] =       CAST(@Agency AS Nvarchar(50))
,[PeriodPaid]=      CAST(@PeriodPaid AS Nvarchar(50))
,[DepositePaid]=      CAST(@DepositePaid AS Nvarchar(50))
,[TotalPaid]=      CAST(@TotalPaid AS Nvarchar(MAX))
,[DateDeparted]=      CAST(@DateDeparted AS Nvarchar(50))
,[Comments]=      CAST(@Comments AS Nvarchar(50))
,[DepositReturned]=      CAST(@DepositReturned AS Nvarchar(MAX))
,[Bedrooms]=      CAST(@Bedrooms AS Nvarchar(50))
,[Departure]=CAST(@Departure AS Nvarchar(20))
,[OccupantName]=CAST(@OccupantName AS Nvarchar(MAX))
,[RentalPerWeek]=CAST(@RentalPerWeek AS Nvarchar(50))
,[Deposite]=      CAST(@Deposite AS Nvarchar(10))
,[WeeksRented]=      CAST(@WeeksRented AS Nvarchar(50))
,[WeeklyRate]=CAST(@WeeklyRate AS Nvarchar(50))
,[DepositeWeeks]=CAST(@DepositeWeeks AS Nvarchar(50))
,[ExtensionFrom]=CAST(@ExtensionFrom AS Nvarchar(20))
,[ExtensionTo]=CAST(@ExtensionTo AS Nvarchar(20))
,[ExtensionPaid]=      CAST(@ExtensionPaid AS Nvarchar(50))
,[DepositeReturnDate]=      CAST(@DepositeReturnDate AS Nvarchar(20))

       WHERE [SID] =@ShoD
            Return @@Rowcount

,[From]=CAST(@From AS nvarchar(20))-----date column
,[To]=CAST(@To AS nvarchar(20))----date column

Kent Olsen

I suspect that @From and @To are integer columns, if so change these two lines:

,[From]=CAST(@From AS nvarchar(20))
,[To]=CAST(@To AS nvarchar(20))

to

,[From]=case when @From=0 then NULL else CAST(@From AS nvarchar(20)) end
,[To]=case when @To=0 then NULL else CAST(@To AS nvarchar(20)) end

Good Luck!
Kent

RIAS

ASKER

@From and @To are date columns

ASKER CERTIFIED SOLUTION

Kent Olsen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RIAS

ASKER

Thanks! will try and get back

RIAS

ASKER

Kdo,
Nope still the same

1900-01-01

Kent Olsen

Methinks the client is "helping" us out here. The first date that was an issue was '01/01/1900'. What you're seeing now is '1900-01-01'. They may be the same date to you and me, but that's not necessarily the case in SQL.

The quickest thing is to modify the date in those two rows to test for '1900-01-01'.

How many rows are you planning to update? Can you select a sample of the source rows?

As I understand this, both the source and destination columns are of type date?

Kent

Kent Olsen

Try using '1900-01-01'. My local server's default is to display the date in yyyy-mm-dd format. If yours is set the same, that should work.

RIAS

ASKER

Vitor Montalvão

My guess is that you have a default value constraint for the date column so when you try to set it to NULL it will be replaced by '1900-01-01'. If that's the case you'll need to disable the constraint to achieve what you want.

RIAS

ASKER

How to disable the constraint?

Koen Van Wielink

Where do the values for @From and @To come from? Are they stored procedure parameters? How are they defined? You may have to set a default value of NULL in the variable declaration.

RIAS

ASKER

This worked!!!!!!

,[DateDeparted]=case when @DateDeparted='' then null else CAST(@DateDeparted AS nvarchar(20)) end

Cheers!!

RIAS

ASKER

Kdo thanks a ton!

Kent Olsen

So the date was NULL in the source data and being converted to the epoch date when NULL was recast.

Interesting.... :)