Ndtyjky

Put this piece of code in query analyzer and hit F5.

declare @aaa datetime

set @aaa='01/12/2011'

set dateformat dmy

select month(@aaa)

It gives you 1. Hit F5 again, it gives you 12. Why?

This is because your line set dateformat dmy is half way through the script.

This is switching your date format after the initial assignment for the first 'F5' but then this is persisted for the second 'F5'

If you make it the first line it should be consistent and return 12 each time

To be clear - the set dateformat dmy makes a difference for the set @aaa='01/12/2011' line as it affects the way that the string '01/12/2011' is parsed to a datetime.

On the first run, the @aaa will actually contain the date '12-Jan-2011', whereas after you set dateformat dmy it will contain the date '01-Dec-2011'.

Because the first time you ran the code, the date was stored internally as January 12th. The set setting sticks to your session, then you ran it again so you redeclared the variable, and this time it was interpreted as December 1st. The set setting doesn't affect datepart functions like MONTH() because the date has already been interpreted and isn't stored internally the way you've typed it.

Instead of declaring date literals with regional and ambiguous formatting, you should always use safe, unambiguous formats. For datetime/smalldatetime:

yyyymmdd

yyyy-mm-ddThh:mm:ss

For date:

yyyy-mm-dd

If you want to display dates in these friendly but confusing formats, by all means do that at the presentation layer. But don't try to confuse SQL Server with them. Please read this blog post and this article by Tibor Karaszi.