x

in 2008 how do I convert a varchar to a date

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.

more ▼

asked Mar 19, 2010 at 12:03 PM in Default

user-986 (google) gravatar image

user-986 (google)
11 2 2 2

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

DECLARE @dtTimeAsVarChar VARCHAR(8)            
DECLARE @formattedDt DATE            
            
SET @dtTimeAsVarChar = '20100506'  -- Data to Manipulate            
            
SET @formattedDt = CONVERT(DATE,@dtTimeAsVarChar,112)  --- Convert to Real Date            
            
SELECT CONVERT(CHAR(10),@formattedDt,101) AS [mm/dd/yyyy]             
SELECT CONVERT(CHAR(10),@formattedDt,102) AS [yyyy.mm.dd]            
SELECT CONVERT(CHAR(11),@formattedDt,106) AS [dd MMM yyyy]            

Returns

mm/dd/yyyy            
----------            
05/06/2010            
            
yyyy.mm.dd            
----------            
2010.05.06            
            
dd MMM yyyy            
-----------            
06 May 2010            

The options for converting date and datetime data are found on MSDN at: http://msdn.microsoft.com/en-us/library/ms187928(SQL.90).aspx

more ▼

answered Jun 12, 2010 at 12:42 PM

Jay D gravatar image

Jay D
128 1 1 3

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Mar 19, 2010 at 08:45 PM

Squirrel 1 gravatar image

Squirrel 1
1.6k 1 3

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x21

asked: Mar 19, 2010 at 12:03 PM

Seen: 912 times

Last Updated: Mar 19, 2010 at 12:03 PM