Solved

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

Posted on 2015-01-12
8
359 Views
Last Modified: 2015-02-13
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
Comment
Question by:Jimbo99999
8 Comments
 
LVL 33

Accepted Solution

by:
ste5an earned 250 total points
ID: 40544305
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40544318
>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
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 40544338
Regional settings from client and server machines may be different. Also, what's the SQL Server instance's collation?
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:Jimbo99999
ID: 40544423
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
 
LVL 33

Expert Comment

by:ste5an
ID: 40544563
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
 

Author Comment

by:Jimbo99999
ID: 40544611
Ok, I will try for the first time.  I am always up for adding new things to the toolbox of knowledge!
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 250 total points
ID: 40544880
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
 

Author Comment

by:Jimbo99999
ID: 40552751
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
VB.Net - String Manipulation Scenario 2 23
Split data on commas and insert into another table in separate records 26 69
sql server query 12 24
SQL Log size 3 17
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question