smalldatetime

Question

Post reply

smalldatetime

SQLAssAS

SSCertifiable

Points: 7246
More actions
April 11, 2014 at 7:40 am

#285679

Why has smalldatetime changed from 2005 to SQL 2008\2012? i dont understand the logic behind it?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Mike John SSCertifiable Points: 7216 More actions · Answer 1

That is interesting, can you elaborate on what has changed please? I have not found anything documented.

Many Thanks

Mike John

SQLAssAS SSCertifiable Points: 7246 More actions · Answer 2

I have done two migrations recently where it has caused me an issue.

I restored a database from a SQL 2005 environment, in one of the tables, a field is smalldatetime and displays DD/MM/YYYY

In the SQL 2012 environment, the same field displays YYYY-MM-DD

Not sure why?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Regional settings on the server are probably different.

Datetime and smalldatetime don't have intrinsic formats. They're stored kinda as floating point values.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLAssAS SSCertifiable Points: 7246 More actions · Answer 4

Gila,

Any idea what settings might cause this? Collation is the same, language is the same on the instance, same on the databases.

Its causing quite a few issues as you would expect and I'm at a bit of a loss with it.

If i update a table:

Update dbo.Table1 set Datetime = '05/12/2013'

then select from the table, it displays as 2013-05-12 00:00:00

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

I believe that the default date settings are based on the server's regional settings.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

I would recommend that you use an unambiguous date format in your code, such as '20130512' for 12th May 2013, if you have the option.

You can set the format using SET DATEFORMAT and you can check the current format like this:

SELECT date_format

FROM sys.dm_exec_sessions

WHERE session_id = @@spid

John

SQLAssAS SSCertifiable Points: 7246 More actions · Answer 7

John Mitchell-245523 (4/14/2014)
I would recommend that you use an unambiguous date format in your code, such as '20130512' for 12th May 2013, if you have the option.
You can set the format using SET DATEFORMAT and you can check the current format like this:
SELECT date_format
FROM sys.dm_exec_sessions
WHERE session_id = @@spid
John

Thanks John,

this has highlighted that my date_format is MDY where as the service account on the other server is DMY, which is why it worked.

I now need to look at setting all accounts to be DMY on the new server

tripleAxe SSCertifiable Points: 5706 More actions · Answer 8

It might also be worth checking the default language setting of the login being used. This has caught me out before. The login on the old server had default language set to "British English", I used sp_helprevlogin to script the login out and on the new server the default language was "English" as in "US English".