• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 425
  • Last Modified:

VB.Net - SQL Server Geting Certain Date Values from DateTime Field

Good Day Experts!

I am having trouble with an item and have tried the net searching to no avail.  

In my SQL table I have a value like 10/28/2014 10:09:23 PM.  The field is defined as DateTime.  In the VB.Net program, the User will enter a start and end date  10/28/2014 to 10/29/2014.  I have been unable to figure out this out.  I have tried Date conversions and Left to try and get the first 10 characters before the time.  

Do you have any helpful suggestions?

Thanks,
jimbo99999
0
Jimbo99999
Asked:
Jimbo99999
2 Solutions
 
ste5anSenior DeveloperCommented:
Well, why not posting your code?

The normal solution is to use a parametized query or command: SqlCommand.Parameters Property.

E.g.

Dim commandText As String = "SELECT * FROM yourTable WHERE DateColumn BETWEEN @Start AND @End;" 

Using connection As New SqlConnection(connectionString)
	Dim command As New SqlCommand(commandText, connection)

	command.Parameters.Add("@Start", SqlDbType.DateTime)
	command.Parameters("@Start").Value = "yourStartDate"
	command.Parameters.Add("@End", SqlDbType.DateTime)
	command.Parameters("@End").Value = "yourEndDate"
	Try
		connection.Open()
		Dim reader As SqlDataReader = command.ExecuteReader()
        While reader.Read()
            Console.WriteLine("{0}", reader(0))
        End While 
	Catch ex As Exception
		Console.WriteLine(ex.Message)
	End Try 
End Using 

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>In the VB.Net program, the User will enter a start and end date  10/28/2014 to 10/29/2014.
In one text box or two?  Common practice is to have a 'From' textbox and 'To' textbox, and pass both values as datetime, rather then '10/28/2014 to 10/29/2014' in one textbox and then rely on code to parse the string to extract both dates.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Regional settings from client and server machines may be different. Also, what's the SQL Server instance's collation?
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Jimbo99999Author Commented:
JimHorn:
I apologize for not being complete in my description.  I have From and To textboxes on this screen as many others.  
How do I pass them as datetime for use in my query?

Ste5an:
I have never tried it the way you have suggested.  Here is what I normally do.  The [Process Week] field does not have the time after it like the field causing me grief.

 cmdGetRecordsString = "Select * FROM [Invoicing] " & _
 "Where [Process Week] >= '" & txtStartDateAud.Text.Trim & "' and [Process Week] <= '" & txtEndDateAud.Text.Trim & "' " 

 cmdGetRecords = New SqlCommand(cmdGetRecordsString, SQLConnectAccount)                        
 Dim rdrGetRecords As SqlDataReader = cmdGetRecords.ExecuteReader
0
 
ste5anSenior DeveloperCommented:
Please use parameterized queries. Otherwise you code is also prone to SQL Injection (ignore the ASP specific points, it's the same for WinForms/WPF).
0
 
Jimbo99999Author Commented:
Ok, I will try for the first time.  I am always up for adding new things to the toolbox of knowledge!
0
 
Scott PletcherSenior DBACommented:
You should add one day to the end date, then do a < the ending date rather than <=.  

That is, rather than " >= '20141028' and <= '20141029' " --wrong way!
you want to specify:
>= '20141028' and < '20141030' --right way!
(Btw, 'YYYYMMDD' will always be interpreted correctly, whereas mm/dd/yyyy is subject to date and language settings.)

This avoids errors based on solely on data type and esp. data type changes (such as a datetime column later being converted to a datetime2 column).
0
 
Jimbo99999Author Commented:
Thanks for the responses.  I had another "little" project that came up in another area.  I will hopefully get back to this one Friday or Monday. Thanks for your patience.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now