devxlogo

SQL Date/Time Table Selection

SQL Date/Time Table Selection

Question:
I have a database with a field set to Date/Time.My project has a variable that is of type Date.In a text box it displays 04/21/97. I cannot get the SQL statement to recognize it so that I can select those records with that date. I can use the format #4/21/97#. Help!

Dim SQLsource As String    Dim stringTempDate As String    stringTempDate = dateWeekEdit    Text2.Text = stringTempDate    SQLsource = “SELECT * FROM Timecard”        ‘    SQLsource = “SELECT * FROM Timecard WHERE Empl_Number = ” _        & EmployeeNumber & ” AND Wk_Ending = ” & stringTempDate        ‘ The above SQL statement has trouble using any date string        ‘ to check against the Wk_Ending field which is set to Date/Time        ‘ format.    datTimecard.DatabaseName = App.Path & dbTimeCard    datTimecard.RecordSource = SQLsource    datTimecard.Refresh

Answer:
You’re on the right track using the pound signs; however, I would try itlike this:

  SQLsource = “SELECT * FROM Timecard WHERE Empl_Number = ” _     & EmployeeNumber & ” AND Wk_Ending = #” & stringTempDate & “#”
Let stringTempDate just be the date. Make sure that the Wk_Ending field isactually a Date/Time field and not something else. The pound sign trickonly works on real date/time fields.

See also  Comparing different methods of testing your Infrastructure-as-Code
devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist