convert 2 fields into a date field in sql

printmedia
printmedia used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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


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.

Commented:
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

Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
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.

Author

Commented:
By the way I would be inserting that into a datetime field
first of all you date values should be stored in datetime format not in two separate columns. Having said that you can create an additional computed column with the datetime value so whenever you populate the year and month, the new column will be automatically populated with it's corresponding datetime value. So you just need to alter your table like below:

alter table yourtable
add SalesDate as (dateadd(Month, SalesMonth, dateadd(year, SalesYear, 0))) persisted


If you don't want to create this column you can do something like this:

select ... from yourtable
where (dateadd(Month, SalesMonth, dateadd(year, SalesYear, 0))) between '...date1...' and '...date2...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial