I have a field that is setup to be a varchar in the system. When I select it, it appears as follows: 20100502. As a date I would like it to appear as 05/02/2010. I know I can do a convert(datetime, feildname,XX) but I am stuck on the xx part.
Any help is appreciated.
asked Mar 19 '10 at 12:03 PM in Default
I concur with Squirrel that formatting should be done on the view of the data. Dates can sometimes be pesky to work with though. The below SQL might help to understand what's happening with your VARCHAR to DATE conversions.
The options for converting date and datetime data are found on MSDN at: http://msdn.microsoft.com/en-us/library/ms187928(SQL.90).aspx
answered Jun 12 '10 at 12:42 PM
this will convert your date in fieldname in varchar to data data type
convert(date, fieldname, 112)
you can use datatime if you like.
to appear as "05/02/2010" this is formatting issue. Do it in your front end application or reporting tools where the data is shown
answered Mar 19 '10 at 08:45 PM