Date Conversion - SQL Server Forums

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

Script Library

Date Conversion

Author

Topic

bill_nirl
Starting Member

2 Posts

Posted - 2009-07-09 : 05:48:48

Hi, wonder if anyone can help.

I have a field in a sql db that was being used as a date field, but not in date format (just text). i have some dates in it like 01.01.2006 and 13.12.2007, but i also have dates like 01/03/2006 and 04/03/2008. I would like to convert them to a new field and format them so they are all in dd/mm/yyyy format, any ideas?

ive tried and update query with the format and dateconvert but still gives me an error.... dont wana do it manually as thr are over 3000 dates.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-07-09 : 06:01:11

No need to create another column

update table set datecol=REPLACE(datecol,'.','/')

then alter the column as datetime data type

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-09 : 06:38:31

Hi

Try this

SELECT CONVERT(VARCHAR(12),CONVERT(DATETIME,'01.01.2006'),103)

-------------------------
R..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-09 : 06:41:12

Always use proper datetime datatype to store dates and let the front end application do the formation

Madhivanan

Failing to plan is Planning to fail

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-07-09 : 07:26:33

Agreed
take the time to change this to a DATETIME column now -- you will save yourself pain later.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

bill_nirl
Starting Member

2 Posts

Posted - 2009-07-10 : 22:35:30

thanks for those who helped.

others that sent their words of wisdom, get a life, please!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-07-10 : 23:31:48

quote:
Originally posted by bill_nirl

thanks for those who helped.

others that sent their words of wisdom, get a life, please!

I think everyone was trying to help you.

You are having a problem that just could not happen if you used a datetime column. Besides the mixed date formats, it is likely that you will find completely invalid dates.

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-11 : 01:41:34

quote:
Originally posted by bill_nirl

thanks for those who helped.

others that sent their words of wisdom, get a life, please!

Ok. Fine. Now tell me, how you will sort the dates without any convertion

Madhivanan

Failing to plan is Planning to fail

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-11 : 01:48:49

quote:
others that sent their words of wisdom, get a life, please!

meanie...

Hope can help...but advise to wait pros with confirmation...

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-07-13 : 06:25:37

Fine bill,

I shall not try to help you should I see further posts from you.

Good day to you sir!

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources