Solved

SQL - Syntax help String Manipulation - SQL Server 2005

Posted on 2011-03-23
4
257 Views
Last Modified: 2012-05-11
Hello experts,

I am really reaching on this one, but I have a scenario that I need help coding please, not sure if it can be done.  I have a table of appointments below, and there are two columns, both varchar(50) datatypes.  The time and date fields were accidentally merged, and I need to unmerge them.  I currently have:

table: appointments
appt_date             appt_time
null                        Appointment date: 02/28/2011
null                        08:00 AM
null                        09:00 AM
null                        10:00 AM
null                        11:00 AM
null                        Appointment date: 02/29/2011
null                        08:00 AM
null                        09:00 AM
null                        10:00 AM
null                        11:00 AM
null                        Appointment date: 02/30/2011
null                        08:00 AM
null                        09:00 AM
null                        10:00 AM
null                        11:00 AM

and I need:

table: appointments
appt_date                                      appt_time
Appointment date: 02/28/2011      08:00 A
Appointment date: 02/28/2011      08:00 AM
Appointment date: 02/28/2011      09:00 AM
Appointment date: 02/28/2011      10:00 AM
Appointment date: 02/28/2011      11:00 AM
Appointment date: 02/29/2011      08:00 A
Appointment date: 02/29/2011      08:00 AM
Appointment date: 02/29/2011      09:00 AM
Appointment date: 02/29/2011      10:00 AM
Appointment date: 02/29/2011      11:00 AM
Appointment date: 02/30/2011      08:00 A
Appointment date: 02/30/2011      08:00 AM
Appointment date: 02/30/2011      09:00 AM
Appointment date: 02/30/2011      10:00 AM
Appointment date: 02/30/2011      11:00 AM

update appointments
set appt_date ?

Thoughts?

Thanks!
0
Comment
Question by:robthomas09
[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
4 Comments
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 425 total points
ID: 35202096
robthomas09,

How are the rows with just time values related back to a specific date?  

If the data is truly as you showed above and the times are consistent over all the dates, you may want to consider clearing the entire table.

e.g.,
truncate table appointments;

Open in new window


Then you can insert new values using a cross join like so:
insert into appointments(appt_date, appt_time)
select appt_date, appt_time
from (
   select '2011-02-28' as [appt_date]
   union select '2011-03-01'
   union select '2011-03-02'
) dates
cross join (
   select '08:00 AM' as [appt_time]
   union select '09:00 AM'
   union select '10:00 AM'
   union select '11:00 AM'
) times
;

Open in new window


If you have a lot of dates (i.e., a range of dates), then you can use a numbers or dates table as the listing of dates -- using CONVERT to format the date as VARCHAR however you like.  Similarly, you can probably build the times in this same fashion.  If you move to SQL 2008, you can consider DATE and TIME data types.  For SQL 2005, you can consider having two DATETIME columns.  One with date at midnight to signify the day and another for time whose date can be ANSI date 0 (e.g., 1900-01-01 08:00:00, 1900-01-01 09:00:00, etc.).  You can use the same CONVERT functions to display as date only or time only, but then get the added benefit of date validation and sorting.  2/30 is probably just sample data, but for example is a bad date that would not be valid input to a true datetime field.

Just a thought.

Best regards and happy coding,

Kevin
0
 
LVL 5

Assisted Solution

by:bitref
bitref earned 50 total points
ID: 35206125
You may use a cursor to pass by the old rows one by one.
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 25 total points
ID: 35207699
What does 08:00A mean?
0
 

Author Closing Comment

by:robthomas09
ID: 35244209
Thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

717 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