How to select in date format?

Asked

Viewed 17,826 times

3

How do I select using dates on 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 a 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 record.

Another question that intrigues me, is that when I give an input 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 resolve ?

sql sql-server datetime

  • Welcome to SOPT. Would have as [Edit] your question, and put the table structure?

2 answers

3

Try it like 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#;

Source

1

Responding, the error in the Insert is given because you try to inform the date in a format not expected by the bank, and the unexpected result in the select is for the same reason.

Explaining: the data format as a string accepted by SQL Server, by default, depends on the language of the user who is using to connect to the database, which is defined by default according to the SQL Server installation language itself, which is defined by default according to the language of the operating system.

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

When you think you have found the correct format and start using it throughout your code, eventually this code will run in a slightly different environment and will give error, or worse: will bring incorrect results or save in the database incorrect information.

Good practice is to use parameters to enter dates or any other value in the SQL commands, this avoids this and other potential problems.

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

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

This will serve so much to Insert how much to select or any other command.

Note: these two formats are ISO standard.

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

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

Browser other questions tagged sql sql-server datetime

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