Microsoft SQL Server forum - Convert month name, year range to mm/dd/yy

Author

Convert month name, year range to mm/dd/yy

manning_news@hotmail.com

2005-07-22, 8:23 pm

Using SQL 2000. I need to select rows based on a date range. Both the
beginning date and ending date of the range will be entered in the
mm/dd/yy format. There are 2 columns in the table called MonthOfEval
and YearOfEval, both varchar(9) and both with data like "August" and
"2005-2006". I think I need to use these 2 columns to create a date in
the mm/dd/yy format, then use that newly created date to see if it's
within the date range. How would I go about doing this or is there a
better way?

Thanks for any help or advice.

Erland Sommarskog

2005-07-22, 8:23 pm

(manning_news@hotmai
l.com) writes:
> Using SQL 2000. I need to select rows based on a date range. Both the
> beginning date and ending date of the range will be entered in the
> mm/dd/yy format. There are 2 columns in the table called MonthOfEval
> and YearOfEval, both varchar(9) and both with data like "August" and
> "2005-2006". I think I need to use these 2 columns to create a date in
> the mm/dd/yy format, then use that newly created date to see if it's
> within the date range. How would I go about doing this or is there a
> better way?

First of all, when you are in SQL Server you should work with dates
in the datetime datatype, which is a binary container. When you pass
dates to SQL Server, you use parameterised commands or stored procedures.
You pass the client API the date as a string, and the API interprets it
according to regional settings.

If you need to manipulate dates as strings in SQL Server, use the format
YYYYMMDD, as this format is not subject to different interpretations
depending on settings.

Next, it is not clear what "2005-2006" and "August" would mean. The first
I could interpret as 2005-01-01 to 2006-12-31. A single month name is
more tricky. OK, from 08-01 to 08-31, but which year? Any of 2005 and
2006? Or is here some broken fiscal year involved, so that 2005-2006
means 2005-07-01 to 2006-06-30?

The general advice for this sort of question is to post:

o CREATE TABLE (preferrably simplified) for your table.
o INSERT statements with sample data.
o The desired result given the sample.

This has both the effect of clarifying what you are asking for, and to
make it easy to develop a tested solution.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Sponsored Links