SQL Server 2014 Trouble with Convert Function

Question

For some reason the following queries return the same result, even though they shouldn't

SELECT CONVERT(DATE, GETDATE(),101)
SELECT CONVERT(DATE, GETDATE(),102)
SELECT CONVERT(DATE, GETDATE(),103)
SELECT CONVERT(DATE, GETDATE(),104)
SELECT CONVERT(DATE, GETDATE(),105)-- Ad Infinitum (Tested up to 140)

Result

2014-10-07

Just in case today's date is 7-Oct-2014

According to http://msdn.microsoft.com/es-es/library/ms187928.aspx there should only be 131 styles, IS the fact that I did NOT get an error with 132-140 related?

Technically I installed SQL Server 2014 in US-English and TRIED to change it using

set dateformat dmy

But I don't know whether it made a difference or not.

Did I screw up my install, should I repair it, uninstall and reinstall, format my machine altogether or is it not related.

First question posted, I believe I followed the rules, as I understood them, If I made a mistake, please do tell me.

Edit:

SELECT CAST (GETDATE() AS DATE)

Has the same result.

OK - but you've not said what you're **looking** for - what output do you **want** ?? Did you have a look at the new [`FORMAT`](http://msdn.microsoft.com/en-us/library/hh213505.aspx) function for SQL Server 2014? — marc_s, Oct 08 '14 at 05:14
I'm guessing you've misunderstood how dates (and datetimes) are stored in SQL Server (and pretty much any other programming language) - they don't have some kind of formatting information attached to them - they're just the date information. The *strings* that you're seeing are what happens when you ask a tool (such as management studio) to *show* you the date information. But don't confuse the string formatting that management studio is applying with the actual *data* that is stored in a `date` column or variable. — Damien_The_Unbeliever, Oct 08 '14 at 06:56
I'm sorry if I wasn't clear enough, my goal was to have ANY result other than the one I was getting, thanks for pointing out my mistake. — Jorge Luis Alvarado, Oct 08 '14 at 06:59

score 1 · Accepted Answer · answered Oct 08 '14 at 05:26

The style only matters when the output type is a literal one, that is, varchar or nvarchar. What you are trying to do is converting datetime into date (result does not depend on style) and then allow the client application to display it however it likes.

In the example below, however, the differences are apparent:

select convert(varchar, cast(getdate() as date), 101);
select convert(varchar, cast(getdate() as date), 102);
select convert(varchar, cast(getdate() as date), 103);
select convert(varchar, cast(getdate() as date), 104);

score 1 · Answer 2 · edited May 23 '17 at 11:57

1

You need to use VARCHAR instead of DATE in CONVERT function. Like below:

SELECT CONVERT(varchar, GETDATE(),101)
SELECT CONVERT(varchar, GETDATE(),102)
SELECT CONVERT(varchar, GETDATE(),103)
SELECT CONVERT(varchar, GETDATE(),104)
SELECT CONVERT(varchar, GETDATE(),105)

Also, check out the below link for the same: Convert Date format into DD/MMM/YYYY format in SQL Server

edited May 23 '17 at 11:57

Community

1
1

answered Oct 08 '14 at 05:33

Paresh J

2,401
3
24
31

SQL Server 2014 Trouble with Convert Function

2 Answers2