scover22
asked on
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')
The translation table has these rows.
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
SELECT CONVERT(VARCHAR(10), CrsStartDate, 101)+ '-' + CONVERT(VARCHAR(10), CrsEndDate, 101) as DateRange FROM SemesterCourses WHERE Semester = '13J')
The translation table has these rows.
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.Descript ion = 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))
...
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.Descript
...
ASKER
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.
Now I'm using the following sql, but get the error
"String or binary data would be truncated."
UPDATE SemesterCourses SET SemesterCourses.SummerSess ion = SummerSessionDates.SummerS ession
FROM SemesterCourses, SummerSessionDates
WHERE SummerSessionDates.CrsStar tDate = SemesterCourses.CrsStartDa te
AND SummerSessionDates.CrsEndD ate = SemesterCourses.CrsEndDate
AND SemesterCourses.SemesterPS = '2135' AND SemesterCourses.Course = 'ART180194'
Thanks.
Now I'm using the following sql, but get the error
"String or binary data would be truncated."
UPDATE SemesterCourses SET SemesterCourses.SummerSess
FROM SemesterCourses, SummerSessionDates
WHERE SummerSessionDates.CrsStar
AND SummerSessionDates.CrsEndD
AND SemesterCourses.SemesterPS
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The field size was too small. I ended up using this code which works great.
UPDATE SemesterCourses SET SemesterCourses.SummerSess ion = SummerSessionDates.SummerS ession
FROM SemesterCourses, SummerSessionDates
WHERE Datediff(dd,SummerSessionD ates.CrsSt artDate,Se mesterCour ses.CrsSta rtDate) = 0
AND Datediff(dd,SummerSessionD ates.CrsEn dDate,Seme sterCourse s.CrsEndDa te) = 0
AND Right(SemesterCourses.Seme sterPS,1) = '5'
UPDATE SemesterCourses SET SemesterCourses.SummerSess
FROM SemesterCourses, SummerSessionDates
WHERE Datediff(dd,SummerSessionD
AND Datediff(dd,SummerSessionD
AND Right(SemesterCourses.Seme
ASKER
UPDATE SemesterCourses SET SemesterCourses.SummerSess
FROM SemesterCourses, Translationtables
WHERE Translationtables.FieldNam
(SELECT CONVERT(VARCHAR(10), CrsStartDate, 101)+ '-' + CONVERT(VARCHAR(10), CrsEndDate, 101) as DateRange
FROM SemesterCourses
WHERE SemesterCourses.SemesterPS