Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2015-01-12
8
Medium Priority
?
411 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 35

Accepted Solution

by:
ste5an earned 1000 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 66

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 52

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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 

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 35

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 1000 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

730 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