5
You can convert dates into sweep for date. I’m having difficulties in performing searches between dates, due to this discrepancy in the database where I perform the query.
When I enter my line of code:
select *
from TAB_FATURAMENTO
where cd_cliente like '%'
and dt_item between '15/05/2017' and '31/05/2017';
The result of my search returns values of dates prior to my range.
16
If your column dt_item be as varchar in format dd/MM/yyyy:
SELECT *
FROM TAB_FATURAMENTO
WHERE cd_cliente LIKE '%'
AND CONVERT(DATE, dt_item, 103) BETWEEN CONVERT(DATE, '15/05/2017', 103) AND CONVERT(DATE, '31/05/2017', 103);
So you will turn the 3 parts into date and be able to perform the comparison correctly.
In the link below CAST and CONVERT (Transact-SQL) - Date and time styles from the documentation you can check the codes corresponding to the date conversions;
Conversions available:
╔═════════════════╦═══════════════════╦═══════════════════════════════════╦════════════════════════════════════════╗
║ Sem século (AA) ║ Com século (aaaa) ║ Standard ║ Entrada/saída (3) ║
╠═════════════════╬═══════════════════╬═══════════════════════════════════╬════════════════════════════════════════╣
║ - ║ 0 ou 100 (1,2) ║ Padrão para datetime e ║ mês dd aaaa hh:miAM (ou PM) ║
║ ║ ║ smalldatetime ║ ║
╠═════════════════╬═══════════════════╬═══════════════════════════════════╬════════════════════════════════════════╣
║ 1 ║ 101 ║ EUA ║ 1 = mm/dd/aa ║
║ ║ ║ ║ 101 = mm/dd/aaaa ║
╠═════════════════╬═══════════════════╬═══════════════════════════════════╬════════════════════════════════════════╣
║ 3 ║ 103 ║ Britânico/francês ║ 3 = dd/mm/aa ║
║ ║ ║ ║ 103 = dd/mm/aaaa ║
╠═════════════════╬═══════════════════╬═══════════════════════════════════╬════════════════════════════════════════╣
║ 4 ║ 104 ║ Alemão ║ 4 = dd.mm.aa ║
║ ║ ║ ║ 104 = dd.mm.aaaa ║
╠═════════════════╬═══════════════════╬═══════════════════════════════════╬════════════════════════════════════════╣
║ 5 ║ 105 ║ Italiano ║ 5 = dd-mm-aa ║
║ ║ ║ ║ 105 = dd-mm-aaaa ║
╠═════════════════╬═══════════════════╬═══════════════════════════════════╬════════════════════════════════════════╣
║ 6 ║ 106 (1) ║ - ║ 6 = dd mês aa ║
║ ║ ║ ║ 106 = dd mês aaaa ║
╠═════════════════╬═══════════════════╬═══════════════════════════════════╬════════════════════════════════════════╣
║ 7 ║ 107 (1) ║ - ║ 7 = Mês dd, aa ║
║ ║ ║ ║ 107 = Mês dd, aaaa ║
╠═════════════════╬═══════════════════╬═══════════════════════════════════╬════════════════════════════════════════╣
║ 8 ║ 108 ║ - ║ hh:mi:ss ║
╠═════════════════╬═══════════════════╬═══════════════════════════════════╬════════════════════════════════════════╣
║ - ║ 9 ou 109 ║ Padrão + milissegundos ║ mês dd aaaa hh:mi:ss:mmmAM (ou PM) ║
╠═════════════════╬═══════════════════╬═══════════════════════════════════╬════════════════════════════════════════╣
║ 10 ║ 110 ║ EUA ║ 10 = mm-dd-aa ║
║ ║ ║ ║ 110 = mm-dd-aaaa ║
╠═════════════════╬═══════════════════╬═══════════════════════════════════╬════════════════════════════════════════╣
║ 11 ║ 111 ║ JAPÃO ║ 11 = aa/mm/dd ║
║ ║ ║ ║ 111 = aaaa/mm/dd ║
╠═════════════════╬═══════════════════╬═══════════════════════════════════╬════════════════════════════════════════╣
║ 12 ║ 112 ║ ISO ║ 12 = aammdd ║
║ ║ ║ ║ 112 = aaaammdd ║
╠═════════════════╬═══════════════════╬═══════════════════════════════════╬════════════════════════════════════════╣
║ - ║ 13 ou 113 ║ Padrão Europa + milissegundos ║ dd mês aaaa hh:mi:ss:mmm (24h) ║
╠═════════════════╬═══════════════════╬═══════════════════════════════════╬════════════════════════════════════════╣
║ 14 ║ 114 ║ - ║ hh:mi:ss:mmm(24h) ║
╠═════════════════╬═══════════════════╬═══════════════════════════════════╬════════════════════════════════════════╣
║ - ║ 20 or 120 (2) ║ ODBC canônico ║ aaaa-mm-dd hh:mi:ss(24h) ║
╠═════════════════╬═══════════════════╬═══════════════════════════════════╬════════════════════════════════════════╣
║ - ║ 21 or 121 (2) ║ ODBC canônico (com milissegundos) ║ aaaa-mm-dd hh:mi:ss(24h) ║
║ ║ ║ padrão para hora, data, datetime2 ║ ║
║ ║ ║ e datetimeoffset ║ ║
╠═════════════════╬═══════════════════╬═══════════════════════════════════╬════════════════════════════════════════╣
║ - ║ 126 (4) ║ ISO8601 ║ aaaa-mm-ddThh:mi:ss.mmm (sem espaços) ║
║ ║ ║ ║ ║
║ ║ ║ ║ Observação: Quando o valor de ║
║ ║ ║ ║ milissegundos (mmm) for 0, o valor de ║
║ ║ ║ ║ milissegundos não é exibido. Por ║
║ ║ ║ ║ exemplo, o valor ║
║ ║ ║ ║ '2012-11-07T18:26:20.000' é exibido ║
║ ║ ║ ║ como '2012-11-07T18:26:20'. ║
╠═════════════════╬═══════════════════╬═══════════════════════════════════╬════════════════════════════════════════╣
║ - ║ 127(6, 7) ║ ISO8601 com fuso horário Z. ║ aaaa-mm-ddThh:mi:ss.mmmZ (sem espaços) ║
║ ║ ║ ║ ║
║ ║ ║ ║ Observação: Quando o valor de ║
║ ║ ║ ║ milissegundos (mmm) for 0, o valor de ║
║ ║ ║ ║ milissegundos não é exibido. Por ║
║ ║ ║ ║ exemplo, o valor ║
║ ║ ║ ║ '2012-11-07T18:26:20.000' é exibido ║
║ ║ ║ ║ como '2012-11-07T18:26:20'. ║
╠═════════════════╬═══════════════════╬═══════════════════════════════════╬════════════════════════════════════════╣
║ - ║ 130 (1, 2) ║ Hijri (5) ║ dd mmm aaaa hh:mi:ss:mmmAM ║
║ ║ ║ ║ ║
║ ║ ║ ║ Neste estilo, mon representa uma ║
║ ║ ║ ║ representação unicode Hijri de vários ║
║ ║ ║ ║ tokens do nome completo do mês. Este ║
║ ║ ║ ║ valor não será renderizado corretamente║
║ ║ ║ ║ em uma instalação US padrão do SSMS. ║
╠═════════════════╬═══════════════════╬═══════════════════════════════════╬════════════════════════════════════════╣
║ - ║ 131 (2) ║ Hijri (5) ║ dd/mm/aaaa hh:mi:ss:mmmAM ║
╚═════════════════╩═══════════════════╩═══════════════════════════════════╩════════════════════════════════════════╝
4
Use the CONVERT
select *
from TAB_FATURAMENTO
where cd_cliente like '%'
and dt_item between CONVERT(varchar(10), '15/05/2017', 103) and CONVERT(varchar(10), '31/05/2017', 103);
The value 103 defines that the date string is in format dd/mm/yyyy.
I used your select, @George Wurthmann. However, the following error occurred: ISC ERROR CODE:335544569

ISC ERROR MESSAGE:
Dynamic SQL Error
SQL error code = -104
Token unknown - line 4, column 29
varchar

Statement: select *
from TAB_FATURAMENTO
where cd_cliente like '%'
and dt_item between CONVERT(varchar, '15/05/2017', 103) and CONVERT(varchar, '31/05/2017', 103)
@Tonyanderson, replace the Convert varchar for char(10), if the answer is correct. Detail: You are sure that dt_item is varchar??
I made a small edition in reply also changing varchar for varchar(10).
0
If you are in SQL and considering that the dt_item field is of date type, to perform the query you can do so:
select *
from TAB_FATURAMENTO
where cd_cliente like '%'
and dt_item between '2017-05-15' and '2017-05-31';
Abs.
His dt_item field is not date time, which generated his doubt. The select you placed is exactly the same as his.
In my select I use the default SQL Server which is yyyy-MM-dd and in these cases the difference with the format it used.
Oh yes, but still a cast is needed. Unless his column is in date time and he doesn’t know. hehe
0
select * from TAB_FATURAMENTO
where cd_cliente like '%'
and dt_item between LEFT(TAB_FATURAMENTO.data, 10) >='2017-05-15' And
LEFT(TAB_FATURAMENTO.data, 10) <='31/05/2017'
I think this is the right way to convert and compare try
0
If the column DT_ITEM is of type date (DATE or DATETIME) and you want to convert string in date use the following:
select *
from TAB_FATURAMENTO
where cd_cliente like '%'
and dt_item between convert(DATETIME, '2017-05-01', 103) and convert(DATETIME, '2017-05-31', 102)
Your query returns the error The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
-1
After thorough analysis of the bank tables, we discovered (my manager and I), that the problem is in the field itself, the same not recognizing the means of conversion.
So the way we found to make such comparisons between the dates was this:
select *
from TAB_FATURAMENTO
where (substring(dt_item from 1 for 2) between substring('15/05/2017' from 1 for 2) and substring('31/05/2017' from 1 for 2) )
and (substring(dt_item from 4 for 2) between substring('15/05/2017' from 4 for 2) and substring('31/05/2017' from 4 for 2) )
and (substring(dt_item from 7 for 4) between substring('15/05/2017' from 7 for 4) and substring('31/05/2017' from 7 for 4) )
order by dt_item`
It’s not visually very beautiful, but it’s what solved our problem.
From now on I thank you all for your help.
This has a very bad performance. The answers with more votes are better alternatives. But better than all this would be changing the column to save dates instead of texts.
@Tonyanderson: What is the database manager: Mariadb? Oracle Database? other? // The construction you posted as a solution is not accepted in SQL Server.
@Josédiz, it’s Firebird. The whole problem is that this database has already been passed to us like this, and there’s a program in Delphi associated with it. We have already tried to change some other tables, but we have had many problems with this.
@Tonyanderson the given solutions did not work so in the tags is SQL server and not Firebird.
I’ve made the correction, I’m sorry for that, I didn’t take the time to post the question.
@Tonyanderson: Regardless of which database manager, the solution you posted will only be correct if the month and year values are the same. // If the value of dt_item is 12/25/2016 and the period is 12/20/2016 to 1/10/2017, the value of dt_item is in the period, right? After all the day 25/12/2016 is between 20/12/2016 and 10/1/2017. However, the code you posted will indicate that not. // Noted that substring('15/05/2017' from 1 for 2) is the same as '15'? // TIP: create another topic, with content identical to this, but informing Firebird as manager.
Thanks for the suggestion, @Josédiz, I’ll do it.
Browser other questions tagged sql database sql-server query
You are not signed in. Login or sign up in order to post.
If I understand correctly, the column
dt_itemIt’s like varchar, right?– Oralista de Sistemas
If your date is like varchar, first you have to convert all to the date format (xx/xx/xxxx -> xxxx-xx-xx) and then convert the field type to date.
– Lucas Thibau Paulino
That’s right @Renan, my field
dt_itemit’s like varchar... and because of that, I can’t perform my search properly.– Tony Anderson
@Tonyanderson, if the dt_item column is as varchar, what is the storage format: dd/mm/yyyy? yyyymmdd? other? // What you want with the restriction {WHERE cd_client like '%'} ?
– José Diz
@Josédiz, the values are stored as
dd/mm/aaaa// {WHERE cd_client like '%'}, searches all clients.– Tony Anderson
@Tonyanderson: As the date is stored in the character column and in the dd/mm/yyyy format, it is necessary that, in the WHERE clause, the contents of the column be converted to another format that can be directly compared to date intervals. One option is to convert to data type
datebefore comparing with the limits, also asdate. // If it’s to list all clients, I suggest you remove the restriction thencd_cliente like '%'of the WHERE clause.– José Diz
@Tonyanderson It is not a good idea to change the question so radically after receiving so many answers. If you now need something for Firebird, open a new question. It would be unfair to invalidate everyone’s answer because you have now decided to change the question tag.
– Jéf Bueno
@jbueno, as you suggested, I asked a new question:
Como converter datas em varchar para date no SQL 'Firebird'?.– Tony Anderson
Has any response helped solve the problem and can address similar questions from other users? If so, make sure to mark the answer as accepted. To do this just click on the left side of it (below the indicator of up and down votes).
– Sorack