Strange strings in SQL statement
-
Hi, I'm having a problem with an sql select statement from some c# code. The function is meant to count the number of entries in a table where the users dates of birth are between two dates. The two birthday ranges are taken from two integers userAgeFrom and userAgeTo. When I run the code I get the following error: "The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value." The weird thing is if I manually populate the strings userDOBFrom and userDOBTo with dates (eg userDOBTo = "12/09/1971") the select statement works and returns the number of users. When I try to populate the strings from a DateTime object, as below, the function fails. Any help would be much appreciated! Kevin Here is the code:
int userAgeFrom = 18; int userAgeTo = 30; int numUsers; string selectStatement; string userDOBFrom; string userDOBTo; DateTime dtUserDOBFrom = DateTime.Now; DateTime dtUserDOBTo = DateTime.Now; userDOBFrom = dtUserDOBFrom.AddYears(-userAgeFrom).ToShortDateString(); userDOBTo = dtUserDOBFrom.AddYears(-userAgeTo).Date.ToShortDateString(); selectStatement = "select count(*) from USERS where date_of_birth between '" + userDOBFrom + "' and '"+ userDOBTo + "'"; sqlDBConnection.Open(); sqlDataAdapter.SelectCommand.CommandText = selectStatement; numUsers = (int)sqlDataAdapter.SelectCommand.ExecuteScalar(); sqlDBConnection.Close(); -
Hi, I'm having a problem with an sql select statement from some c# code. The function is meant to count the number of entries in a table where the users dates of birth are between two dates. The two birthday ranges are taken from two integers userAgeFrom and userAgeTo. When I run the code I get the following error: "The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value." The weird thing is if I manually populate the strings userDOBFrom and userDOBTo with dates (eg userDOBTo = "12/09/1971") the select statement works and returns the number of users. When I try to populate the strings from a DateTime object, as below, the function fails. Any help would be much appreciated! Kevin Here is the code:
int userAgeFrom = 18; int userAgeTo = 30; int numUsers; string selectStatement; string userDOBFrom; string userDOBTo; DateTime dtUserDOBFrom = DateTime.Now; DateTime dtUserDOBTo = DateTime.Now; userDOBFrom = dtUserDOBFrom.AddYears(-userAgeFrom).ToShortDateString(); userDOBTo = dtUserDOBFrom.AddYears(-userAgeTo).Date.ToShortDateString(); selectStatement = "select count(*) from USERS where date_of_birth between '" + userDOBFrom + "' and '"+ userDOBTo + "'"; sqlDBConnection.Open(); sqlDataAdapter.SelectCommand.CommandText = selectStatement; numUsers = (int)sqlDataAdapter.SelectCommand.ExecuteScalar(); sqlDBConnection.Close();Hmm, better would be to use command parameters. Otherwise though, I see nothing wrong with your code, other than the misplaced ".Date." in
userDOBTo = dtUserDOBFrom.AddYears(-userAgeTo).Date.ToShortDateString(); -
Hmm, better would be to use command parameters. Otherwise though, I see nothing wrong with your code, other than the misplaced ".Date." in
userDOBTo = dtUserDOBFrom.AddYears(-userAgeTo).Date.ToShortDateString(); -
Hi, thanks for the tip, I've tried it with and without the extra .Date and get the same error message. Can you elaborate on the command parameters? Cheers!
Sorry, don't have time for a full explanation. Its in the Help. Basic idea is that instead of quoting the dates, you would put a placeholder there instead, like "... BETWEEN @P1 and @P2". Then you can create and add parameters to your command. When you do it this way, it has the benefit of strong-typing your parameters, which avoids nasty surprises like the one you are getting, as well as sneaky SQL-Injection attacks. It sounds more complex than it is...
-
Hi, I'm having a problem with an sql select statement from some c# code. The function is meant to count the number of entries in a table where the users dates of birth are between two dates. The two birthday ranges are taken from two integers userAgeFrom and userAgeTo. When I run the code I get the following error: "The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value." The weird thing is if I manually populate the strings userDOBFrom and userDOBTo with dates (eg userDOBTo = "12/09/1971") the select statement works and returns the number of users. When I try to populate the strings from a DateTime object, as below, the function fails. Any help would be much appreciated! Kevin Here is the code:
int userAgeFrom = 18; int userAgeTo = 30; int numUsers; string selectStatement; string userDOBFrom; string userDOBTo; DateTime dtUserDOBFrom = DateTime.Now; DateTime dtUserDOBTo = DateTime.Now; userDOBFrom = dtUserDOBFrom.AddYears(-userAgeFrom).ToShortDateString(); userDOBTo = dtUserDOBFrom.AddYears(-userAgeTo).Date.ToShortDateString(); selectStatement = "select count(*) from USERS where date_of_birth between '" + userDOBFrom + "' and '"+ userDOBTo + "'"; sqlDBConnection.Open(); sqlDataAdapter.SelectCommand.CommandText = selectStatement; numUsers = (int)sqlDataAdapter.SelectCommand.ExecuteScalar(); sqlDBConnection.Close();Try: userDOBFrom.ToShortDateString() userDOBTo.ToShortDateString() when you build the SQL statement. The default ToString() method may generate the wrong syntax. Normally I would insert: MessageBox.Show(selectStatement); To see what is going on in this case.
-
Hi, I'm having a problem with an sql select statement from some c# code. The function is meant to count the number of entries in a table where the users dates of birth are between two dates. The two birthday ranges are taken from two integers userAgeFrom and userAgeTo. When I run the code I get the following error: "The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value." The weird thing is if I manually populate the strings userDOBFrom and userDOBTo with dates (eg userDOBTo = "12/09/1971") the select statement works and returns the number of users. When I try to populate the strings from a DateTime object, as below, the function fails. Any help would be much appreciated! Kevin Here is the code:
int userAgeFrom = 18; int userAgeTo = 30; int numUsers; string selectStatement; string userDOBFrom; string userDOBTo; DateTime dtUserDOBFrom = DateTime.Now; DateTime dtUserDOBTo = DateTime.Now; userDOBFrom = dtUserDOBFrom.AddYears(-userAgeFrom).ToShortDateString(); userDOBTo = dtUserDOBFrom.AddYears(-userAgeTo).Date.ToShortDateString(); selectStatement = "select count(*) from USERS where date_of_birth between '" + userDOBFrom + "' and '"+ userDOBTo + "'"; sqlDBConnection.Open(); sqlDataAdapter.SelectCommand.CommandText = selectStatement; numUsers = (int)sqlDataAdapter.SelectCommand.ExecuteScalar(); sqlDBConnection.Close();This might be a long shot, but we've had problems like this with different regional settings. The ToShortDateString() "uses formatting information derived from the current culture." Another thing to try (even longer shot), is to make sure that the collation on your server is expecting your dates in the format that ToShortDateString() is returning.
-
Hi, I'm having a problem with an sql select statement from some c# code. The function is meant to count the number of entries in a table where the users dates of birth are between two dates. The two birthday ranges are taken from two integers userAgeFrom and userAgeTo. When I run the code I get the following error: "The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value." The weird thing is if I manually populate the strings userDOBFrom and userDOBTo with dates (eg userDOBTo = "12/09/1971") the select statement works and returns the number of users. When I try to populate the strings from a DateTime object, as below, the function fails. Any help would be much appreciated! Kevin Here is the code:
int userAgeFrom = 18; int userAgeTo = 30; int numUsers; string selectStatement; string userDOBFrom; string userDOBTo; DateTime dtUserDOBFrom = DateTime.Now; DateTime dtUserDOBTo = DateTime.Now; userDOBFrom = dtUserDOBFrom.AddYears(-userAgeFrom).ToShortDateString(); userDOBTo = dtUserDOBFrom.AddYears(-userAgeTo).Date.ToShortDateString(); selectStatement = "select count(*) from USERS where date_of_birth between '" + userDOBFrom + "' and '"+ userDOBTo + "'"; sqlDBConnection.Open(); sqlDataAdapter.SelectCommand.CommandText = selectStatement; numUsers = (int)sqlDataAdapter.SelectCommand.ExecuteScalar(); sqlDBConnection.Close();I agree with the general consensus. Most likely
ToShortDateString()is returning a string representation of a date that SQL Server can't parse. I would use the formattedToString()instead:userDOBFrom = dtUserDOBFrom.AddYears(-userAgeFrom).ToString("yyyy-MM-dd"). This should put it in an ISO standard format that SQL Server should understand, regardless of culture/regional settings.
Grim
(aka Toby)
MCDBA, MCSD, MCP+SB
SELECT * FROM user WHERE clue IS NOT NULL GO
(0 row(s) affected)