how to do inserting while typecasting?

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.
akohanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
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
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
chaauCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PortletPaulfreelancerCommented:
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
Scott PletcherSenior DBACommented:
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
akohanAuthor Commented:
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
PortletPaulfreelancerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
akohanAuthor Commented:
Hi PortletPaul,

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

Thank you!
0
akohanAuthor Commented:
Thank you all for your help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.