Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New to the CF scene
    Join Date
    May 2007
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    A problem with dates

    Hi,
    I keep getting an error when I run my code. Here’s the error “The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
    The statement has been terminated”. It points to this part of my code…
    Code:
     cmd.ExecuteNonQuery()
    Here’s my code…
    Code:
        Private Sub TestDate()
            Dim DateClosedString As String = "Closed"
            Dim cnn As Data.SqlClient.SqlConnection
            Dim cmd As Data.SqlClient.SqlCommand
            Dim TodayTest As Date = Today
            Dim strSQL2 As String
    
            strSQL2 = "Update TestTBL SET DateStatus = '" & DateClosedString & "' WHERE EndDate >= '" & Today & "'"
            Dim conn As String
            conn = "Data Source=.\SQLEXPRESS;AttachDbFilename=""|DataDirectory|\aspnetdb.mdf"";Integrated Security=True;User Instance=True"
            cnn = New Data.SqlClient.SqlConnection(conn)
            cmd = New Data.SqlClient.SqlCommand(strSQL2, cnn)
            cnn.Open()
            cmd.ExecuteNonQuery()
            cnn.Close()
    End Sub
    What I want it to do is check my TestTBL and if any EndDates are greater or equal to todays date, write closed to the DateStatus field.
    The dates are saved to the database as DateTime. This is copy and passed from an enddate field in my database “22/05/2007 00:00:00”. I’m not really need the time part but I cant find any other way.
    Can anyone help? I’ve been pulling my hair out over this one for days.

  • #2
    Regular Coder Freon22's Avatar
    Join Date
    May 2005
    Location
    USA
    Posts
    287
    Thanks
    3
    Thanked 5 Times in 5 Posts
    http://www.sql-server-performance.co...aspx?faqid=203

    This may also be due to date setting of your machine on which SQL Server is installed. It take date as MM/DD/YYYY format. If you are searching with a format of 22/05/2007 it checks that 22 is not valid month and returns error.
    Last edited by Freon22; 05-15-2007 at 06:08 PM.

  • #3
    New Coder
    Join Date
    Mar 2007
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts
    why don't you try using query parameters instead? declare a parameter with the date type and put your value in it, the conversion, if the format is valid, should be made automatically.

  • #4
    New Coder
    Join Date
    Mar 2006
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Code:
     strSQL2 = "Update TestTBL SET DateStatus = '" & DateClosedString & "' WHERE EndDate >= '" & Today & "'"
    It can't compare a date with a string.

  • #5
    New Coder
    Join Date
    Mar 2007
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Code:
    strSQL = "Update TestTBL SET DateStatus =@strDateClosed WHERE EndDate >= @DateToday"
    
    cmd.commandtext = strSQL
    Dim param As New SqlParameter("@strDateclosed", Data.SqlDbType.Type)
    param.value = txtTest.text
    cmd.parameters.add(param)
    param = new SqlParameter("@DateToday", Data.SqlDbType.Datetime)
    param.value = Date.Today()
    cmd.Parameters.Add(param)
    this could be better, but I'm in a hurry. It should work, though. The Type in italics is the type of that on your database, as I wasnt sure if it was a string or a date.

  • #6
    Regular Coder
    Join Date
    May 2007
    Location
    UK
    Posts
    180
    Thanks
    0
    Thanked 18 Times in 18 Posts
    It looks to me that your database has been set up to use "American" date formats. I don't know how to fix this as a server default or fix it in the database itself but there is a simple code workaround.

    After opening the database connection, but before executing any other command, execute the following command:

    Code:
    set dateformat dmy
    It will remain active for all other commands executed against the same connection.

    On a related subject, you may have similar problems with DateValidator controls. Just add the following line to your web.config:

    Code:
    <globalization requestEncoding="utf-8" responseEncoding="utf-8" culture="en-GB" uiCulture="en-GB" />
    in the system.web section.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •