Solved

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

Posted on 2015-01-12
8
395 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 34

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

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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

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 34

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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
I have a large data set and a SSIS package. How can I load this file in multi threading?
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
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

626 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