2

What I have here is very strange to me...

Select BARCODE, DATA 
from HISTORIA 
WHERE BARCODE='25405187' AND DATA = '2013-03-08 00:00:00.000'

Select BARCODE, DATA 
from HISTORIA 
WHERE BARCODE='25405187'

Simple right?

Now please look at returned data:

enter image description here

Ekhmmm... why... Why... WHYYYY? :) As you see proper dates are there, but comparison kills results.

I have SQL Server 2005 Express and the Management Studio Express

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
baron_bartek
  • 1,073
  • 2
  • 20
  • 39
  • 1
    What does `SELECT #2013-03-08 00:00:00.000#` return? I suspect SQL reads the data differently then what it displays, in particular in regards to months/days locations in the strings. – Oded Mar 10 '13 at 20:00
  • What data type have `DATA`? – Hamlet Hakobyan Mar 10 '13 at 20:00
  • What happens when you use `'2013-Mar-08 00:00:00.000'` (or the equivalent short month name)? – Oded Mar 10 '13 at 20:01
  • Hi. Thx for quick answear. Using this: 2013-Mar-08 00:00:00.000 returns error -> Msg 10054, Level 20, State 0, Line 0 A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) – baron_bartek Mar 10 '13 at 20:10
  • SELECT #2013-03-08 00:00:00.000# -> returns error -> Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '00'. – baron_bartek Mar 10 '13 at 20:10
  • @Oded `DATA` field is datetime type – baron_bartek Mar 10 '13 at 20:16
  • @oded if the Sql reads and display data diferently then maybe some kind of date conversion would help. Anyone has experience? – baron_bartek Mar 10 '13 at 20:23

2 Answers2

3

'2013-03-08 00:00:00.000' is not an unambiguous format when casting to datetime. It can either mean 3rd of August or 8th of March.

It depends upon your DATEFORMAT settings (which in turn depends upon the language of your login).

SET LANGUAGE english

SELECT CAST('2013-03-08 00:00:00.000' AS DATETIME) AS english

SET LANGUAGE british

SELECT CAST('2013-03-08 00:00:00.000' AS DATETIME) AS british

Returns

Changed language setting to us_english.
english
-----------------------
2013-03-08 00:00:00.000

Changed language setting to British.
british
-----------------------
2013-08-03 00:00:00.000

Use

WHERE BARCODE='25405187' AND DATA = '20130308 00:00:00.000'

Or more simply

WHERE BARCODE='25405187' AND DATA = '20130308'
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
1

Try this standard (ISO) date representation:

Select BARCODE, DATA 
from HISTORIA 
WHERE BARCODE='25405187' AND DATA = '2013-03-08T00:00:00.000'
msi77
  • 1,602
  • 1
  • 11
  • 10