Checking for a null date

Question

Checking for a null date

bill.humphrey

SSC Enthusiast

Points: 173
More actions
April 7, 2008 at 4:03 am

#95847

Hi
in a stored procedure i want to check for a null date and assign a default value if it is null:
alter procedure sp_list_orcon_cat_b_v2
@start_date nvarchar(19),
@end_date nvarchar(19),
@priority nvarchar(1),
@response integer
if isnull(end_date) then
set @end_date = @start_date
end if

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

SQLZ SSChampion Points: 12872 More actions · Answer 1

bill.humphrey (4/7/2008)
Hi
in a stored procedure i want to check for a null date and assign a default value if it is null:
alter procedure sp_list_orcon_cat_b_v2
@start_date nvarchar(19),
@end_date nvarchar(19),
@priority nvarchar(1),
@response integer
if isnull(end_date) then
set @end_date = @start_date
end if

Try this instead:

if @end_date is null set @end_date = @start_date

Karl
source control for SQL Server

bill.humphrey SSC Enthusiast Points: 173 More actions · Answer 2

I get an error "incorrect syntax near keyword 'if'.

alter procedure sp_list_orcon_cat_b_v2

@start_date nvarchar(19),

@end_date nvarchar(19),

@priority nvarchar(1),

@response integer

if @end_date is null set @end_date = @start_date

as

begin

SELECT TOP 100 PERCENT id, report_date, priority_text,

MIN(DATEDIFF(mi, dt_switch, dt_arrive)) AS resp,

COUNT(id) AS no_of_resp,

CASE WHEN MIN(datediff(mi,

dt_switch, dt_arrive)) <= 19 THEN 1 ELSE 0 END AS InStd

--INTO #a_temptable_catb

FROM dbo.cadcasedetails

WHERE (NOT (dt_arrive IS NULL))

AND (report_date >= CONVERT(DATETIME, @start_date, 103))

AND (report_date < CONVERT(DATETIME, @end_date, 103))

--AND (report_date < CONVERT(DATETIME, @start_date, 103))

AND (priority_text = 'B')

GROUP BY id, report_date, priority_text

--SELECT COUNT(InStd) AS total_resp, SUM(InStd) AS in_std_resp, ROUND(CAST(SUM(InStd) AS float) / COUNT(InStd) * 100, 2) AS Expr3

--FROM #a_temptable_catb

--DROP TABLE #a_temptable_catb

END

GO

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 3

[font="Verdana"]

bill.humphrey (4/7/2008)
I get an error "incorrect syntax near keyword 'if'.
alter procedure sp_list_orcon_cat_b_v2
@start_date nvarchar(19),
@end_date nvarchar(19),
@priority nvarchar(1),
@response integer
AS
if (@end_date is null) then
begin
set @end_date = @start_date
end
begin
SELECT TOP 100 PERCENT id, report_date, priority_text,
MIN(DATEDIFF(mi, dt_switch, dt_arrive)) AS resp,
COUNT(id) AS no_of_resp,
CASE WHEN MIN(datediff(mi,
dt_switch, dt_arrive)) <= 19 THEN 1 ELSE 0 END AS InStd
...

Check it now after applying the changes highlighted in bold. Refer the Create Procedure from BOL as well.

Thanks,

Mahesh

[/font]

MH-09-AM-8694

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

Bill

Try this - doesn't need any IFs and does it in one line:

SET @end_date = COALESCE(@end_date, @start_date)

John

bill.humphrey SSC Enthusiast Points: 173 More actions · Answer 5

ah I shifted the if statement inside the begin clause and it accepts it

Kathy-82899 Right there with Babe Points: 762 More actions · Answer 6

Replace your if statement with the following:

Set @end_date = ISNULL(@end_date, @start_date).

The ISNULL() function replaces a null value with the specified replacement value. It is not used to check for a null value as you used it in your if statement.

You can also use COALESCE(@end_date, @start_date). This function returns the first non-null value.

KDW