-4

I have a select statement that when run on the live production server, returns mm-dd-yyyy regardless of what I do. this only happens though when selecting the date from an another aliased select. Simplified (as my sql is quite long):-

select name,age,dob 
from tblone as tbltemp1

in this select, dob always mm/dd/yyyy not dd/mm/yyyy

select dob from tbltemp1

Hope that explanation makes sense and if anyone can help?

Improve this question
10
  • 1
    Help with what?, what it is that you actually want?
    – Lamak
    Sep 16, 2014 at 16:13
  • Add some actual details, such as the structure (in T-SQL) of the table in question. You may want to clarify your actual question.
    – Hannah Vernon ♦
    Sep 16, 2014 at 16:15
  • 3
    I think in your haste to simplify your long code, you've obliterated our ability to understand the problem. It appears you have an issue with the displayed format of a column. Is this merely a presentation issue or is the data itself getting converted? Also, in your opening paragraph, you indicate it's a mm-dd-yyyy format but later on you indicate it's mm/dd/yyyy not dd/mm/yyyy format as expected.
    – billinkc
    Sep 16, 2014 at 16:15
  • Apologies - did not mean to confuse. The SQL is very long, so I have put it as a downloadable txt file here - the problem is line 54:- woldsnewmedia.com/sql.txt The SQL is used to populate the dataset in an SSRS file
    – user2462433
    Sep 16, 2014 at 16:39
  • There may be no need to post the entire query. What could be more useful is the type of the dob column, where you are executing the query and consuming the results, and possibly the locale settings of the system where you are executing the query as well.
    – Andriy M
    Sep 16, 2014 at 16:43

2 Answers 2

Reset to default
5

Right so the value is probably a date and is preserved as a date when it gets to SSRS. SSRS is applying its own formatting to a date.

If you don't like that format, apply the correct specifier.

etc

Improve this answer
-1

The date format is based on the language setting

SELECT @@language

If you query the sys.syslanguages table you can see the date format for each language. For example us_english is mdy and british is dmy.

The language can be changed using the [SET LANGUAGE][1] command.

SET LANGUAGE 'us_english'
Improve this answer
1
  • 2
    If dob is a date or datetime, SET LANGUAGE will have no impact on how the column is displayed unless you do CAST(dob AS varchar(…)). If it's a varchar already, SET LANGUAGE will have no impact either. Therefore, SET LANGUAGE alone won't help here.
    – Andriy M
    Sep 16, 2014 at 16:37

Not the answer you're looking for? Browse other questions tagged or ask your own question.