| 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 |
|
|
|
|
Date Conversion