DateTime in MS SQL 2000 With C#

Abdurahman Almatrodi
Abdurahman Almatrodi used Ask the Experts™
on

I've an application developed in C# connecting to MS SQL 2000. And, some user have dd/mm/yyyy hh:mm AM format, others have mm/dd/yyyy. I got error when I try to insert an new record or work with date.

What is the shortest way to solve this issue?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
use SqlCommand and SqlParameter.
in c# part, ensure you can parse the user input into a c# date, from there it's dead easy:
    SqlCommand cmd = new SqlCommand("SELECT * FROM table_name WHERE dateTimeField > @SomeDate");
    cmd.Parameters.AddWithValue("@SomeDate", DateTime.Today);

Open in new window

Abdurahman AlmatrodiBusiness Development

Author

Commented:

I am using such as:

String.Format("INSERT INTO MyTable (RecDate) VALUES ({0}) WHERE RecID = {1}", '19/08/2009', 21)

The error is coming from SQL 2000 itself.


Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
the result of your string.format is this:
INSERT INTO MyTable (RecDate) VALUES (19/08/2009) WHERE RecID = 21

which won't work.
you need this:
String.Format("INSERT INTO MyTable (RecDate) VALUES (CONVERT(datetime, '{0}', 103) WHERE RecID = {1}", '19/08/2009', 21)

Open in new window

Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Abdurahman AlmatrodiBusiness Development

Author

Commented:

Thanks. It works well.

May you guide me to a link that explain this matter.

Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
I would start with this link, for the cast/convert function:
http://msdn.microsoft.com/en-us/library/ms187928.aspx
Abdurahman AlmatrodiBusiness Development

Author

Commented:
Dear Angel

What I need to know is how String.Format change the date.

Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial