April 2, 2008 at 4:22 am
Hi
I am trying to test a stored procedure in the query analyzer using a date parameter, but the test script date is not recognised. In the procedure I convert the datime field to dd/mm/yyyy and that works fine but when querying the date field it does not find the record with the test date?:
Test Script:
declare @start_date as datetime
set @start_date = convert(datetime, '24/07/2007', 103)
exec sp_list_orcon_cat_a @start_date
Procedure:
alter procedure sp_list_orcon_cat_a
@start_date datetime
as
declare @lErrorNo integer
declare @lRowsAffected integer
begin
select convert(varchar(10), report_date, 103) AS rep_date,
priority_text,
dt_switch,
dt_arrive,
datediff(minute, dt_switch, dt_arrive) as timediff
fromdbo.cadcasedetails
where (dbo.cadcasedetails.dt_arrive is not null)
and (priority_text = 'A')
and (dbo.cadcasedetails.report_date = @start_date)
order by timediff asc
end
GO
April 2, 2008 at 5:17 am
[font="Verdana"]
Try this ...
Procedure:
alter procedure sp_list_orcon_cat_a
@start_date datetime
as
declare @lErrorNo integer
declare @lRowsAffected integer
set @start_date = convert(datetime, '24/07/2007', 103)
begin
select convert(varchar(10), report_date, 103) AS rep_date,
priority_text,
dt_switch,
dt_arrive,
datediff(minute, dt_switch, dt_arrive) as timediff
fromdbo.cadcasedetails
where (dbo.cadcasedetails.dt_arrive is not null)
and (priority_text = 'A')
and (dbo.cadcasedetails.report_date = @start_date)
order by timediff asc
end
Test Script:
--declare @start_date as datetime
--set @start_date = convert(datetime, '24/07/2007', 103)
exec sp_list_orcon_cat_a @start_date
GO
[/font]
Let me know,
Thanks,
Mahesh
MH-09-AM-8694
April 2, 2008 at 5:34 am
Hi
What do u mean by "not recognised"? Is there any error or the proc is not returning the dataset that u expect.
"Keep Trying"
April 2, 2008 at 5:46 am
bill.humphrey (4/2/2008)
where (dbo.cadcasedetails.dt_arrive is not null)and (priority_text = 'A')
and (dbo.cadcasedetails.report_date = @start_date)
order by timediff asc
If you are not getting an error and just getting no records, check that you have data that agrees with your 'WHERE' clause first (sounds simple, but it happens :D)
dt_arrive is not null
priority_text = 'A'
report_date = @start_date
If it was easy, everybody would be doing it!;)
April 2, 2008 at 5:51 am
OK I tried it but it still returns a blank row and the record exists in the data returned when I don't use the date parameter. I'm using SQL 2000 not sure if this makes any difference
procedure:
alter procedure sp_list_orcon_cat_a
@start_date datetime
as
declare @lErrorNo integer
declare @lRowsAffected integer
set @start_date = convert(datetime, '24/07/2007', 103)
begin
select convert(varchar(10), report_date, 103) AS report_date,
priority_text,
dt_switch,
dt_arrive,
datediff(minute, dt_switch, dt_arrive) as timediff
fromdbo.cadcasedetails
where (dbo.cadcasedetails.dt_arrive is not null)
and (priority_text = 'A')
and (dbo.cadcasedetails.report_date = @start_date)
order by timediff asc
script:
declare @start_date as datetime ("cant use param without this declaration")
--set @start_date = convert(datetime, '24/07/2007', 103)
--set @start_date = Convert(datetime, '24/07/2007 00:00.000', 103)
exec sp_list_orcon_cat_a @start_date
Kind Regards
Bill Humphrey
April 2, 2008 at 5:54 am
I'm pulling a sample date from the dataset when the date parameter is not set so I know the record should qualify the criteria
Kind Regards
Bill Humphrey
April 2, 2008 at 6:00 am
Does the datetime field in the database have a time component? Or is the time 00:00.
If the time is not 00:00, you won't get a match.
[Edit: it won't match your current param unless it is exactly the same]
If it was easy, everybody would be doing it!;)
April 2, 2008 at 8:22 am
Ye got it working at last, although it was displaying dd/mm/yyyy as mentioned I still needed to include 00:00:00 but because there was no record for '24/07/2007 00:00:00' I needed to include, report_date BETWEEN CONVERT(DATETIME, @start_date, 103) AND CONVERT (DATETIME, @start_date,103)+1) in the sp and that captured all the records for that date:w00t: :
In the script:
declare @start_date as nvarchar(19)
set @start_date = '24/07/2007 00:00:00'
print @start_date
exec sp_list_orcon_cat_a @start_date
I the procedure:
alter procedure sp_list_orcon_cat_a
@start_date nvarchar(19)
as
declare @end_date as datetime
declare @lErrorNo integer
declare @lRowsAffected integer
begin
select convert(varchar(10), report_date, 103) AS report_date,
priority_text,
dt_switch,
dt_arrive,
datediff(minute, dt_switch, dt_arrive) as timediff
fromdbo.cadcasedetails
where (dbo.cadcasedetails.dt_arrive is not null)
and (priority_text = 'A')
and (report_date BETWEEN CONVERT(DATETIME, @start_date, 103) AND CONVERT (DATETIME, @start_date,103)+1)
order by timediff asc
end
GO
April 2, 2008 at 8:34 am
bill.humphrey (4/2/2008)
set @start_date = '24/07/2007 00:00:00'
I don't believe you need to add the time when setting your start_date, as that is implied...
'24/07/2007 00:00:00' = '24/07/2007' (when datatyped as datetime)
The problem was that you were trying to match exactly on a datetime = '24/07/2007 00:00:00', and your data didn't have that...but you fixed that with the 'BETWEEN'.
Good job!
If it was easy, everybody would be doing it!;)
April 2, 2008 at 8:41 am
Trader Sam (4/2/2008)
bill.humphrey (4/2/2008)
set @start_date = '24/07/2007 00:00:00'I don't believe you need to add the time when setting your start_date, as that is implied...
'24/07/2007 00:00:00' = '24/07/2007' (when datatyped as datetime)
The problem was that you were trying to match exactly on a datetime = '24/07/2007 00:00:00', and your data didn't have that...but you fixed that with the 'BETWEEN'.
Good job!
Yes that's probs true, trying to work with UK date format in SQL is a black art 🙂
Kind Regards BH
April 3, 2008 at 10:55 am
Although the "Between" approach may appear to work, I'd advise making sure it doesn't pick up any records for the next day. Remember that "Between" is inclusive, so theoretically (I haven't tested your example code) using BETWEEN would return rows with both 24/07/2007 and 25/07/2007 in report_date.
April 3, 2008 at 11:15 am
Very good point...
This is why many people use...
where somedate >= '04/01/2008' --target date
and somedate < '04/02/2008' --target date + 1
to get all records that truly have datetime '04/01/2008' regardless of the time portion.
This eliminates any chance of getting something that may have had a date of '04/02/2008 00:00.00'.
If it was easy, everybody would be doing it!;)
April 3, 2008 at 2:00 pm
Hi have you ever try to use varchar instead of datetime parameter??
In UK system is better like that:
declare @start_date as varchar(10)
set @start_date = convert(varchar, '24/07/2007', 103)
exec sp_list_orcon_cat_a @start_date
Procedure:
alter procedure sp_list_orcon_cat_a
@start_date varchar(10)
as
declare @lErrorNo integer
declare @lRowsAffected integer
begin
select convert(varchar(10), report_date, 103) AS rep_date,
priority_text,
dt_switch,
dt_arrive,
datediff(minute, dt_switch, dt_arrive) as timediff
from dbo.cadcasedetails
where (dbo.cadcasedetails.dt_arrive is not null)
and (priority_text = 'A')
and (convert(varchar ,dbo.cadcasedetails.report_date,103) = @start_date)
order by timediff asc
end
GO
April 3, 2008 at 2:46 pm
I'd be leery of doing a CONVERT on each row to compare to a string; it seems that would add unnecessary processing overhead to the query. So how is that better (for UK format or otherwise) than comparing datetime fields in the table against a datetime variable?
April 4, 2008 at 1:42 am
Trader Sam (4/3/2008)
Very good point...This is why many people use...
where somedate >= '04/01/2008' --target date
and somedate < '04/02/2008' --target date + 1
to get all records that truly have datetime '04/01/2008' regardless of the time portion.
This eliminates any chance of getting something that may have had a date of '04/02/2008 00:00.00'.
I checked the data and it did not display any unwanted records, but I like you method better it leaves nothing to chance.
Kind Regards
Bill Humphrey
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply