Solved

TEXT Month and Year to DATE Month and Year

Posted on 2008-10-09
9
806 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
  • 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

786 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