SQL Insert Date Mystery on 2012 from 2005

Question

I have migrated a 2005 db to 2012

There is a stored procedure that run to insert todays date into a table

IF NOT EXISTS(SELECT * FROM tblTime WHERE [Day] = DATEPART(day,GETDATE())
                                    AND [Month] = DATEPART(month,GETDATE())
                                    AND [Year]= DATEPART(year,GETDATE()) )
BEGIN
    INSERT INTO tblTime (Period,[Day],[Month],[Year],MonthPrefix)
        VALUES (CONVERT(VARCHAR(10), GETDATE(), 105),
                DATEPART(day,GETDATE()),
                DATEPART(month,GETDATE()),
                DATEPART(year,GETDATE()),
                CASE DATEPART(month,GETDATE()) 
                    WHEN 1 THEN 'JAN'
                    WHEN 2 THEN 'FEB'
                    WHEN 3 THEN 'MAR'
                    WHEN 4 THEN 'APR'
                    WHEN 5 THEN 'MAY'
                    WHEN 6 THEN 'JUN'
                    WHEN 7 THEN 'JUL'
                    WHEN 8 THEN 'AUG'
                    WHEN 9 THEN 'SEP'
                    WHEN 10 THEN 'OCT'
                    WHEN 11 THEN 'NOV'
                    WHEN 12 THEN 'DEC'
                    END
                )
END

When this is run on the new 2012 SQL box the date gets put in as

2015-12-06 00:00:00

But the same SP on the old 2005 would read

2015-06-12 00:00:00

I have checked that the 2 servers have the same language settings etc.

But is there something I have missed.

Many thanks for reading.

Answer 1

You can run

DBCC USEROPTIONS

on the two different SQL box and chek the dateformat. To set that value use

SET DATEFORMAT ymd

or any other combination like mdy, dmy, etc

Answer 2

I have got to change the language for each user.

There were set to English, but should of been British English for my instance.

Once changed, restarted the instance.

Thanks for the above post for pointing me in the correct direction.