Solved

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

Posted on 2015-01-12
8
372 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 49

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

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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 different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

756 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