CODEWITHSUNDEEP
c#sqlsql-server-2008
Mono Developer
Mono Developer

Reputation: 619

Formatting SQL friendly Date/Times in C#

I am building some test scenarios for my application. In order to do this, I have some code that is generating some SQL to help me test properly. A sample of the SQL that is generated is shown here:

INSERT INTO MyTable
(
 [ID],
 [Name],
 [CheckInDate],
 [CheckOutDate]
)
VALUES
(
  1,
  'A title',
  'Sat, 17 Dec 2011 14:33:12 GMT',
  'Sat, 17 Dec 2011 15:13:12 GMT'
)

When I attempt to execute this SQL, I receive the following: Conversion failed when converting date and/or time from character string.

How can i format the date/time strings so that SQL Server 2008 will accept them? I really need my C# code to generate the SQL (including the date/time items) to create my tests properly.

Thank you!

Upvotes: 3

Views: 1174

Answers (3)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726479

A better approach would be to not format dates at all, parameterize your insert statement, and pass dates as command parameters.

Upvotes: 1

Ron Sijm
Ron Sijm

Reputation: 8738

You can use SqlParameters.

So your command would be:

INSERT INTO MyTable
(
 [ID],
 [Name],
 [CheckInDate],
 [CheckOutDate]
)
VALUES
(
  1,
  'A title',
  @CheckInDate,
  @CheckOutDate
)

And you'd insert the dates like so:

SqlParameter checkin = new SqlParameter("@CheckInDate", SqlDbType.DateTime);
SqlParameter checkout = new SqlParameter("@CheckOutDate", SqlDbType.DateTime);

checkin.Value = DateTime.Today; // Format these to the desired dates
checkout.Value = DateTime.Today;

command.Parameters.Add(checkin);
command.Parameters.Add(checkout);

Upvotes: 2

marc_s
marc_s

Reputation: 754230

The way to solve this is to use the ISO-8601 date format that is supported by SQL Server - this format works always - regardless of your SQL Server language and dateformat settings.

The ISO-8601 format is supported by SQL Server comes in two flavors:

  • YYYYMMDD for just dates (no time portion) - note here: no dashes!, that's very important! YYYY-MM-DD is NOT independent of the dateformat settings in your SQL Server and will NOT work in all situations!

or:

  • YYYY-MM-DDTHH:MM:SS for dates and times - note here: this format has dashes.

This is valid for SQL Server 2000 and newer.

If you use SQL Server 2008 and the DATE datatype (only DATE - not DATETIME!), then you can indeed also use the YYYY-MM-DD format and that will work, too, with any settings in your SQL Server.

Don't ask me why this whole topic is so tricky and somewhat confusing - that's just the way it is. But with the YYYYMMDD format, you should be fine for any version of SQL Server and for any language and dateformat setting in your SQL Server.

Upvotes: 9

Related Questions