Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've dealt with MS SQL Server datetime types for a long time but never thought why the following is happening:

  1. I query a table that contains a smalldatetime column. This smalldatetime is always returned in the format yyyy-MM-dd hh:mm:ss
  2. Now I write a different query on which I want to apply a smalldatetime filter in the WHERE clause, something like WHERE TimeStamp >= 'yyyy-MM-dd hh:mm:ss'
  3. SQL Server retrieves an error and tells me that was not possible to convert that nvarchar to a valid smalldatetime

It appears that it only works if I change the specified format and I write it using the european format, like WHERE TimeStamp >= 'dd-MM-yyyy hh:mm:ss'. Why is SQL Server showing me the dates in a format that is not covertable or valid when applied back to itself? I don't have any problem in changing the date format when writing queries, but I want to play with these dates at an application level (Java-JDBC app) and I don't want to be applying date format changes all the time... Could anyone explain me why this is happening and if there is any way to solve it at a DB level? Thanks!!

Edit: Please see the screenshot of the error in Management Studio below. enter image description here

share|improve this question
    
I suspect that either your column is not smalldatetime, or the string you put in the where clause is not a valid date. –  Aaron Bertrand Apr 22 at 13:27
3  
Did you try using the language-independent date format YYYYMMDD? –  Jon Seigel Apr 22 at 13:35
    
The column is smalldatetime, as I've checked it several times, and the string is a valid date. If I do this query SELECT * FROM Table WHERE TimeStamp >= '22-04-2014 10:00:00' it works. It doesn't work though if I run SELECT * FROM Table WHERE TimeStamp >= '2014-04-22 10:00:00' –  Hauri Apr 22 at 13:37
    
Well I can't reproduce that problem. For me 22-04-2014 breaks because that's not a valid date in my locale. 2014-04-22 is a valid date in ALL locales, so I don't know how you're going to convince us that you've told us the whole story here. Can you create a repro on SQLfiddle that demonstrates this failure? –  Aaron Bertrand Apr 22 at 13:38
    
Another hint that you haven't told us the whole story: your error message mentions nvarchar but there's no way that came from the code you explicitly typed in the comment. Are you passing the parameter as nvarchar instead of smalldatetime? How exactly is SQL Server determining that this string is Unicode? –  Aaron Bertrand Apr 22 at 13:54

1 Answer 1

up vote 8 down vote accepted

The only truly safe formats for DATETIME/SMALLDATETIME in SQL Server are:

YYYYMMDD
YYYY-MM-DDThh:nn:ss[.mmmmmmm]
----------^ yes, that T is important!

Anything else is subject to incorrect interpretation by SQL Server, Windows, the provider, the application code, end users, etc. For example, the following always breaks:*

SET LANGUAGE FRENCH;
SELECT CONVERT(DATETIME, '2013-11-13');

Result:

Le paramètre de langue est passé à Français.
Msg 242, Level 16, State 3, Line 2
La conversion d'un type de données varchar en type de données datetime a créé une valeur hors limites.

Just changing the language (which any of your user sessions can do) forced SQL Server to interpret that as YYYY-DD-MM instead of YYYY-MM-DD. Similar things can happen with setting like DATEFORMAT. But these settings are literally ignored when using the above two formats.

Always, always, always use one of the above two formats. If you are passing a variable as a string, stop doing that. If you can't, check to make sure it passes ISDATE() first. If you are letting people type any date string into a form field, stop doing that, too. Use a date-picker or calendar control and dictate the format of the string before you pass it to SQL Server. Well, depending on the language, just keep it as a datetime value and don't convert it to a string at all.

I can't reproduce your problem at all, btw...

Please read this post:

Bad Habits to Kick : Mis-handling date/range queries

There is an exception: SELECT CONVERT(DATE, 'yyyy-mm-dd'); will not break. But I err on the side of consistency rather than using a format only in the one place where I know it doesn't break, and having to use a safer format everywhere else.

share|improve this answer
3  
@datagod that's how SQL Server recognizes it as a specific ISO format that it shouldn't try to interpret in any other way. –  Aaron Bertrand Apr 22 at 13:50
1  
@Hauri I don't know what to tell you. This error won't happen with these formats unless something is changing the string format on you, or your columns are not the types you think they are. Have you captured the exact queries being passed with Profiler? That might be a step. –  Aaron Bertrand Apr 22 at 14:17
2  
@Hauri then you have issues in your java code. My guess would be that you build your query concatenating strings together instead of building a query that uses parameters. –  Mikael Eriksson Apr 22 at 15:37
2  
@Hauri because your language or date format settings are interpreting that as yyyy-dd-mm. Add the T, or manually change it (e.g. SET DATEFORMAT YMD). I wonder which of those is easier. –  Aaron Bertrand Apr 22 at 15:47
2  
@Hauri because the dateformat you use for the string literal does not work with the regional settings you have in your session against SQL server. –  Mikael Eriksson Apr 22 at 15:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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