1

I'm trying to make a program that will do some queries on a SQL Server, for some reason the code is having strange behavior, since it will create different date values on 2 of my computers.

This is the part of code behaving weird.

Query += " dbo.MensajesProcesados.SentDateTime BETWEEN CONVERT(VARCHAR, '" + FechaInicio.Value.ToUniversalTime().ToShortDateString() + "', 103) AND CONVERT(VARCHAR, '" + FechaFin.Value.ToUniversalTime().ToShortDateString() + "', 103)";

in 1st computer(working) gives me:

BETWEEN CONVERT(VARCHAR, '5/18/2015', 103) AND CONVERT(VARCHAR, '5/19/2015', 103)

and the second computer which is not working:

BETWEEN CONVERT(VARCHAR, '17/05/2015',103) AND CONVERT(VARCHAR, '19/05/2015',103)

This one is not working and will output me the following error

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

Please, any help would be great!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
pato.llaguno
  • 741
  • 4
  • 20
  • 4
    If you stop concatenating your SQL and instead use parameters, these sorts of problems won't exist. And, as a bonus feature, your code won't be wide open for SQL injection. – Ken White May 18 '15 at 22:22
  • You are showing 2 different date formats in the question, but nowhere do you specify to the converter what format to use (in the sample code provided). How should the convert function know whether the first number represents the month or day of the datetime? You can change the format used by the converter, or the format used by the datetime (for string output), but the correct solution is to do SQL properly with params. SQL libraries can be very useful and offer much more than a simple means to execute a string SQL statement. – gnomed May 18 '15 at 22:24
  • @KenWhite the problem is that i don't know another way to do what i need if i don't use concatenating since i have various search parameters and if the text boxes have something in it it will use it in the where clause, otherwise it wont, how could you achieve this with params? – pato.llaguno May 19 '15 at 21:33
  • 1
    You create the parameters as needed with `SQLCommand.Parameters.AddWithValue`. See these [search results](http://stackoverflow.com/search?q=[c%23]+parameterized+query) which should turn up some examples of doing so. – Ken White May 19 '15 at 22:07

2 Answers2

6

Provide Sql Server with culture neutral format 'yyyyMMdd':

Query += " dbo.MensajesProcesados.SentDateTime
BETWEEN '" + FechaInicio.Value.ToString("yyyyMMdd") + "' 
AND '" + FechaFin.Value.ToString("yyyyMMdd") + "'";
Giorgi Nakeuri
  • 35,155
  • 8
  • 47
  • 75
  • and if i wanted to add time, i guessed something like this, didnt work tho `FechaInicio.Value.ToUniversalTime().ToString("yyyyMMdd") + " " +TimeInicio.Value.ToUniversalTime().ToString("HHmmss")` – pato.llaguno May 19 '15 at 15:22
  • @pato.llaguno, formatiing support that: ToString("yyyyMMdd hh:mm:ss") – Giorgi Nakeuri May 19 '15 at 15:26
  • the thing is that the time i sin another textbox, i couldnt find a way to select it in the same text box – pato.llaguno May 19 '15 at 17:29
4

This has to do with the culture/language of the SQL Server. See here: SQL Server Datetime issues. American vs. British?

set language us_english
declare @d datetime = '1929/12/18'

You could follow the solution in the question and change the language manually, or you can build the date by inputting the month and year so there's no room for translation, see some of the answers here: Create a date with T-SQL

DECLARE @day int, @month int, @year int
SELECT @day = 4, @month = 3, @year = 2011
SELECT dateadd(mm, (@year - 1900) * 12 + @month - 1 , @day - 1)

Try CONVERT instead of CAST.

CONVERT allows a third parameter indicating the date format.

Community
  • 1
  • 1
rlb.usa
  • 14,942
  • 16
  • 80
  • 128
  • Would DateTimeInstance.ToString("o") (round-trip format) be a reliable way to create a non-ambiguous string representation of a DateTime? – Simon Campbell May 18 '15 at 22:12
  • @SimonCampbell Honestly not sure, because now you're factoring in your programming language's culture settings, too. But you could always be more explicit for safety , ex, in c# `myDateTime.ToString("yyyy-MM-dd HH:mm:ss");` – rlb.usa May 18 '15 at 22:15
  • 1
    If he changes the language setting in the RDBMS to address the issue, then the case that was formerly working will then break. – gnomed May 18 '15 at 22:30