Zusee Weekin Zusee Weekin - 4 months ago 12
SQL Question

change date format

I have a date field called TimeStampUTC in

2016-08-01 03:00:00.000
format but I need to return it in
01/08/2016


I'm using the following query

SELECT Convert(varchar(2),DATEPART(DAY, a.TimeStampUTC)) + '/'+ Convert(varchar(2),DATEPART(MONTH, a.TimeStampUTC)) + '/' + Convert(varchar(4),DATEPART(year, a.TimeStampUTC)) AS Date FROM table AS a WHERE a.TimeStampUTC > '$startDate' AND a.TimeStampUTC <='$endDate'


Issue is it return as
1/8/2016


How to write the query to return it as `01/08/2016` way.


Any help would be appreciated.

Answer

You can get different datetime formats by using CONVERT() function,

SELECT   
Convert(varchar(15),TimeStampUTC,103 )   AS Date 
FROM table AS a   
WHERE a.TimeStampUTC > '$startDate' AND a.TimeStampUTC <='$endDate'

Here you can find the different date format codes: http://www.w3schools.com/sql/func_convert.asp