Solved

TEXT Month and Year to DATE Month and Year

Posted on 2008-10-09
9
820 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

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 …
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

628 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