Having trouble with a DateTime in my query
I am trying to grab the purchase orders from my database that took place on todays date.
My query looks like this:
VB Code:
Dim cnn As New SqlConnection(strCon)
Dim cmd As New SqlCommand
Dim da As New SqlDataAdapter
Dim ds As New DataSet
'Try
cmd = cnn.CreateCommand
cmd.CommandText = "SELECT " & _
"PurchaseOrders.CreationDateTime," & _
"PurchaseOrders.StoreID," & _
"PurchaseOrders.PurchaseOrderNumber," & _
"PurchaseOrders.VendorCode," & _
"PurchaseOrderItems.ItemCode," & _
"PurchaseOrderItems.Description," & _
"PurchaseOrderItems.VendorItemCode," & _
"PurchaseOrderItems.VendorUnitCode," & _
"PurchaseOrderItems.VendorOrderQuantity," & _
"Stores.AddressLine1, Stores.City," & _
"Stores.State, Stores.ZipCode " & _
"FROM PurchaseOrderItems INNER JOIN " & _
"PurchaseOrders ON PurchaseOrderItems.PurchaseOrderID = " & _
"PurchaseOrders.PurchaseOrderID INNER JOIN " & _
"Stores ON PurchaseOrders.StoreID = Stores.StoreID " & _
"WHERE PurchaseOrders.PurchaseOrderStatusCode = 'Ordered' AND (" & _
"PurchaseOrders.VendorCode = 'Spartan Oil Corporation' OR " & _
"PurchaseOrders.VendorCode = 'Vesco Oil Corporation' OR " & _
"PurchaseOrders.VendorCode = 'Polymer Dynamics' OR " & _
"PurchaseOrders.VendorCode = 'Mighty Auto Parts East') AND " & _
"PurchaseOrders.CreationDateTime >= " & "2/19/2007 10:21:00 AM"
da.SelectCommand = cmd
da.Fill(ds, "Orders")
dgvOrders.DataSource = ds
dgvOrders.DataMember = "Orders"
In that huge query the last item in the Where Clause is what I am having trouble with. I just want to pull Purchase orders that were made on the current days date. The database is SQL Server and the field is a DateTime Field. I am sure it is the way I have set the date but that is how it is set up in the SQL table.
Re: Having trouble with a DateTime in my query
Firstly, how is this:
VB Code:
"PurchaseOrders.CreationDateTime >= " & "2/19/2007 10:21:00 AM"
an improvement over this:
VB Code:
"PurchaseOrders.CreationDateTime >= 2/19/2007 10:21:00 AM"
Secondly, enclose date/time values in "#" symbols. You can use single quotes too but I think it's preferable to use hash symbols to differentiate them from strings.