SQL VarChar to Date

Question

hi i am trying to convert a VarChar date field (e.g. 20100320) to a real date field like

'dd/mm/yyyy' (e.g. 20/03/2010).

I have tried two ways: a)

 (SELECT    MIN(CAST(A.DateOfAction AS Date)) AS Expr1
           FROM      ResAdm.Action A
           WHERE    (A.PersonID = P.PersonID)) 

     AS 'Period From',

b)

(SELECT    MIN(CONVERT(DATE, A.DateOfAction, 103)) AS Expr1
           FROM      ResAdm.Action A
           WHERE    (A.PersonID = P.PersonID)) 

     AS 'Period From',

both producing the result like

yyyy-mm-dd (e.g. 2010-03-20)

but i want the result like

dd/mm/yyyy (e.g. 20/03/2010)

any help will be appreciated. thanks.

Answer 1

Try this:

select convert(varchar(8), convert(datetime, min(a.DateOfAction), 112), 103)

Your problem is that once you have a date format, SQL Server will dump it out in its default date format, which you've discovered is yyyy-mm-dd. You need to convert from date to varchar to get the format you want. But to convert from date, you need to first convert to date! 112 is the format for yyyymmdd, and 103 is the format for dd/mm/yyyy, so this is why you need these formats. (Books Online reference for date formats)