Comparing date with NULL values

[Mano]

Hi , I need to compare two date fields in two different tables.One of the field is varchar(8) and other is dateime.When there is a date in one field and NULL in other field , how do I compare these two vales?

[ItsMeAgain]

How about starting by casting your varchar 'date' column as datetime?

------------
Mano at 5/8/01 1:13:56 PM

Hi , I need to compare two date fields in two different tables.One of the field is varchar(8) and other is dateime.When there is a date in one field and NULL in other field , how do I compare these two vales?

I tried using Convert(char(8),@var,1)

------------
ItsMeAgain at 5/8/01 1:17:33 PM

How about starting by casting your varchar 'date' column as datetime?

------------
Mano at 5/8/01 1:13:56 PM

Hi , I need to compare two date fields in two different tables.One of the field is varchar(8) and other is dateime.When there is a date in one field and NULL in other field , how do I compare these two vales?

[ItsMeAgain]

declare @DateChar as varchar(8)
declare @DateDate as datetime
set @DateDate = '05/08/01'
set @DateChar = Null
if @DateChar is Null
begin
set @DateChar = '01/01/50'
end
if @DateDate is Null
begin
set @DateChar = '01/01/50'
end
if datediff(day, @DateDate, cast(@DateChar as datetime)) < 0
begin
print &#39;DateDate is bigger&#39;
end
if datediff(day, @DateDate, cast(@DateChar as datetime)) > 0
begin
print &#39;DateChar is bigger&#39;
end
if datediff(day, @DateDate, cast(@DateChar as datetime)) = 0
begin
print &#39;They are the same&#39;
end



------------
at 5/8/01 1:50:23 PM


I tried using Convert(char(8),@var,1)

------------
ItsMeAgain at 5/8/01 1:17:33 PM

How about starting by casting your varchar &#39;date&#39; column as datetime?

------------
Mano at 5/8/01 1:13:56 PM

Hi , I need to compare two date fields in two different tables.One of the field is varchar(8) and other is dateime.When there is a date in one field and NULL in other field , how do I compare these two vales?

Couldn&#39;t you use ISNULL for the @DateDate & @DateChar if all that&#39;s needed is a comparison of equality.


------------
ItsMeAgain at 5/8/01 2:07:57 PM

declare @DateChar as varchar(8)
declare @DateDate as datetime
set @DateDate = &#39;05/08/01&#39;
set @DateChar = Null
if @DateChar is Null
begin
set @DateChar = &#39;01/01/50&#39;
end
if @DateDate is Null
begin
set @DateChar = &#39;01/01/50&#39;
end
if datediff(day, @DateDate, cast(@DateChar as datetime)) < 0
begin
print &#39;DateDate is bigger&#39;
end
if datediff(day, @DateDate, cast(@DateChar as datetime)) > 0
begin
print &#39;DateChar is bigger&#39;
end
if datediff(day, @DateDate, cast(@DateChar as datetime)) = 0
begin
print &#39;They are the same&#39;
end



------------
at 5/8/01 1:50:23 PM


I tried using Convert(char(8),@var,1)

------------
ItsMeAgain at 5/8/01 1:17:33 PM

How about starting by casting your varchar &#39;date&#39; column as datetime?

------------
Mano at 5/8/01 1:13:56 PM

Hi , I need to compare two date fields in two different tables.One of the field is varchar(8) and other is dateime.When there is a date in one field and NULL in other field , how do I compare these two vales?