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
 SQL Server 2000 Forums
 Import/Export (DTS) and Replication (2000)
 How to convert DATE Format from DD/MM/YY to MM/DD/
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shekarpc
Starting Member

5 Posts

Posted - 04/23/2004 :  16:40:37  Show Profile  Send shekarpc a Yahoo! Message  Reply with Quote
Hi
we have a Excel sheet, contains 2 columns, The Table Struc below
-------------
Name, DOB
-------------
XXX, '23/04/1996'
YYY, '26/10/1985'
ZZZ, '10/02/1977'


When i am trying to Import or Export this Data from Excel to SQL Server Table the Data is coming blank for DOB for the Person XXX, YYY becasue the SQL Date format is MM/DD/YYYY. it needs to convert in to this Format. How can i do this. I spend almost half day no clue.

Please let me know if any one know the solution for this.


Shekar PC

tkizer
Almighty SQL Goddess

USA
37143 Posts

Posted - 04/23/2004 :  16:45:06  Show Profile  Visit tkizer's Homepage  Reply with Quote
SELECT DATEPART(m, DOB) + '/' + DATEPART(d, DOB)
FROM Table1

Tara
Go to Top of Page

shekarpc
Starting Member

5 Posts

Posted - 04/23/2004 :  16:49:30  Show Profile  Send shekarpc a Yahoo! Message  Reply with Quote
The Problem is how to convert from EXCEL to DATABASE
If it is there is Database i can do any thing. but it is in the Excel sheet with DATES like
'23/04/2004'
'27/06/1998'

First how to get this Values in to SQL Table like
'04/23/2004'
'06/27/1998'



Shekar PC
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37143 Posts

Posted - 04/23/2004 :  16:54:06  Show Profile  Visit tkizer's Homepage  Reply with Quote
Import the data into a staging table that has VARCHAR as the data type. Then move your data from the staging table to the destination table using T-SQL along with CONVERT and a style (103 style is what you need):

INSERT INTO DestTable (Name, DOB)
SELECT Name, CONVERT(datetime, DOB, 103)
FROM Stage

Tara
Go to Top of Page

shekarpc
Starting Member

5 Posts

Posted - 04/23/2004 :  16:57:57  Show Profile  Send shekarpc a Yahoo! Message  Reply with Quote
when i importing the Data from EXCEL to Staging Table the Data is coming as NULL where ever the Data is
XXX, NULL it supposed to be '23/04/1996'
YYY, NULL' it supposed to be 26/10/1985'

First not able to get the Data in to staging area, u can try just creating a Excel sheet with 2 Records with the DATE like the above...
I know once it came to SQL table i can convert in to any format.....
But problem is it is not even getting in to SQL Table. just coming as NULL
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37143 Posts

Posted - 04/23/2004 :  17:04:14  Show Profile  Visit tkizer's Homepage  Reply with Quote
What is the format of the cell in Excel? Try changing it. Then import.

Tara
Go to Top of Page

shekarpc
Starting Member

5 Posts

Posted - 04/23/2004 :  18:02:21  Show Profile  Send shekarpc a Yahoo! Message  Reply with Quote
The Format is DD/MM/YYYY
We can not change it, because it comes from Client UK, they send the Documents daily couple of time, we have a Job to run daily few times to take that execl file and ipmort the Data in to Excel

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37143 Posts

Posted - 04/23/2004 :  18:04:13  Show Profile  Visit tkizer's Homepage  Reply with Quote
No, I mean:

Right click on the cell in Excel. Go to Format Cells. Try changing the category.

Tara
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 04/26/2004 :  15:03:08  Show Profile  Reply with Quote
DTS, bcp, BULK INSERT?

Which one?

And are you telling us that you have a value in that column, and it comes in to stage as null?

Don't buy that one...

And is your stage column varchar, not datetime?

Post the DDL of your stage table and what method you are using to get the data in to stage...




Brett

8-)

EDIT: I realized it's EXCEL, so I'm guessing DTS

Is the file named the same all the time? Is the process automated or manually run?


Edited by - X002548 on 04/26/2004 15:04:47
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.05 seconds. Powered By: Snitz Forums 2000