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

T-SQL help comparing dates to text in UPDATE

Posted on 2013-01-04
Last Modified: 2013-01-04
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.
Question by:scover22
  • 3
  • 3
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 250 total points
ID: 38745212
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.

Author Comment

ID: 38745239
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')
LVL 69

Expert Comment

by:Scott Pletcher
ID: 38745354
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))
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.


Author Comment

ID: 38745356
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'

LVL 69

Accepted Solution

Scott Pletcher earned 250 total points
ID: 38745403
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.

Author Closing Comment

ID: 38745427
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'

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

809 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