Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

T-SQL help comparing dates to text in UPDATE

Posted on 2013-01-04
6
Medium Priority
?
423 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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1000 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 70

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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 

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 70

Accepted Solution

by:
Scott Pletcher earned 1000 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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

705 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