CODEWITHSUNDEEP
c#sqlsql-server
pato.llaguno
pato.llaguno

Reputation: 741

Datetime acting weird on different computers

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!

Upvotes: 1

Views: 732

Answers (2)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

Provide Sql Server with culture neutral format 'yyyyMMdd':

Query += " dbo.MensajesProcesados.SentDateTime
BETWEEN '" + FechaInicio.Value.ToString("yyyyMMdd") + "' 
AND '" + FechaFin.Value.ToString("yyyyMMdd") + "'";

Upvotes: 6

rlb.usa
rlb.usa

Reputation: 15043

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.

Upvotes: 4

Related Questions