Solved

T-SQL help comparing dates to text in UPDATE

Posted on 2013-01-04
6
410 Views
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.
Susan
0
Comment
Question by:scover22
  • 3
  • 3
6 Comments
 
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.
0
 

Author Comment

by:scover22
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')
0
 
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))
...
0
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.

 

Author Comment

by:scover22
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'

Thanks.
0
 
LVL 69

Accepted Solution

by:
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.
0
 

Author Closing Comment

by:scover22
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'
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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

770 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