Convert varchar to date in SQL

3

You can convert dates to varchar for date . I'm having difficulty performing cross-date searches because of this discrepancy in the database where I 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 me values from dates before my range.

    
asked by anonymous 01.06.2017 / 13:55

6 answers

14

If your dt_item column is as varchar in the 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'll turn the 3 parts into date and you'll be able to perform the comparison correctly.

At the link below CAST and CONVERT (Transact-SQL) time of the documentation you can check the codes corresponding to date conversions;

Available translations:

╔═════════════════╦═══════════════════╦═══════════════════════════════════╦════════════════════════════════════════╗
║ 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              ║
╚═════════════════╩═══════════════════╩═══════════════════════════════════╩════════════════════════════════════════╝
  • (1) these style values return non-deterministic results. They include all styles (aa) (without century) and a subset of styles (aaaa) (with century).
  • (2) Default values (style0 or 100, 9 or 109, 13 or 113, 20 or 120, and 21 or 121) always return the century (yyyy).
  • (3) input when you convert to datetime; output when you convert character data.
  • (4) designed for use with XML. For conversion of datetime or smalldatetime character data of output format is as described in the previous table.
  • (5) Hijri is a calendar system with many variations. SQL Server uses the Kuwaiti algorithm.
01.06.2017 / 15:32
3

Use 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 103 value defines that the date string is in the format dd / mm / yyyy .

    
01.06.2017 / 14:12
1

If you are in SQL and considering that the dt_item field is of type date, to perform the query you can do this:

select *
    from TAB_FATURAMENTO
    where cd_cliente like '%'
    and dt_item between '2017-05-15' and '2017-05-31';

Abs.

    
01.06.2017 / 14:08
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 most correct way to convert and compare try

    
01.06.2017 / 17:59
0

If the DT_ITEM column is of type date (DATE or DATETIME) and you want to convert string to 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)
    
01.06.2017 / 18:02
-1

After careful analysis of the database tables, we find (my manager and myself) that the problem is in the field itself, it is not recognizing the means of conversion.

So, the way we found to make such comparisons between 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 solved our problem.

Thanks in advance for all the help.

    
01.06.2017 / 17:12