Converting string to Datetime VB.net

Question

I have a string variable which gets populated with a date from a calender in this format 29/07/2012

I want to convert this to a datetime datatype before storing it in a sql database in this format 2012-07-29 00:00:00:000

The error message i'm getting at the minute is:
"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

Here's some code:

Dim strDate As String = ""

 Dim cmd2 As New SqlCommand("sp_Return6MonthTotal")
                Dim strTotalHours6Month As Integer = 0
                cmd2.Connection = con1
                cmd2.CommandType = Data.CommandType.StoredProcedure
                cmd2.Parameters.Add(New SqlParameter("@ServiceNumber", Data.SqlDbType.Char, 11))
                cmd2.Parameters.Add(New SqlParameter("@OvertimeDate2", Data.SqlDbType.DateTime))
                cmd2.Parameters("@ServiceNumber").Value = strServiceNumber
                cmd2.Parameters("@OvertimeDate2").Value = strDate

                If cmd2.ExecuteScalar() Is DBNull.Value Then
                    Label17.Text = "0"
                    Label21.Text = "0"
                Else
                    strTotalHours6Month = cmd2.ExecuteScalar()
                    Label17.Text = strTotalHours6Month
                    Label21.Text = Math.Round(strTotalHours6Month / 6)
                End If


                Dim cmd3 As New SqlCommand("sp_ReturnMonthTotal")
                Dim strTotalHours As Integer = 0
                cmd3.Connection = con1
                cmd3.CommandType = Data.CommandType.StoredProcedure
                cmd3.Parameters.Add(New SqlParameter("@ServiceNumber", Data.SqlDbType.Char, 11))
                cmd3.Parameters.Add(New SqlParameter("@OvertimeDate2", Data.SqlDbType.DateTime))
                cmd3.Parameters("@ServiceNumber").Value = strServiceNumber
                cmd3.Parameters("@OvertimeDate2").Value = strDate

Stored procedures:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_ReturnMonthTotal]
   @ServiceNumber varchar(100),
   @OvertimeDate2 datetime
AS

SELECT sum(hoursworked) from overtime where servicenumber = @ServiceNumber and month(CONVERT(datetime, OvertimeDate2, 103)) = month(CONVERT(datetime, @OvertimeDate2, 103)) and year(CONVERT(datetime, OvertimeDate2, 103)) = year(CONVERT(datetime, @OvertimeDate2, 103))

-------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Return6MonthTotal]
   @ServiceNumber varchar(100),
   @OvertimeDate2 datetime
AS

SELECT sum(hoursworked) from overtime where servicenumber = @ServiceNumber and overtimedate2 >= DATEADD(month, -6, CONVERT(datetime, @OvertimeDate2, 103))

Sorry my programming knowledge isn't great and I didn't code this so hope this makes sense to someone.

Can you provide some code that has issues?

Simon Whitehead
– Simon Whitehead

2012-08-02 10:49:40 +00:00
Commented Aug 2, 2012 at 10:49 — Simon Whitehead
– Simon Whitehead, Commented Aug 2, 2012 at 10:49
What have you tried? What code gives you that error?

Oded
– Oded

2012-08-02 10:50:14 +00:00
Commented Aug 2, 2012 at 10:50 — Oded
– Oded, Commented Aug 2, 2012 at 10:50
dont forget to mark it as accepted if it works for you...

Pranay Rana
– Pranay Rana

2012-08-02 11:06:07 +00:00
Commented Aug 2, 2012 at 11:06 — Pranay Rana
– Pranay Rana, Commented Aug 2, 2012 at 11:06

Pranay Rana · Accepted Answer · 2012-08-02 11:26:40Z

2

Try just convert date by sing code like as below and than send it to database to store value of that

vb.net

Dim strDate As String = "21/07/2006"
Dim dtfi As New DateTimeFormatInfo()
dtfi.ShortDatePattern = "dd/MM/yyyy"
dtfi.DateSeparator = "/"
Dim objDate As DateTime = Convert.ToDateTime(strDate, dtfi)

c#(Ref)

 string strDate = "21/07/2006";
 DateTimeFormatInfo dtfi = new DateTimeFormatInfo();
 dtfi.ShortDatePattern = "dd/MM/yyyy";
 dtfi.DateSeparator = "/";
 DateTime objDate = Convert.ToDateTime(strDate, dtfi);

edited Aug 2, 2012 at 11:26

answered Aug 2, 2012 at 10:51

Pranay Rana

177k37 gold badges244 silver badges267 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Oded · Accepted Answer · 2012-08-02 10:52:16Z

1

DateTime.Parse("29/07/2012") will parse without issue into a DateTime type. This may very well fail if the string is ambiguous ("08/07/2012" could be July 8th or August 7th, depending on culture), so using ParseExact or TryParseExact with the exact format string (and culture) may be a more robust solution.

This can be fed directly into SQL Server using any of the common ways (ADO.NET, an ORM like nHibernate or EF).

There is a misconception in the question that DATETIMEs are stored with a format in SQL Server - they are not. They have an internal representation not visible - what you see is SSMS formatting the value to what you see.

answered Aug 2, 2012 at 10:52

Oded

501k102 gold badges900 silver badges1k bronze badges

Comments

Daniel A. White · Accepted Answer · 2012-08-02 10:49:52Z

0

You will have to use DateTime.TryParseExact to get it in a DateTime object then store that as a parameter in your SqlCommand.

answered Aug 2, 2012 at 10:49

Daniel A. White

192k49 gold badges389 silver badges473 bronze badges

Comments

Mazdak Shojaie · Accepted Answer · 2012-08-02 10:58:43Z

0

Dim dt As DateTime
dt = Convert.ToDateTime(TextBox1.Text)

but I think you should use date format as MM/DD/YYYY (07/29/2012 instead of 29/07/2012)

answered Aug 2, 2012 at 10:58

Mazdak Shojaie

1,7281 gold badge27 silver badges33 bronze badges

Collectives™ on Stack Overflow

Converting string to Datetime VB.net

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related