Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

how to do inserting while typecasting?

Posted on 2014-09-16
9
Medium Priority
?
223 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
  • 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 66

Assisted Solution

by:Jim Horn
Jim Horn earned 400 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 400 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
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!

 
LVL 49

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 70

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 49

Accepted Solution

by:
PortletPaul earned 1200 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

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.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
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 how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

886 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