SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Moving data between tables		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

urmas80
Starting Member

13 Posts
Posted - 07/05/2012 :  08:06:41  Show Profile  Reply with Quote
Hi All,

I has a task, and will be very happy to get some help in it.
I got source table with following data types taht i need to transfer to other table.

TABLE A (Source)
COLUMN A {varchar(50)}
"Customer ID"
"2289"
"5678"
"2223"

COLUMN B {varchar(50)}
"Date"
"01/01/01"
"30/05/02"
"25/09/02"


Expected Result:

TABLE B (Destination)
COLUMN A {int}
"Customer ID"
2289
5678
2223

COLUMN B {date}
"Date"
01-01-01
05-30-02 or 30-05-02
25-09-02 or 09-25-02

As you has noticed i has here few aspects:

1. In column A and B i need to remove the " sign
2. Change the date type (i guess that in column A it's will be automatically as soon as i remove the ").
3. change the date format.. i not sure even how do i start with that...

Thank You in advance!
Edited by - urmas80 on 07/05/2012 08:07:36

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 07/05/2012 :  08:22:07  Show Profile  Reply with Quote 
INSERT INTO TableB
([Customer Id], [Date])
SELECT
	CAST(REPLACE([Customer Id],'"','') AS INT),
	CONVERT(DATE, REPLACE([Date],'"',''),103)
FROM
	TableA;
If you don't have clean data - for example, if some of the Customer Id's are non-numeric, or if some of the dates are not in the correct format, this would fail.
Go to Top of Page

urmas80
Starting Member

13 Posts
Posted - 07/05/2012 :  08:42:42  Show Profile  Reply with Quote
Thank you for very quick response and so simple answer!

Looks like it works till it gets to the date, then i get the following error:


Conversion failed when converting date and/or time from character string.


i see that all date is in correct format except of the ["] sign...

what if i leave it in the source format..? can you explain the convert line please?
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 07/05/2012 :  08:53:26  Show Profile  Reply with Quote
Sorry about that - change it to this:
INSERT INTO TableB
([Customer Id], [Date])
SELECT
	CAST(REPLACE([Customer Id],'"','') AS INT),
	CONVERT(DATE, REPLACE([Date],'"',''),3)
FROM
	TableA;
CONVERT function converts one data type to another. The style (third parameter) conveys information about the format of the input string. Take a look at this page: http://msdn.microsoft.com/en-us/library/ms187928.aspx
Go to Top of Page

urmas80
Starting Member

13 Posts
Posted - 07/05/2012 :  09:05:41  Show Profile  Reply with Quote
Great!! Thank you, that worked!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000