Solved

how to do inserting while typecasting?

Posted on 2014-09-16
9
205 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
DTS Connection Failed 7 66
Need help on t-sql 2012 10 53
SQL Server Designer 19 39
Create snapshot on MSSQL 2012 3 17
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
I have a large data set and a SSIS package. How can I load this file in multi threading?
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 backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

776 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