# Visual Basic > Database Development >  [RESOLVED] Date in Query

## sacramento

Hi to all,

I have this little query to sort dates that are greather than the actual date, but i don't know why the query don't sort the list like desired.

Example:

Date today - 11-01-2023

Records
Previsao_Nasc_1_Ovo  = 13-03-2022
Previsao_Nasc_1_Ovo  = 15-01-2023
Previsao_Nasc_1_Ovo  = 16-01-2022
Previsao_Nasc_2_Ovo  = 22-01-2022
Previsao_Nasc_1_Ovo  = 14-01-2023


The code should retrieve:
15-01-2023 and 14-01-2023

but the code sort all records...seams the code only see the day

Any help please to resolve this?





```
sql = _
       "select Previsao_Nasc_1_Ovo as PrevisaoX, Viveiro_N" & _
       " from fichas_criacao" & _
       " where Previsao_Nasc_1_Ovo > ? " & _
       " union all " & _
       " select Previsao_Nasc_2_Ovo as PrevisaoX, Viveiro_N" & _
       " from fichas_criacao" & _
       " where Previsao_Nasc_2_Ovo > ? " & _
       " order by PrevisaoX "

        Dim cmd As New OleDbCommand(sql, con)
        For i As Integer = 1 To 2
            cmd.Parameters.Add("", OleDbType.Date).Value = Date.Today.ToString("dd-MM-yyyy")
        Next i

        da = New OleDbDataAdapter(cmd)


        da.Fill(ds, "table1")
```


Thanks

----------


## jmcilhinney

It's a date. Stop using a String. If you want to use todays' date then use Date.Today. Don't convert it to a String.

Also, VB has supported multiline String literals for some time now, so you should stop making your code harder to read - and therefore more error-prone - by using all that concatenation. Here's what your code should look like:

vb.net Code:
Dim sql = "SELECT Previsao_Nasc_1_Ovo AS PrevisaoX, Viveiro_N
            FROM fichas_criacao
            WHERE Previsao_Nasc_1_Ovo > ?
            UNION ALL
            SELECT Previsao_Nasc_2_Ovo AS PrevisaoX, Viveiro_N
            FROM fichas_criacao
            WHERE Previsao_Nasc_2_Ovo > ?
            ORDER BY PrevisaoX"
 Dim adapter As New OleDbDataAdapter(sql, "connection string here")
 With adapter.SelectCommand.Parameters
    .Add("Previsao_Nasc_1_Ovo", OleDbType.Date).Value = Date.Today
    .Add("Previsao_Nasc_2_Ovo", OleDbType.Date).Value = Date.Today
End With
 Dim table As New DataTable
 adapter.Fill(table)
The SQL code is much easier to read when you use upper-case for the keywords, but that's obviously not required. There's also not really any point creating separate connection and command objects when the data adapter can do that implicitly. You also ought to only create a DataTable unless you specifically need a DataSet. I'd also recommend using names for the parameters for readability, even if the system doesn't use them.

----------


## jmcilhinney

I also forgot to mention that the ORDER By clause in your SQL is probably not doing what you think it is. That is part of the second subquery, i.e. the part after the UNION ALL. If you want the combined data sorted then you would need another SELECT statement and an ORDER BY associated with that.

----------


## sacramento

> It's a date. Stop using a String. If you want to use todays' date then use Date.Today. Don't convert it to a String.
> 
> Also, VB has supported multiline String literals for some time now, so you should stop making your code harder to read - and therefore more error-prone - by using all that concatenation. Here's what your code should look like:
> 
> vb.net Code:
> Dim sql = "SELECT Previsao_Nasc_1_Ovo AS PrevisaoX, Viveiro_N
>             FROM fichas_criacao
>             WHERE Previsao_Nasc_1_Ovo > ?
>             UNION ALL
> ...



Hi jmcilhinney,

Thanks for your help

Unfornuttely the result is the same with date.today...Records with year 2022, continue to sort

Thanks

----------


## jmcilhinney

To clarify, what you're talking about is filtering, not sorting. Sorting is the order of the records that are retrieved, while filtering is what records are included and which are excluded.

What database are you using and what is the data type of those columns?

----------


## sacramento

> To clarify, what you're talking about is filtering, not sorting. Sorting is the order of the records that are retrieved, while filtering is what records are included and which are excluded.
> 
> What database are you using and what is the data type of those columns?


HI,

In fact i would like filtering records

Database is Access and the data type of those columns are Text

----------


## jmcilhinney

> the data type of those columns are Text


Well that's the problem then. Don't store data that isn't text in a text column. If you want to store dates then do it in a date column. If the data is text then it is compared as text, e.g. "01-01-2023" comes before "01-02-2022". If you use the correct data types in the first place then you avoid issues like this. Do you store numbers as text? I would assume that that wouldn't even occur to you. Why would you then store dates as text?

----------


## jmcilhinney

If you absolutely must store dates as text because some other idiot is forcing you to, then you should store them in 'yyyy-MM-dd' format. That's why people represent date literals in T-SQL (for SQL Server) that way: because then chronological order will match alphanumeric order.

----------


## sacramento

Hi,
Not good idea store dates in text....i had change the data type for date and the code work very well

Thanks for your help

----------

