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.
TextValue1='2006'
TextValue2='1'
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.
TextValue1='2006'
TextValue2='1'
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.
so:
select * from YourTable
where year(YourDatefield) <= 2006
and month(YourDateField) <= 1
select * from YourTable
where year(YourDatefield) <= 2006
and month(YourDateField) <= 1
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.
OR:
where year(YourDatefield) <= 2006
or (year(YourDatefield) < 2006
and month(YourDateField) <= 1)
OR:
where year(YourDatefield) <= 2006
or (year(YourDatefield) < 2006
and month(YourDateField) <= 1)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
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.
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.
ASKER
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.
ASKER
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 <.
they will return the month and year of a date value.