Customized date formating
-
Hi, I have always used the following coding when formatting dates for use with SQL: bookingMadeDateTime.ToString("yyyy-MM-dd hh:mm:ss tt") <-- or very similar however I have now been advised by a prospective user that they are using the following formats and the sql insert is failing: Date: dd.MM.yy Long date: dddd, dd' de 'MMMM' de 'yyyy Time: hh:mm tt The error is: There was a syntax error in the date format. [ Expression = 2007-10-29 12:32:33 p.m. ] The dates are entered using a datetime picker and I thought that by formatting the way I have, internationalization would be an issue. Also, the actual expression looks fine to me. Can someone help me with this please. Thanks.
Glen Harvy
-
Hi, I have always used the following coding when formatting dates for use with SQL: bookingMadeDateTime.ToString("yyyy-MM-dd hh:mm:ss tt") <-- or very similar however I have now been advised by a prospective user that they are using the following formats and the sql insert is failing: Date: dd.MM.yy Long date: dddd, dd' de 'MMMM' de 'yyyy Time: hh:mm tt The error is: There was a syntax error in the date format. [ Expression = 2007-10-29 12:32:33 p.m. ] The dates are entered using a datetime picker and I thought that by formatting the way I have, internationalization would be an issue. Also, the actual expression looks fine to me. Can someone help me with this please. Thanks.
Glen Harvy
Glen Harvy wrote:
I have always used the following coding when formatting dates for use with SQL: bookingMadeDateTime.ToString("yyyy-MM-dd hh:mm:ss tt") <-- or very similar however I have now been advised by a prospective user that they are using the following formats and the sql insert is failing
Why would you be formatting dates for SQL Statements. Surely you would be passing this as a parameter? If you didn't use parameters your code would be highly susceptable to SQL Injection attacks, which would not be good. Please read SQL Injection Attacks and Tips on How to Prevent Them[^] to secure your application. Incidentally it will also solve your current date formatting problem.
Upcoming FREE developer events: * Glasgow: SQL Server Managed Objects AND Reporting Services ... My website
-
Hi, I have always used the following coding when formatting dates for use with SQL: bookingMadeDateTime.ToString("yyyy-MM-dd hh:mm:ss tt") <-- or very similar however I have now been advised by a prospective user that they are using the following formats and the sql insert is failing: Date: dd.MM.yy Long date: dddd, dd' de 'MMMM' de 'yyyy Time: hh:mm tt The error is: There was a syntax error in the date format. [ Expression = 2007-10-29 12:32:33 p.m. ] The dates are entered using a datetime picker and I thought that by formatting the way I have, internationalization would be an issue. Also, the actual expression looks fine to me. Can someone help me with this please. Thanks.
Glen Harvy
Always insist on ISO 8601 compliant date and time formats.
-
Glen Harvy wrote:
I have always used the following coding when formatting dates for use with SQL: bookingMadeDateTime.ToString("yyyy-MM-dd hh:mm:ss tt") <-- or very similar however I have now been advised by a prospective user that they are using the following formats and the sql insert is failing
Why would you be formatting dates for SQL Statements. Surely you would be passing this as a parameter? If you didn't use parameters your code would be highly susceptable to SQL Injection attacks, which would not be good. Please read SQL Injection Attacks and Tips on How to Prevent Them[^] to secure your application. Incidentally it will also solve your current date formatting problem.
Upcoming FREE developer events: * Glasgow: SQL Server Managed Objects AND Reporting Services ... My website
Colin Angus Mackay wrote:
Why would you be formatting dates for SQL Statements. Surely you would be passing this as a parameter? If you didn't use parameters your code would be highly susceptable to SQL Injection attacks, which would not be good.
As the input is from a datetime picker and the user has no option but to select a date, I wasn't concerned with injection attacks. I am happy to rewrite the code as you suggest ie paramaterized however I don't see this as solving the problem at hand ie the user hast his computer to use non-US English. Does .Net automatically tanslate date formats?
Glen Harvy
-
Glen Harvy wrote:
I have always used the following coding when formatting dates for use with SQL: bookingMadeDateTime.ToString("yyyy-MM-dd hh:mm:ss tt") <-- or very similar however I have now been advised by a prospective user that they are using the following formats and the sql insert is failing
Why would you be formatting dates for SQL Statements. Surely you would be passing this as a parameter? If you didn't use parameters your code would be highly susceptable to SQL Injection attacks, which would not be good. Please read SQL Injection Attacks and Tips on How to Prevent Them[^] to secure your application. Incidentally it will also solve your current date formatting problem.
Upcoming FREE developer events: * Glasgow: SQL Server Managed Objects AND Reporting Services ... My website
Further to your suggestion regarding parameters, how do I add this in an Insert Statement. I currently have the following Stingbuilder:
....
commandRecDetails.Append(" VALUES ( '");
commandRecDetails.Append(bookingMadeDateTime.ToString("yyyy-MM-dd hh:mm:ss tt") + "',");
commandRecDetails.Append(" " + courtNumber + ", ");
commandRecDetails.Append(" " + thisBookingNumber.ToString() + ", '");
etc etc....
recordBookingDetails.CommandText = commandRecDetails.ToString();I can define the paramater
recordBookingDetails.Parameters.Add("DateMade", bookingMadeDateTime);
but how do I include it to replace the DateTime value in the string above? Thanks. Glen.
Glen Harvy
-
Colin Angus Mackay wrote:
Why would you be formatting dates for SQL Statements. Surely you would be passing this as a parameter? If you didn't use parameters your code would be highly susceptable to SQL Injection attacks, which would not be good.
As the input is from a datetime picker and the user has no option but to select a date, I wasn't concerned with injection attacks. I am happy to rewrite the code as you suggest ie paramaterized however I don't see this as solving the problem at hand ie the user hast his computer to use non-US English. Does .Net automatically tanslate date formats?
Glen Harvy
Glen Harvy wrote:
I am happy to rewrite the code as you suggest ie paramaterized however I don't see this as solving the problem at hand ie the user hast his computer to use non-US English. Does .Net automatically tanslate date formats?
My PC is set to use Standard English rather than US English and I have no problems. If you use parameters then you don't have to worry about date formats, you just pass a DateTime object in to the parameter and set the SQL type of the parameter to DATETIME or SMALLDATETIME and that's all you need to do.
Upcoming FREE developer events: * Glasgow: SQL Server Managed Objects AND Reporting Services ... My website
-
Further to your suggestion regarding parameters, how do I add this in an Insert Statement. I currently have the following Stingbuilder:
....
commandRecDetails.Append(" VALUES ( '");
commandRecDetails.Append(bookingMadeDateTime.ToString("yyyy-MM-dd hh:mm:ss tt") + "',");
commandRecDetails.Append(" " + courtNumber + ", ");
commandRecDetails.Append(" " + thisBookingNumber.ToString() + ", '");
etc etc....
recordBookingDetails.CommandText = commandRecDetails.ToString();I can define the paramater
recordBookingDetails.Parameters.Add("DateMade", bookingMadeDateTime);
but how do I include it to replace the DateTime value in the string above? Thanks. Glen.
Glen Harvy
Your SQL String will look like this: ...
VALUES(@dateMade, @courtNumber, @thisBookingNumber)everything beginning with an @ is a parameter name. Then add it withrecordBookingDetails.Parameters.AddWithValue("@dateMade", bookingMadeDateTime);orrecordBookingDetails.Parameters.Add("@dateMade", SqlDbType.DateTime).Value = bookingMadeDateTime;
Upcoming FREE developer events: * Glasgow: SQL Server Managed Objects AND Reporting Services ... My website
-
Glen Harvy wrote:
I am happy to rewrite the code as you suggest ie paramaterized however I don't see this as solving the problem at hand ie the user hast his computer to use non-US English. Does .Net automatically tanslate date formats?
My PC is set to use Standard English rather than US English and I have no problems. If you use parameters then you don't have to worry about date formats, you just pass a DateTime object in to the parameter and set the SQL type of the parameter to DATETIME or SMALLDATETIME and that's all you need to do.
Upcoming FREE developer events: * Glasgow: SQL Server Managed Objects AND Reporting Services ... My website
Thanks for this confirmation. I have made some changes to the program as you suggest and it certainly seems to be working OK.
Glen Harvy
-
Your SQL String will look like this: ...
VALUES(@dateMade, @courtNumber, @thisBookingNumber)everything beginning with an @ is a parameter name. Then add it withrecordBookingDetails.Parameters.AddWithValue("@dateMade", bookingMadeDateTime);orrecordBookingDetails.Parameters.Add("@dateMade", SqlDbType.DateTime).Value = bookingMadeDateTime;
Upcoming FREE developer events: * Glasgow: SQL Server Managed Objects AND Reporting Services ... My website
It doesn't get any better than this ...:-D Thanks again.
Glen Harvy