String to date conversion 1

[cfcProgrammer]

Hi all,
I've looked at several posts and googled this.. but I can't seem to get this to work for me.

I am calling a stored procedure from an ASP Page passing in a date.

In my stored procedure I have @FromDate and @ToDate defined at varchar(10), the dates passed in will be in mm/dd/yyyy format. Then in my select statement in the where clause I am trying to limit the data by using BETWEEN. Can someone help me figure out how to do this conversion.

Here is the stored procedure.

CREATE procedure FishStatusReportByArea

@FromDate 	varchar(10),
@ToDate      	varchar(10),
@Region       	varchar(3) ,
@Sort 		varchar(5)
as

select 	 ft.area,
	 fc.species, 
	 sum(d.dir_hrs),
	sum(d.dom_id),
	 sum(d.for_id),
	sum(d.dom_obs),
	sum(d.for_obs)
from    asis_directed d
join     asis_report_info ri on d.patrol_no=ri.patrol_no
join     asis_fhmtask ft on ft.patrol_no=d.patrol_no and ft.ref_no=d.ref_no
join     asis_fishery_code fc on fc.fishery = ft.fishery
where    d.patrol_no like ''+@Region+'%' 
and      ri.patrol_date between '@FromDate' and '@ToDate'
group by ft.area,ri.patrol_date,fc.species
order by ft.area @Sort
GO

Thank you so much
Colleen

cfcProgrammer

 

[djj55]

If you use dynamic sql it would be something like:

DECLARE @strSQL varchar(500)
@strSQL = 'select ft.area, fc.species, sum(d.dir_hrs), sum(d.dom_id), 
    sum(d.for_id), sum(d.dom_obs), sum(d.for_obs) 
    from asis_directed d
    join asis_report_info ri 
    on d.patrol_no=ri.patrol_no
    join asis_fhmtask ft 
    on ft.patrol_no=d.patrol_no 
        and ft.ref_no=d.ref_no
    join asis_fishery_code fc 
    on fc.fishery = ft.fishery
    where d.patrol_no like ' + @Region + '%'
        and ri.patrol_date between '' + @FromDate + '' and '' + @ToDate + '''
    group by ft.area,ri.patrol_date,fc.species
    order by ft.area. ' + @Sort
execute (@strSQL)

the single quotes may need work as I did not test this.

Good Luck
djj

 

[cfcProgrammer]

I've tried several things... and nothing seems to be working... I know this is simple code but it is holding me up... there is nothing more frustrating... when you have something so simple causing issues.

I would really appreciate any help that someone could give me.

Here is an example of one of the things I tried.. that is still giving me an error.

CREATE procedure FishStatusReportByArea

@FromDate 	varchar(10) ,
@ToDate      	varchar(10) ,
@Region       	varchar(3) ,
@Sort 		varchar(5)
as

select 	 ft.area,
	 fc.species, 
	 sum(d.dir_hrs),
	sum(d.dom_id),
	 sum(d.for_id),
	sum(d.dom_obs),
	sum(d.for_obs)
from    asis_directed d
join     asis_report_info ri on d.patrol_no=ri.patrol_no
join     asis_fhmtask ft on ft.patrol_no=d.patrol_no and ft.ref_no=d.ref_no
join     asis_fishery_code fc on fc.fishery = ft.fishery
where    d.patrol_no like ''+@Region+'%' 
and      ri.patrol_date between "' +  CONVERT(datetime,@FromDate,103) + '" and "' + CONVERT(datetime,@ToDate,103)'"
group by ft.area,ri.patrol_date,fc.species
order by ft.area
GO

cfcProgrammer

 

[jbenson001]

and ri.patrol_date between "' + CONVERT(datetime,@FromDate,103) + '" and "' + CONVERT(datetime,@ToDate,103)'"

Why do you have quotes of any kind in here? You are not doing dynamic SQL. And why are you using format style 103? Use 101 instead.

Try:

and      ri.patrol_date between CONVERT(datetime,@FromDate,101) and CONVERT(datetime,@ToDate,101)