Solved

how to do inserting while typecasting?

Posted on 2014-09-16
9
208 Views
Last Modified: 2014-09-18
Let's say we have a table as

Table A
id  int  PK
firstname  varchar(50)
lastname   varchar(50)
dateOfBirth  varchar(10)

and need to insert them all using one SQL statement in Table B, however, table B is
id  int  PK
firstname  varchar(50)
lastname   varchar(50)
dateOfBirth  date

Question: How can I use following statement while making dateOfBirth to go from varchar to date ?

INSERT INTO B (firstname,  Lastname , dateOfBirth )
SELECT firstname, lastname, dateOfBirth
From A

Open in new window


THis works in a normal way but not for type conversion. Any idea?

THanks.
0
Comment
Question by:akohan
[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
  • 2
  • 2
  • +2
9 Comments
 
LVL 25

Expert Comment

by:chaau
ID: 40326934
Just explicitly cast the data:
INSERT INTO B (firstname,  Lastname , dateOfBirth )
SELECT firstname, lastname, CONVERT(VARCHAR(10), dateOfBirth, 121)
From A

Open in new window

0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 100 total points
ID: 40326942
To expand, you'll have to make sure that the values being converted are actually in date format, otherwise you'll get an error.  So borrowing from the above, first run this...
SELECT firstname, lastname, dateOfBirth
From A
WHERE ISDATE(dateOfBirth) = 0

Open in new window

If it returns no rows, then you're good to go.  If it does return rows, such as 'banana', 'February 31st, 2014', '00/00/0000', then you'll have to figure out how to handle those values, either by changing data to a valid date format, or not inserting the row.

Then run the above query with this at the end -->  
INSERT INTO B (firstname,  Lastname , dateOfBirth )
SELECT firstname, lastname, CONVERT(VARCHAR(10), dateOfBirth, 121)
From A
WHERE ISDATE(dateOfBirth) = 1

Open in new window

0
 
LVL 25

Assisted Solution

by:chaau
chaau earned 100 total points
ID: 40326946
Just re-read the question. You are trying to convert from varchar to date, I have provided an answer to convert from date to varchar. In addition to Jim's comments above I would like to add this. If you still to insert records with invalid date of birth you can use CASE statement:
INSERT INTO B (firstname,  Lastname , dateOfBirth )
SELECT firstname, lastname, CASE WHEN ISDATE(dateOfBirth) THEN CONVERT(DATE, dateOfBirth) END
From A

Open in new window

The query above will insert all records from table A, and for those records with an invalid date of birth, the date will be NULL
0
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.

 
LVL 48

Expert Comment

by:PortletPaul
ID: 40326986
For those with an invalid date of birth varchar you will need to locate those and correct them...
before loading into the other table if possible.

but that is not all

what is this date?

                                    02/10/1990

                                    (is it October? is it February?)

Let's just hope that varchar field does use a sensible format like YYYY-MM-DD

no points please
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40328404
Actually, one would normally hope the varchar column uses the only genuinely sensible, and unambiguous, format of YYYYMMDD (I know, the column's "too long" for that).  The dashes waste disk space and cause ambiguity, a double error.
0
 

Author Comment

by:akohan
ID: 40329188
I always get the date in one format since coming from one source and not entered by users. so I'm assuming i can go with format I have.

Yes, now it works. One thing I forgot to ask here is that I like to have dateOfBirth as DateTime.  The truth is that I have to columns one DateOfBirth and TimeOfBirth. In my question you helped me to get an idea on that but need to mix TimeOfBirth too.

Can you comment on this please? anyway I will give the credit to this thread.

Thanks.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 300 total points
ID: 40329233
akohan  

Adding requirements progressively is not the way to do it.
You knew of the existence of TimeOfBirth but excluded it from the question.

You also have NOT provided exact details on the format of either of these fields, so we must rely on assumptions - and this can lengthen resolution because we don't always guess correctly.
--------------------------

dateOfBirth  assume it is in YYYY-MM-DD format
TimeOfBirth  assume this is hh:mm:ss    

convert(datetime, DateOfBirth + ' ' + TimeOfBirth, 121) as "whatever you will call it"

Open in new window


If this does not match the formats of those fields look through the available date styles until you find a style number to suit. Remember you will be concatenating the date and time separated by a space

-----------------
OR perhaps for an ISO8601 date pattern YYYY-MM-DDThh:mm:ss

convert(datetime, DateOfBirth + 'T' + TimeOfBirth, 126) as "whatever you will call it"

Open in new window

0
 

Author Comment

by:akohan
ID: 40331221
Hi PortletPaul,

You are right, please accept my apologies I guess happens when working one works with busy mind.

Thank you!
0
 

Author Closing Comment

by:akohan
ID: 40331227
Thank you all for your help!
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

751 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