Selecting Dates 1

[TRACEYMARYLAND]

I have column that is a datetime
If there is no date the value is set to NULL

So if i pass in no parameters to the @from_date and
@to_date i need to show all records.

If i put in a value then only select those records

I have tried

select
ProdSched.PromiseDate >= @from_date and
ProdSched.PromiseDate <= @to_date

and tried this
and Convert(varchar(8),ProdSched.PromiseDate,112) >=
Convert(varchar(8),@from_date,112)
and Convert(varchar(8),ProdSched.PromiseDate,112) <=
Convert(varchar(8),@to_date,112)

But can't get all records if value is nothing NULL

Any suggestions

 

[AtomicWedgie]

I don't know if this is possible but maybe you could try something like this:

if @from_date is null then
set @from_date = select min(ProdSched.PromiseDate) from prodsched
end

if @to_date is null then
set @to_date = select max(ProdSched.PromiseDate) from prodsched
end

I don't know if the code is valid as it is.

Another way could be to create dynamic sql statements.

Regards,

Atomic Wedgie

 

[nigelrivett]

(ProdSched.PromiseDate >= @from_date or @from_date is null)and (ProdSched.PromiseDate <= @to_date or @to_date is null)


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[AtomicWedgie]

even better

 

[TRACEYMARYLAND]

How would i do the same command if the parameters default values are

@From_Date nvarchar(15),
@to_date nvarchar(15)

i need to set null being passed in rather than passing in a date...as null(not date)

Cheers

 

[ESquared]

Just put Convert(datetime,@From_Date) either in the query in every instance or assign that to another variable first and use that in the query.