Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

TEXT Month and Year to DATE Month and Year

Posted on 2008-10-09
9
Medium Priority
?
825 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 25

Accepted Solution

by:
DBAduck - Ben Miller earned 500 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 25

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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone 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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

660 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