Solved

TEXT Month and Year to DATE Month and Year

Posted on 2008-10-09
9
817 Views
Last Modified: 2010-04-21
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.
0
Comment
Question by:jobprojn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22681992
You can use the SQL year() and month() functions.

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

Expert Comment

by:BrandonGalderisi
ID: 22682000
so:

select * from YourTable
where year(YourDatefield) <= 2006
and month(YourDateField) <= 1
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22682011
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)
0
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
LVL 24

Accepted Solution

by:
DBAduck - Ben Miller earned 125 total points
ID: 22682030
You could also create a date and then use it to compare.

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

SELECT blah
FROM table1
WHERE @varToCompare <= YourDate
0
 

Author Comment

by:jobprojn
ID: 22682059
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?
0
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
ID: 22682089
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.
0
 

Author Closing Comment

by:jobprojn
ID: 31504807
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.
0
 

Author Comment

by:jobprojn
ID: 22682181
Will use dbaduck's solution, but need to add the dateadd function to tack on one full month to allow for accurate <= compares.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22682412
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 <.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question