Member Avatar for etti

I really need help with the below error.
I dont get this when i run my asp.net c# application in VS@005,
but when i make virtual directory on my office Windows server I
get this error.
Tell me what changes need to be done in C# code or crystal report or Sql queries or
server settings !!below is the error :


The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Description: An unhandled exception occurred during the execution of the current web request.
Exception Details: System.Data.SqlClient.SqlException: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

The scenario is there is a VB 6.0 exe which runs on the startup of machine and using a simple hard coded sql query collects date time of the instance and puts in database.
Later for checking out the entries asp.net c# application is being made where using crystal report
we are displaying the entries.
Specifically there is a report which calculates user's Leaves:
Below is the simple function written in c# for calculating leave to show in report :

public void totalleavetaken()
    { 
//        select count(status) from user_info 
//where status = 'leave' and user_name = 'vishal'
//and user_date < '04/30/2008'

        string username;
        DateTime enddate;
        DateTime startdate;

        username = ddluser.SelectedValue;
        enddate = CalendarPopup2.SelectedDate;
        startdate = CalendarPopup1.SelectedDate;

        sqlconn = new SqlConnection(creativeconfiguration.DbConnectionString);
        string str = "select count(status) from user_info where status = 'Leave' and user_name ='" + username + "' and user_date between '" + startdate + "' and '" + enddate + "'";
        cmd = new SqlCommand(str, sqlconn);
        sqlconn.Open();
        dr =  cmd.ExecuteReader();
        if (dr.Read()) -----------------------> The error coming on this line
         {
            lbltotalleave.Text = Convert.ToString(dr[0]);
        }
        sqlconn.Close();
    
    }
  • 2 Contributors
  • 2 Replies
  • 317 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by etti

Dani AI

Generated

The error means SQL Server tried to turn a string into a datetime and the string could not be interpreted for the server session (wrong format, invalid date, or out-of-range). That usually happens when dates are stored as char/varchar or when the dev machine and the production server use different dateformat/language settings. The problem is an implicit conversion in SQL, not a C# parse bug.

Quick diagnostics to run on the server:

-- confirm column type
SELECT COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'user_info' AND COLUMN_NAME = 'user_date';
-- SQL Server 2012+: find rows that fail conversion
SELECT * FROM user_info
WHERE user_date IS NOT NULL
  AND TRY_CONVERT(datetime, user_date) IS NULL;
-- Older servers: ISDATE (note: ISDATE depends on session language)
SELECT * FROM user_info
WHERE user_date IS NOT NULL
  AND ISDATE(user_date) = 0;

A robust fix is to store dates in a DATETIME/DATE column and migrate values safely (backup first). Example migration pattern:

ALTER TABLE user_info ADD user_date_dt datetime NULL;
UPDATE user_info
SET user_date_dt = TRY_CONVERT(datetime, user_date);
-- inspect failures
SELECT * FROM user_info WHERE user_date_dt IS NULL AND user_date IS NOT NULL;
-- after fixing rows: drop old varchar and rename column

At the application layer, pass DateTime parameters (not concatenated strings) and use a proper date-range predicate. Use parameterized ADO.NET, using blocks, and ExecuteScalar for a count. Also handle end-of-day correctly (BETWEEN is inclusive). Example pattern:

-- SQL: use half-open range
SELECT COUNT(1) FROM user_info
WHERE status = @status
  AND user_name = @user
  AND user_date >= @start
  AND user_date < @endExclusive;

Followed by passing @start = startDate.Date and @endExclusive = endDate.Date.AddDays(1) from C#.

Notes: 's string-format hint only masks locale and injection issues; was right to switch to parameterized commands. Also check the VB6 inserter: it should write a datetime parameter (or an unambiguous ISO format) rather than a locale-formatted string. Back up data before schema changes.

Recommended Answers

All 2 Replies

Member Avatar for greeny_1984

hi,

string str = "select count(status) from user_info where status = 'Leave' and user_name ='" + username + "' and user_date between '" + startdate + "' and '" + enddate + "'";
change to

string str = "select count(status) from user_info where status = 'Leave' and user_name ='" + username + "' and user_date between '" + startdate.ToShortDateString() + "' and '" + enddate.ToShortDateString() + "'";
Member Avatar for etti

string str = "select count(status) from user_info where status = 'Leave' and user_name ='" + username + "' and user_date between '" + startdate.ToShortDateString() + "' and '" + enddate.ToShortDateString() + "'";

The above solution gets the injection attack.
Finally i found the answer which is explained below:
The previous query was getting injection ::

string str = "select count(status) from user_info where status = 'Leave' and user_name ='" 
  + username + "' and user_date between '" + startdate + "' and '" + enddate + "'";

I suggest that you change it to use stored procedures or parameterised TSQL

string str = "select count(status) from user_info where status = 'Leave' and user_name = @USER and user_date between @startdate and @enddate'"; 

cmd.Parameters.Add("@USER", SqlDbType.NVarChar, 100);  -- Assuming type and size
cmd.Parameters["@USER"].Value = username
cmd.Parameters.Add("@startdate ", SqlDbType.DateTime);
cmd.Parameters["@startdate "].Value = startdate ;
cmd.Parameters.Add("@enddate", SqlDbType.DateTime);
cmd.Parameters["@enddate"].Value = enddate;

This works perfectly :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.