Solved

TEXT Month and Year to DATE Month and Year

Posted on 2008-10-09
9
803 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
 
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

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 …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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 …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

863 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now