How to convert DATE Format from DD/MM/YY to MM/DD/

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

How to convert DATE Format from DD/MM/YY to MM/DD/

Author

Topic

shekarpc
Starting Member

5 Posts

Posted - 2004-04-23 : 16:40:37

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

38200 Posts

Posted - 2004-04-23 : 16:45:06

SELECT DATEPART(m, DOB) + '/' + DATEPART(d, DOB)
FROM Table1

Tara

shekarpc
Starting Member

5 Posts

Posted - 2004-04-23 : 16:49:30

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-23 : 16:54:06

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

shekarpc
Starting Member

5 Posts

Posted - 2004-04-23 : 16:57:57

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-23 : 17:04:14

What is the format of the cell in Excel? Try changing it. Then import.

Tara

shekarpc
Starting Member

5 Posts

Posted - 2004-04-23 : 18:02:21

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-23 : 18:04:13

No, I mean:

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

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-26 : 15:03:08

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?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources