Solved

how to do inserting while typecasting?

Posted on 2014-09-16
9
203 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 24

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 24

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
 
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 69

Expert Comment

by:ScottPletcher
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now