| Author |
Topic |
|
meef
Posting Yak Master
113 Posts |
 Posted - 2011-03-30 : 11:14:16
|
I have a field that displays dates as "20110330" but want it to show the traditional "3/30/2011".I tried CONVERT(VARCHAR(10), p.curr_est_deliver_date, 101) AS [MM/DD/YYYY] but I guess that's the wrong way to try it, I don't see a way to put the column in there. What is the correct way to do this? |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-03-30 : 11:21:11
|
| your field is actually a string, not a date, so you have to convert it to a date first, then back to a stringdeclare @s varchar(10)set @s = '20110330'select convert(varchar(10),convert(datetime,@s),101)JimEveryday I learn something that somebody else already knew |
 |
|
|
meef
Posting Yak Master
113 Posts |
Posted - 2011-03-30 : 11:30:19
|
| Thanks, worked like a charm. |
|
|
|
Trininole
Yak Posting Veteran
83 Posts |
Posted - 2011-03-30 : 14:49:35
|
| so what would be the proper format for converting a date from 19880330 to "1988-03-30"? I still have yet to figure out as i look it up.Roger DeFour |
|
|
|
meef
Posting Yak Master
113 Posts |
Posted - 2011-03-31 : 10:38:30
|
I'm not sure about the dashes, but for "/" you would do exactly as I had to do, and use 111 instead of 101.So select convert(varchar(10),convert(datetime,@s),111) Maybe there's another way, but the only ones I'm seeing with dashes in the dates also include the hours/minutes/seconds. |
|
|
|
meef
Posting Yak Master
113 Posts |
Posted - 2011-03-31 : 14:54:25
|
Ok, ran into another issue that I didn't notice yesterday. I need this is pull from a column, not have the date hard coded in there like above. I tried this:SET @date = (select dbo.tbl_214_datatable.curr_est_deliver_date) the curr_est_deliver_date is the filed that has the date I need. It tells me it can't be bound when I try the above. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2011-04-01 : 07:08:00
|
| If you use front end application, do formation thereMadhivananFailing to plan is Planning to fail |
|
|
|
meef
Posting Yak Master
113 Posts |
Posted - 2011-04-01 : 10:21:23
|
| I tried doing the format in Visual Studio Reporting but it doesn't do anything. I'm using "MM/dd/yyyy". |
|
|
|
meef
Posting Yak Master
113 Posts |
Posted - 2011-04-04 : 09:54:12
|
| Any other ideas? |
|
|
|
|
Converting date format