Why if I set "default language" in spanish the GETDATE() still formating the date in english?

Question

why if I do this on my SQL-Server 2008:

EXEC sp_configure 'default language', 5
reconfigure

Where the date format is dd/mm/yyyy:

select * from sys.syslanguages where langid = 5

returns

dateformat
----------
dmy

So if I do

select GETDATE()

I'm waiting for something like:

(no column name)
----------------
31/08/2013 13:20:44.590

but I get:

(no column name)
----------------
2013-08-31 13:20:44.590

I'm using SQL-Server 2008 Express compatibility mode 100

ADDED:

My real problem is that I need to pass to Stored Procedures dates in dd/mm/yyyy hh:mm to DATETIME variables, but the parser is still waiting for yyyy-mm-dd although I change the default language.

Thank you

Answer 1

The following is going to be rendered by Management Studio, irrespective of server settings:

SELECT GETDATE();

This is returning a datetime value to the client application, NOT A STRING. If you want a string, you can explicitly convert to a specific style:

As for the input to your stored procedures, please, please, please pass proper datetime parameters and not strings. There is no reason to allow users to enter freetext like 6/9/2013 when you really don't know if they meant September 6th or June 9th. The safe formats to pass to SQL Server are:

YYYYMMDD HH:MM:SS.nnn
YYYY-MM-DDTHH:MM:SS.nnn

Anything else can be misinterpreted. Which is why you shouldn't handle these as strings anywhere except at the final step of presentation / display.

Answer 2

you can use like this

Select CONVERT(varchar(100), GETDATE(),103)+' '
+CONVERT(varchar(100), GETDATE(),108) as now

the result is