Problem putting a DateTime variable into a datetim

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Problem putting a DateTime variable into a datetim

Author

Topic

rhiaro
Starting Member

1 Post

Posted - 2010-01-19 : 08:41:25

Hi,

For a university project I am creating a basic car hire website. ASP.NET is all new to me, so if you could keep replies simply explained I'd appreciate it!

For the final stage of the car hire process, I take variables stored in sessions:

string theUser = User.Identity.Name.ToString();

DateTime pickUp = Convert.ToDateTime(Session["pickUp"]);

DateTime dropOff = Convert.ToDateTime(Session["dropOff"]);

string carReg = (String)Session["carReg"];

double pricePerNight = Convert.ToDouble(Session["carPrice"]);

double totalPrice = noOfDays * thePricePerNight;

to insert into the Hire table, where the datatypes are as follows:

UserName - varchar(MAX)

RegNo - varchar(50)

PickUpDate - datetime

ReturnDate - datetime

Price - money

But when I execute the insert statement, I get the following error:

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

My code for inserting into the database is as follows:

//Add details to Hire table

string connectionString = WebConfigurationManager.ConnectionStrings["AdminCarsConnectionString"].ConnectionString;

SqlConnection con = new SqlConnection(connectionString);

con.Open();

SqlCommand cmd;

string query = "INSERT INTO Hire (UserName, RegNo, PickUpDate, ReturnDate, Price)";

query += "VALUES ('" + theUser + "', '" + carReg + "', '" + dtPickUp + "', '" + dtDropOff + "', '" + totalPrice + "')";

cmd = new SqlCommand(query, con);

cmd.ExecuteNonQuery();

lblCust.Visible = false;

lblPrice.Visible = false;

btnConfirm.Visible = false;

lblHire.Text = "The process is complete.";

I experimented by changing the datatype in the table to varchar, and inserting string values of the dates instead and that worked perfectly - however I need to keep these values as datetime, because of a query that checks for time clashes at an earlier stage in the hire process, which uses the datetime values in the Hire table (this works just fine, using the datetime variables from the session).

Do you have any advice on how I could solve this problem? The values that I am trying to insert are datetime type, and the field I want to insert them into is datetime type, so I don't know why it has a conversion problem. I hope someone can help soon because this is so close to being finished, and my deadline is in two days...

Thanks,

Amy

Kristen
Test

22859 Posts

Posted - 2010-01-19 : 10:06:57

"Convert.ToDateTime(Session["pickUp"]);"

Not sure what this is doing behind the scenes, but a "default" conversion of String-to-Date normally relies on the locale setting of the machine it is running on (and because of that I always avoid default date conversions in case stuff gets moved to a server with different locale setting, or some twit changes the locale setting on the current machine!)

In SQL Server a date string formatted as "yyyymmdd" is regarded as unambiguous, and will always convert correctly, and without and "hints" as to what format it is in. So you could use string-manipulation in ASP.NET to get to that format, and then SQL should implicitly cast correctly. (Note there are NO hyphens in this format, don't use 'yyyy-mm-dd' as that will in some/many environments but not all <sigh!>)

You can convert from a formatted date string to datetime (again, in SQL) by using

CONVERT(datetime, 'xx/xx/xxxx', 999)

where the X's are a string-date and you know which bit is Day, Month and Year, and the "999" is the relevant number (from the documentation for the SQL CONVERT function) which corresponds to the date format you are trying to convert. (Note that conversion numbers < 100 are for 2 digit years, and >100 are for 4 digit years. And, yes, its an extremely primitive system - Welcome to Legacy Systems!

I have no idea what the equivalent would be in ASP.NET - hopefully something involving less legacy-junk!

Failing that, pass the date as a String to SQL and do the CONVERT in SQL


INSERT INTO MyTable(StringCol1, DateCol2, ...)
SELECT 'MyValue1', CONVERT(datetime, '19/01/2010', 101), ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources