| Author |
Topic |
|
jung1975
Aged Yak Warrior
503 Posts |
 Posted - 2005-08-08 : 16:15:32
|
| I have a column called [date of birth] with Varchar(20) data typethe data looks like '18-May-53'I would like to convert this data format to '05/18/53'. How can I do this? |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-08-09 : 01:48:52
|
| >> have a column called [date of birth] with Varchar(20) data typeWhy did you use Varchar datatype to store date when datetime field is available?MadhivananFailing to plan is Planning to fail |
 |
|
|
kapilarya
Yak Posting Veteran
86 Posts |
Posted - 2005-08-09 : 03:37:02
|
| check this outDECLARE @date varchar(10)SELECT @date = '18-May-53'SELECT convert(varchar,CONVERT(datetime,@date,1),1)Kapil Arya |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-08-09 : 04:45:58
|
| This type of conversions are not necessary if proper data types are usedMadhivananFailing to plan is Planning to fail |
|
|
|
jung1975
Aged Yak Warrior
503 Posts |
Posted - 2005-08-09 : 11:44:56
|
| Problem is there are 6000 records , so I can't manually input the data like you said..DECLARE @date varchar(10)SELECT @date = '18-May-53'SELECT convert(varchar,CONVERT(datetime,@date,1),1) |
|
|
|
nosepicker
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-08-09 : 11:56:38
|
| Just as a small clarification, the style parameter of '1' doesn't do anything with the datetime datatype, only with the varchar datatype. Therefore, the conversion should be:SELECT CONVERT(varchar, CONVERT(datetime, @date), 1) |
|
|
|
jung1975
Aged Yak Warrior
503 Posts |
Posted - 2005-08-09 : 12:09:49
|
| I am trying to use a cursor to convert every records in the table..but gettign an errer: "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value." declare @date varchar(20)declare cc cursorfast_forwardfor select [date of birth] from f_labone_stagingOpen ccfetch next from cc into @datewhile @@fetch_status = 0begin update f_labone_stagingset [date of birth] = CONVERT(varchar, CONVERT(datetime, [date of birth]), 1)Fetch next from cc into @dateendclose ccdeallocate cc |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-08-09 : 12:33:55
|
| First, skip the cursor. Just issue the UPDATE statement and let it run as a set-based operation. It will be amazingly faster than the cursor.Second, if you're getting and out-of-range value error message, that is probably because you have an incorrectly entered date. You'll need to do some searching and scrubbing of the data. Perhaps search for records that have only one hyphen or that have more than two hyphens. Or, with 6000 records, it wouldn't take long to just do a visual scan looking for a date that is out of alignment.---------------------------EmeraldCityDomains.com |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-08-09 : 12:51:57
|
Oh, SURE, Brett! That's just like you to come up with an easier way.   ---------------------------EmeraldCityDomains.com |
|
|
|
jung1975
Aged Yak Warrior
503 Posts |
Posted - 2005-08-09 : 13:13:55
|
Thanks! I found the problem... |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
convert data format