Changing formats from Varchar to Date

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

General SQL Server Forums

New to SQL Server Programming

Changing formats from Varchar to Date

Author

Topic

Widz
Starting Member

11 Posts

Posted - 2009-10-14 : 10:30:26

I have imported a text file into SQL (MS SQL Server management studio express)and i have the field reading as a varchar and the date is like 1/3/1978 and i want it as 01/03/1978 how do i go about it? i have tried the convert and its giving me an error message.

Any Help

Information Analyst

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-14 : 10:45:46

like:


DECLARE @d varchar(20)
SET @d = '1/3/1978'
SELECT @d, CONVERT(datetime, @d,101)

????

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-14 : 10:47:15

It's a good practice to import data into a staging table where you can limit data validation and referential integrity errors. Then, in a more controlled fashion, insert/transform the data into permanent table(s) with appropiate datatypes for all columns. So Dates should only be in DATETIME and SMALLDATETIME columns. Then you can use your front end application or reporting application to format those dates however you want.

Post the convert statement you used to get the error along with the data value that caused the error.

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-15 : 10:18:44

Looks like there is at least one value which is not a well formed date. Try to identify them with:
select Birth_Date from MDM_DYNAMIC_MDM_EMPLOYEE_FILE where isDate(birth_date) = 0

Once you correct the data then you can change the datatype:
alter table MDM_DYNAMIC_MDM_EMPLOYEE_FILE alter column Birth_date datetime

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-16 : 03:52:09

quote:
Originally posted by TG

Looks like there is at least one value which is not a well formed date. Try to identify them with:
select Birth_Date from MDM_DYNAMIC_MDM_EMPLOYEE_FILE where isDate(birth_date) = 0

Once you correct the data then you can change the datatype:
alter table MDM_DYNAMIC_MDM_EMPLOYEE_FILE alter column Birth_date datetime

Be One with the Optimizer
TG

Isdate() is not fully reliable. In this case you need to check the length as well
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/09/24/handle-isdate-with-care.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources