change date format

Question

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.

@SankarRaj Are you suggest to use this without convert day,month and year separately ? — Zusee Weekin, Aug 01 '16 at 04:34
If it's a date field, no need to parse out the components, you can convert to a string with one of many formats. — Hart CO, Aug 01 '16 at 04:35
Here's a handy list of date format numbers: https://www.mssqltips.com/sqlservertip/1145/date-and-time-conversions-using-sql-server/ — Hart CO, Aug 01 '16 at 04:39
[Sql server does not store date display format.](http://stackoverflow.com/questions/30032915/how-to-cast-the-datetime-to-time/30033028#30033028) — Zohar Peled, Aug 01 '16 at 04:49

score 1 · Accepted Answer · answered Aug 01 '16 at 04:41

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

score 0 · Answer 2 · answered Aug 01 '16 at 04:36

0

you can use DATE_FORMAT()

DATE_FORMAT(yourdate,'%d-%m-%Y')

for your reference: sql date formats

answered Aug 01 '16 at 04:36

Kate Sams

51
4

SQL Server has no `DATE_FORMAT()` function, SQL Server 2012 and newer have `FORMAT()`, but the way you input the date format is different than what you have here. – Hart CO Aug 01 '16 at 04:38

score 0 · Answer 3 · answered Aug 01 '16 at 04:37

0

declare @TimeStampUTC datetime='2016-08-01 03:00:00.000' 
        select CONVERT(VARCHAR(10),convert(date,@TimeStampUTC),103)

answered Aug 01 '16 at 04:37

brahmareddy

121
8

Ket · Answer 4 · 2016-08-01T04:43:07.480

0

Assuming you are looking for DD/MM/YYYY, you can try this:

select REPLACE(CONVERT(varchar(15), a.TimeStampUTC, 105),'-','/')

edited Aug 01 '16 at 04:43

answered Aug 01 '16 at 04:42

Ket

83
8

1

No need to replace dashes with slashes, can just use format code `103`. Also 103 and 105 both have day first, not month. – Hart CO Aug 01 '16 at 04:43
Yes, that works too ! Thank you Hart, i learnt something new as well :) – Ket Aug 01 '16 at 04:44

change date format

4 Answers4