asked on

convert 2 fields into a date field in sql

Hi all.

I have a sql table with 2 columns: SaleMonth and SaleYear.

I want to be able to have the end user enter date range parameters (i.e. 11/2010 - 01/2011) the only way I think I can do this is by creating another column by combining my 2 columns (SaleMonth and SaleYear) like this: 11/1/2010 and 1/1/2011

Any ideas how I can do this?

Thank you in advance.

Jesus Rodriguez

You can have only oen column with the date and them let the client select the range that he wants. Ex:

SaleDate (datetime)
05/01/2011
05/20/2011
04/2/2011
03/2/2011

Then you can query your table like this
select * from table where saledate between '04/04/2011' and '05/20/2011'
and create the two dates as parameter in the query and also give the date range on the days if you want

Jesus Rodriguez

If you have to use the two columns then you can say this

select * from table
where cdate(salemonth+'/01/'+saleyear) between Date1 and Date2

where date1 and date2 will be two dates, but as I told you before, I recommend you the first solution.

bhoenig

This should get you started.

declare @InputParameterStart varchar(10)
set @InputParameterStart = '11/2010'

declare @month char(2)
declare @year char(4)
declare @StartDate datetime

set @month = substring(@InputParameterStart,1,2)
set @year = substring(@InputParameterStart, 4,4)
--print @month
--print @year

set @StartDate = cast(@year + '/' + @month + '/01' as datetime)
print @StartDate

Open in new window

printmedia

ASKER

Thanks k-designers. But when I try using cdate I get the following error: 'cdate' is not a recognized function name.

I'm using SQL Server 2000.

printmedia

ASKER

By the way I would be inserting that into a datetime field

ASKER CERTIFIED SOLUTION

ralmada

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial