Convert varchar uk date value to correct datetime value using 103 113 styles

Category SQL Sub Category Date And Time

This can prove annoying when the system assumes mm/dd/yyyy usa format as opposed to dd/mm/yyyy uk format.

Solution if you have a varchar string column such as StartDate is to use convert

Copy Code
CONVERT(smalldatetime, StartDate, 103)

So StartDate of '01/04/2010' becomes correct date of !st April 2010 and not 4th Jan 2010.

Other times where this conversion comes in useful is shown in the following examples:

Convert date to string in sql so you can add to string expressions etc:

Copy Code
COALESCE(CONVERT(VARCHAR, p.PaymentStartDate, 103),'') AS PaymentStartDate
--dd/mm/yyyy 
--03/02/2008

COALESCE(CONVERT(VARCHAR, p.PaymentStartDate, 113),'') AS PaymentStartDate
--dd mon yyyy hh:mm:ss:mmm(24h)
--03 Feb 2008 00:00:00:000

--to reduce the time part you can use a smalled text value
COALESCE(CONVERT(VARCHAR(17), p.PaymentStartDate, 113),'') AS PaymentStartDate
--dd mon yyyy hh:mm:ss:mmm(24h)
--03 Feb 2008 00:00:00:000