0

I am using a database with Microsoft SQL Server Management Studio.

I want to perform a very simple SQL request:

SELECT 
   [Date], [Price]
FROM 
   [mydatabase].[dbo].[Table]
WHERE 
   [Date] = CAST('2011-06-17 06:00:00' AS smalldatetime)

and I get the following error message:

The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value

Of course Date type is smalldatetime, but still having problems.

Can anybody help me with this?

Table looks like Datum is smalldatetime

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
UDE_Student
  • 349
  • 1
  • 3
  • 19
  • What's the type of `[Date]` column and what if you cast to datetime instead of smalldatetime? – Rahul Jun 07 '14 at 16:50
  • Type is like you see smalldatetime. If i cast it as date then i have no results. – UDE_Student Jun 07 '14 at 16:51
  • Well then directly compare like `Where [Date]='2011-06-17 06:00:00'`. No need of casting I believe. – Rahul Jun 07 '14 at 16:53
  • 1
    This ISO date format is not unambiguous for the legacy date datatypes and is interpreted according to the date settings of the login (derived from its default language). Try removing the dashes. `'20110617 06:00:00'` – Martin Smith Jun 07 '14 at 16:55
  • @MartinSmith Thanks, i changed it in the properties of the server(under standard language, it was german now english), restarted the server service but still same problem. – UDE_Student Jun 07 '14 at 16:59
  • And what is `Datum` field then (is it `Date` from your query)? – potashin Jun 07 '14 at 16:59
  • It is the default language of **the SQL Server login** not the server (set in management studio by expanding the logins node and finding the relevant one) but best to use an unambiguous format for the string literal as suggested above. – Martin Smith Jun 07 '14 at 17:00

1 Answers1

0

try casting to datetime instead of smalldatetime and change date format

SELECT [Date]
      ,[Price]
  FROM [mydatabase].[dbo].[Table]
  Where [Date]=CAST('06/17/2011 06:00:00' AS DATETIME))
Ashkan Mobayen Khiabani
  • 33,575
  • 33
  • 102
  • 171