Error making query with between in datetime field

Asked

Viewed 1,259 times

0

I’m trying to make a between but I don’t know how to proceed.

where cliente.id = (select usuario.idCliente from usuario where usuario.login = 'julio') and
(cast(os.dataHora as date) between  '2016-09-08' and '2013-10-29')

or

Where client.id = (select usuario.idCliente from usuario Where usuario.login = 'Julio') and (os.dataHora between '2016-09-08' and '2013-10-29')

Errorinserir a descrição da imagem aqui

sql-server

2 answers

1


Run in your database:

SET DATEFORMAT ymd;

Correct is you change the language of your database, so it will work in the default 'yyyy-mm-dd'.

If you want to use it the way it is, the format is:

yyyy-dd-mm hh:mi:ss.ms

To convert a String for date you can use any of the following commands:

cast('2016-11-07' as date);

or

convert(date, '2016-11-07');

  • I have another old application that will die in the future but I must keep this application running. Do you think doing this will go bad in the old application? The new one is not ready yet.

  • kkk. What a strange thing. Now I want to see in PHP. By the way I’ll have work. Obr.

  • How I do the conversion using Convert or cast?

  • As I could not use the set format ymd, I put the Convert being the best answer. Thanks anyway. I was trying since yesterday.

  • tested with Convert and cast. Excuse and.

1

Worked using CONVERT Where os.dataHora between CONVERT(datetime, '01-08-2016', 105) and CONVERT(datetime, '30-10-2016', 105)

  • 1

    Fabricio, you used the correct option, which is to use the Convert function to indicate to SQL Server what format the date is provided in. It is the most secure way and ensures that the application will run correctly, whatever the server’s LANGUAGE setting.

Browser other questions tagged sql-server

You are not signed in. Login or sign up in order to post.