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

avatar 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]


mm/dd/yyyy ---------- 05/06/2010



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

avatar image

Jay D
128 2 3 6

(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

avatar image

Squirrel 1
1.6k 1 3 5

(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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Mar 19, 2010 at 12:03 PM

Seen: 1094 times

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

Copyright 2018 Redgate Software. Privacy Policy