I've dealt with MS SQL Server datetime types for a long time but never thought why the following is happening:
- I query a table that contains a smalldatetime column. This smalldatetime is always returned in the format
yyyy-MM-dd hh:mm:ss
- 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'
- 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.