Query with Date and Time

Question

In my database, using SQL Server 2014, with a table named TableOberge and with column named Date-In of type Date, and with a second column named Hour-In of type Time(7). With this query I want to display the records that have reached their dates and times in DatagridView1... but I have failed to display them correctly.

Any Help please:

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Dbb_Connection()
    Using InfoAdapter As New SqlDataAdapter("SELECT * FROM TABLEOBERGE WHERE [DATE_IN] >= CONVERT(date, GETDATE()) AND [HOUR_IN] >= convert(time(0),getDate())", StrCon)
        InfoTable = New DataTable
        InfoAdapter.Fill(InfoTable)
        DataGridView1.DataSource = InfoTable
    End Using
End Sub

My code for add record :

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dbb_Connection()
    Using Command As New SqlCommand With {.Connection = StrCon}
        With Command.Parameters
            Command.CommandText = "INSERT INTO [TABLEOBERGE] ([ID], [FIRSTNAME], [PHONE], [ADRESSE], [DATE_OUT], [HOUR_OUT], [DATE_IN], [HOUR_IN]) VALUES (@ID, @FIRSTNAME, @PHONE, @ADRESSE, @DATE_OUT, @HOUR_OUT, @DATE_IN, @HOUR_IN)"
            .AddWithValue("@ID", SqlDbType.Int).Value = TextBox1.Text
            .AddWithValue("@FIRSTNAME", SqlDbType.NVarChar).Value = TextBox2.Text
            .AddWithValue("@PHONE", SqlDbType.NVarChar).Value = TextBox3.Text
            .AddWithValue("@ADRESSE", SqlDbType.NVarChar).Value = TextBox4.Text
            .AddWithValue("@DATE_OUT", SqlDbType.Date).Value = TextBox5.Text
            .AddWithValue("@HOUR_OUT", SqlDbType.Time).Value = TextBox6.Text
            .AddWithValue("@DATE_IN", SqlDbType.Date).Value = TextBox7.Text
            .AddWithValue("@HOUR_IN", SqlDbType.Time).Value = TextBox8.Text
        End With
        If StrCon.State = ConnectionState.Closed Then StrCon.Open()
        If Command.ExecuteNonQuery() = 1 Then
            MsgBox("SUCCED ADD", MsgBoxStyle.MsgBoxRtlReading, "SUCCES")
        Else
            MsgBox("ERROR FATAL", MsgBoxStyle.MsgBoxRtlReading, "ERROR")
        End If
        StrCon.Close()
    End Using
End Sub

http://www.vbforums.com/showthread.php?862331-Display-record-with-condition-of-date-and-time And Here https://www.developpez.net/forums/d1851524/dotnet/langages/vb-net/afficher-records-conditions-date-time/

Hi there, I have added some relevant tags so your post has more visibility. Have you tried running your query in SSMS to confirm the returned results are what you expect? — Jacob H, May 10 '18 at 18:01
`but I have failed to display them correctly.` <= That is very ambiguous. Please elaborate exactly how it fails and include all the relevant details. — Igor, May 10 '18 at 18:01
On a side note. IMO your design would be better if you used a single field for your date and time instead of splitting them into 2. `DateIn` of type `DateTime2(7)` would be a good fit. There are plenty of SQL functions that allow you to filter based strictly on time or date if this is necessary. It makes writing a query like above simpler. — Igor, May 10 '18 at 18:03
With my failed query nothing happens and appears in Datagriview1.If i change query only like this ( SELECT * From TableOberge , StrCon) all records in my Table appear in my DataGridView1 — ABIDINE, May 10 '18 at 18:08
What you really need to post is the table structure, sample data and desired output. Here is a great place to start. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ I don't think this query is doing what you think it does, and I certainly don't think it is what you want it to do. — Sean Lange, May 10 '18 at 18:20
Thanks for the link .. what I want to do is fine here in link of my first post — ABIDINE, May 10 '18 at 18:29
"How will the request be with `DateTime2(7)`" <= `SELECT * FROM TABLEOBERGE WHERE [DATE_IN] <= GETDATE()` — Igor, May 10 '18 at 18:36
I think you might have the equality comparer turned the wrong way. If you are looking for an expired date (ie. in the past) then that date should be **less than** the current date. — Igor, May 10 '18 at 18:37
Maybe Igor is right? Your query mean: 1) [DATE_IN] >= CONVERT(date, GETDATE()) --> Every row with date greater or equal to TODAY AND time (hh:mm:ss) greater than current time. That query means that if DATE_IN is tomorrow but the time is lower than current time the row won't be selected. Is your goal? — GigiS, May 10 '18 at 18:49
Well good luck then. If you would just post the details this would be a simple solution. Hope you get it figured out. — Sean Lange, May 10 '18 at 19:22

Igor · Accepted Answer · 2018-05-10T18:58:18.833

0

I think you might have the equality comparer turned the wrong way. If you are looking for an expired date (ie. in the past) then that date should be less than the current date.

SELECT * 
FROM TABLEOBERGE 
WHERE [DATE_IN] <= CAST(getdate() as Date) AND [HOUR_IN] <= CAST(getDate() AS TIME)

IMO your design would be better if you used a single field for your date and time instead of splitting them into 2. DateIn of type DateTime2(7) would be a good fit. There are plenty of SQL functions that allow you to filter based strictly on time or date if this is necessary. It makes writing a query like above simpler.

SELECT * FROM TABLEOBERGE WHERE [DATE_IN] <= GETDATE()

edited May 10 '18 at 18:58

answered May 10 '18 at 18:39

Igor

60,821
10
100
175

Thank you very much I started to learn your super lesson. I only have one problem left. How will the sqldatatypes be in this code for add record .. in my first update post – ABIDINE May 10 '18 at 18:50
@ABIDINE - See [How can I add user-supplied input to an SQL statement?](https://stackoverflow.com/q/35163361/1260204). – Igor May 10 '18 at 18:54
@ABIDINE - also changing the question is frowned upon. If you have a new question you should post it as a new question instead of adding it to the existing one at a later point in time. – Igor May 10 '18 at 18:55
@ABIDINE - If the above has answered your original question please consider marking an answer using the checkmark on the left side of the answer. – Igor May 10 '18 at 18:55
Thanks dear Igor .. no i just want to know how will this datatype have a relationship with your query ..( .AddWithValue("@DATE_IN", SqlDbType.Date).Value = TextBox7.Text .AddWithValue("@HOUR_IN", SqlDbType.Time).Value = TextBox8.Text) – ABIDINE May 10 '18 at 19:14
@ABIDINE - you have to set the parameter's value using the the native .net type, not the string equivalent. Example: `.Value = DateTime.Now`. If all you have is text then you have to parse that first into a DateTime instance. – Igor May 10 '18 at 19:17
@ABIDINE - but again, this is a very different question than the one you started with originally. Please create a new question for further help. See also the link I posted earlier in the comment chain of this answer. – Igor May 10 '18 at 19:17
Thank you Igor .how to add reputation for your answer please ?? – ABIDINE May 10 '18 at 19:27
@ABIDINE - You can click on the checkmark on the left of the answer which turns it green. This is also called marking an answer. – Igor May 10 '18 at 19:34

Query with Date and Time

1 Answers1