T-SQL help comparing dates to text in UPDATE

I have two date fields in the SemesterCourses table (course start date and course end date). I have another table for translating the date range to a code.  This sql statement converts the dates to a string and combines them as needed.
SELECT CONVERT(VARCHAR(10), CrsStartDate, 101)+ '-' + CONVERT(VARCHAR(10), CrsEndDate, 101) as DateRange FROM SemesterCourses WHERE Semester = '13J')
two dates converted to varchar and concatenated
The translation table has these rows.
SummerSession entries in translation table
What I want to do is compare the DateRange string from the SemesterCourses table to the Description field in the TranslationTable and update the field SummerSession in the SemesterCourses table with the Code from the TranslationTable.

Something like
UPDATE SemesterCourses SET s.SummerSession = t.Code
FROM SemesterCourses s, TranslationTable t
WHERE t.Description= ??

Thanks for your help.
Susan
scover22Asked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
The new columns should be defined as "date" or "datetime" data types.

Based on your original code, it looks like the SemesterCourses.CrsStartDate and CrsEndDate are already date/datetime.

It's also possible that the "SemesterCourses.SummerSession" column is not long enough to hold the entire string in the "SummerSessionDates.SummerSession" column, so  you'll need to check that also.
0
 
Scott PletcherSenior DBACommented:
Trying to do it that way is going to be an endless headache for you.

Store the dates as separate date columns, CrsStartDate and CrsEndDate, in both tables and compare them that way.

If you need a character combined column for display, create a computed column for that; you can persist (physically store) the computed column if you have to.
0
 
scover22Author Commented:
I'm trying to use the generic table already in the db to store the summer session codes. I know I can create a table specifically for the summer session values, but I'd like to avoid that if this is possible. I've made a little more progress with the sql by adding a specific course so I only get one record returned from the SELECT in the UPDATE.  It still doesn't work.  

UPDATE SemesterCourses SET SemesterCourses.SummerSession = Translationtables.Code
FROM SemesterCourses, Translationtables
WHERE Translationtables.FieldName = 'SummerSession' AND Translationtables.Description =
(SELECT CONVERT(VARCHAR(10), CrsStartDate, 101)+ '-' + CONVERT(VARCHAR(10), CrsEndDate, 101) as DateRange
FROM SemesterCourses
WHERE SemesterCourses.SemesterPS = '2135' AND SemesterCourses.Course = 'ART180194')
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Scott PletcherSenior DBACommented:
If possible, add computed columns to the existing table to isolate the dates.

If you can't, we've got to deal with how to match "06/03/2013-07/05/2013" to "6/3/2013-7/5/2013".

So let's skip the standard CONVERT and do our own conversion:

...
WHERE ... AND Translationtables.Description = CAST(MONTH(CrsStartDate) AS varchar(2)) + '/' + CAST(DAY(CrsStartDate AS varchar(2)) + '-' + CAST(YEAR(CrsStartDate AS char(4)) + '-' + CAST(MONTH(CrsEndDate) AS varchar(2)) + '/' + CAST(DAY(CrsEndDate AS varchar(2)) + '-' + CAST(YEAR(CrsEndDate AS char(4))
...
0
 
scover22Author Commented:
Scott,  in the interest of getting this problem behind me, I took your advice and created a new table with separate fields for the start and end dates.
New table
Now I'm using the following sql, but get the error
"String or binary data would be truncated."

UPDATE SemesterCourses SET SemesterCourses.SummerSession = SummerSessionDates.SummerSession
FROM SemesterCourses, SummerSessionDates
WHERE SummerSessionDates.CrsStartDate = SemesterCourses.CrsStartDate
AND SummerSessionDates.CrsEndDate = SemesterCourses.CrsEndDate
AND SemesterCourses.SemesterPS = '2135' AND SemesterCourses.Course = 'ART180194'

Thanks.
0
 
scover22Author Commented:
The field size was too small. I ended up using this code which works great.

UPDATE SemesterCourses SET SemesterCourses.SummerSession = SummerSessionDates.SummerSession
FROM SemesterCourses, SummerSessionDates
WHERE Datediff(dd,SummerSessionDates.CrsStartDate,SemesterCourses.CrsStartDate) = 0
AND Datediff(dd,SummerSessionDates.CrsEndDate,SemesterCourses.CrsEndDate) = 0
AND Right(SemesterCourses.SemesterPS,1) = '5'
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.