Solved

T-SQL help comparing dates to text in UPDATE

Posted on 2013-01-04
6
419 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
[X]
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
  • 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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

729 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