SQL Server Date conversion issue : yyyy-mm-dd should become as dd-mm-yyyy through query

Question

I have a date column where the date format is

2016-07-24

in SQL Server now. Is there any possible way to retrieve the date format as

24-07-2016

through a select statement? and My presently I am using this query

Convert(varchar(10),CONVERT(date,event_enddate,105),126) as "end"

What am I missing here?

A date column has no Format! If your column of date type char, you should convert it — Jens, Jul 04 '16 at 06:50
Target style `dd-mm-yyyy` is time style 105. So I guess you should place 126 inside and 105 outside — Mincong Huang, Jul 04 '16 at 06:53
Possible duplicate of [How to get a particular date format ('dd-MMM-yyyy') in SELECT query SQL Server 2008 R2](http://stackoverflow.com/questions/18202260/how-to-get-a-particular-date-format-dd-mmm-yyyy-in-select-query-sql-server-2) — Himanshu Tyagi, Jul 04 '16 at 06:53
here something... `SELECT convert(varchar, getdate(), 105) — dd-mm-yyyy` https://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/ — J. Zend, Jul 04 '16 at 06:55
don't do it in SQL query. You are only returning a string to your application. Perform the formatting in your front end application — Squirrel, Jul 04 '16 at 06:56
@Keynes You should replace double quote `"end"` to `[end]`, I think. — Blank, Jul 04 '16 at 06:58
CONVERT(date,event_enddate,105),105) as "end" . This i tried and received desired output — Deepak Keynes, Jul 04 '16 at 07:10

Arulkumar · Accepted Answer · 2016-07-04T07:02:21.397

2

SELECT REPLACE(CONVERT(VARCHAR(10), CAST(event_enddate AS DATE),103), '/', '-') AS [end]

or

SELECT CONVERT(VARCHAR(10), CAST(event_enddate AS DATE), 105) AS [end]

will return your expected format.

More over add square bracket [ ] instead of double quotes " " for your alias name

Sample execution with GETDATE(): Reference

SELECT CONVERT(VARCHAR(10), CAST(GETDATE() AS DATE),105) AS [end]

Output:

04-07-2016

edited Jul 04 '16 at 07:02

answered Jul 04 '16 at 06:54

Arulkumar

a bit overkill. Actually not really a bit :) – Squirrel Jul 04 '16 at 06:55
MSDN provides functions to converts date values directly. I don't think you need REPLACE. – Mincong Huang Jul 04 '16 at 06:55
@MincongHuang: Yeah, I missed. Thanks for notify. – Arulkumar Jul 04 '16 at 06:58
@Arulkumar, I get error like this, Incorrect syntax near the keyword 'AS'. – Deepak Keynes Jul 04 '16 at 07:12
@Keynes: It returns the proper data. Check the [fiddler](http://rextester.com/GHVP67275) – Arulkumar Jul 04 '16 at 07:21
@Arulkumar, yes buddy, it has worked fine now, and i tried another way that also worked :) Convert(varchar(10),CONVERT(date,'columnname',105),105) as "end"; – Deepak Keynes Jul 04 '16 at 07:36
@Keynes: In the above query `CONVERT(date,'columnname',105)` is not needed. Simply replace with `columnname` is enough, as you mentioned in the comments, the particular column already is in `datetime`. – Arulkumar Jul 04 '16 at 07:38

score 1 · Answer 2 · answered Jul 04 '16 at 06:54

1

Were you looking for the below?

SELECT convert(varchar, getdate(), 105)

For other formats, check this.

answered Jul 04 '16 at 06:54

CONVERT(date,event_enddate,105),105) as "end", should i have to do like this? – Deepak Keynes Jul 04 '16 at 07:03

2 Answers2