TEXT Month and Year to DATE Month and Year

I've got two separate text values that each represent a Month and Year.  I need to compare these text values to a true date/time value stored in a table.

TableValue: 2005-11-28 00:00:00

As for the compare I'm doing, I need to find all records where (TextValues <= TableValue).  Any advice is appreciated.
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

DBAduck - Ben MillerConnect With a Mentor Principal ConsultantCommented:
You could also create a date and then use it to compare.

DECLARE @varToCompare datetime
SELECT @varToCompare = CONVERT(datetime, TextValue2 + '/1/' + TextValue1)

FROM table1
WHERE @varToCompare <= YourDate
You can use the SQL year() and month() functions.

they will return the month and year of a date value.

select * from YourTable
where year(YourDatefield) <= 2006
and month(YourDateField) <= 1
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Realize that 2005-11-28 won't be less in that way.  You would be best to pass in an actual date value for comparison.


where year(YourDatefield) <= 2006
or (year(YourDatefield) < 2006
     and month(YourDateField) <= 1)
jobprojnAuthor Commented:
The month() function returns an error message when used against a text string.  For example:

month(10) = "syntax error converting datetime from character string".
month(CAST('10' AS Int)) = returns value of 1, should return 10, but it comes out as 1

Then once the text values are converted to year() and month() they still have to be converted into a real date/time so that they can be compared against the datetime value in the system, right?
DBAduck - Ben MillerPrincipal ConsultantCommented:
Well, the month() and year() functions are meant to be used on a complete date.

So you would use Month() against the TableValue (  Month(TableValue) ) not a string.  And the same is with the year.

when doing date stuff, you should use date to date in my example.
jobprojnAuthor Commented:
Thanks dbaduck. I will use this solution, only will have to add a dateadd function to add one month to text value 2.  This way when I do the compare of <= days that are greater than the first of the month.  Good Day.
jobprojnAuthor Commented:
Will use dbaduck's solution, but need to add the dateadd function to tack on one full month to allow for accurate <= compares.
If you want to to include the month prompted for, then do < on the dateadd(m,1...) and not <=.  Reason being that if BY CHANCE a record is recorded for the first day of the month at midnight (Nov-1-2008 12am) then it would be included in <= but not <.
All Courses

From novice to tech pro — start learning today.