How to do a select in date format?

Question

How to do a select in date format?

Navigation

#1 by (2 votes)
#2 by (0 votes)

2

How do I perform a select using dates in SQL server 2008 ? When I do a search with this select:

select * from NOME_TABELA where DATA_FISCAL between '2016-05-01' and '2016-05-11'

The result is 12 records including one record has the date information as "2016-05-11 00:00:00.000" .

When I select:

" select * from NOME_TABELA where DATA_FISCAL like '2016-05-11 00:00:00.000' "

or

" select * from NOME_TABELA where DATA_FISCAL = '2016-05-11 00:00:00.000' "

SQL does not find any records.

Another question that intrigues me is that when I give an insert in this table, this error appears. "Failed to convert string date and / or time."

Does anyone know the reason for this error? and how to solve?

sql sql-server datetime

asked by anonymous 13.05.2016 / 17:04

2 answers

Decimal house - JQuery Query with date in variable in MySQL returning empty

score 2 · Answer 1

Try this: First make a select to check whether the format is date or datetime. Then use your parameters this way -

//Use o seu formato de data
SELECT * FROM NOME_TABELA 
WHERE DATA_FISCAL BETWEEN #07/04/1996# AND #07/09/1996#;

Font

score 0 · Answer 2

Responding , the insert error occurs because you try to enter the date in a format not expected by the database, and the unexpected result in select > is for the same reason.

Explaining: The format of the date as a string accepted by SQL Server, by default, depends on the language of the user you are using to connect to the database, which is defined by default in accordance with the installation language of SQL Server itself, which is defined by default according to the operating system language.

But the user language can also be overridden by session settings , and the date format itself accepts as a string can also be overwritten by session settings.

When you think that you have found the correct format and start using it for all your code, eventually this code will run in a slightly different environment and will go to give error, or worse: it will happen to bring incorrect results or save incorrect information to the bank.

The good practice is to use parameters to report dates or any other value in SQL commands, this avoids this and other potential problems.

If you need to report the date as a string, without using parameters, report it in one of two universally supported by SQL Server formats regardless of settings. Namely:

yyymmdd for date,
or yyyy-mm-ddThh: mm: ss [.mmm] for date and time.

This will serve both insert and select or any other command.

Note: these two formats are ISO standard.

In your case, the select telling date and time would look like this:

select * from NOME_TABELA where DATA_FISCAL = '2016-05-11T00:00:00.000'