0

currently, i have a datetime object

DateTime theDateTime = DateTime.ParseExact(dateAndTime, "d MMMM yyyy hh:mm tt", provider);

which successfully converts it into a datetime (from a string) to become for example :

7/6/2012 9:30:00 AM

How do i convert this to become 2012/07/06 09:30:00 (24hr format)? So that i can insert it into the database using C#??

PS: I'm using Sybase SQL Anywhere 12, and from what I've read, they neeed the format to be in year/months/day and the time to be in 24hr format right? Please correct me if I'm wrong.

melvg
  • 57
  • 1
  • 2
  • 12

2 Answers2

0

The DateTime itself does not have a format. The date and time are stored internally as a number. Usually the classes of the database provider take care of converting a DateTime to the correct format.

If Sybase will only accept the date formatted as a string you will need to use the DateTime.ToString method and format it with the correct format string.

How are you building your insert command? Are you using database parameters or just building a string containing the insert statement?

Andrew Kennan
  • 13,947
  • 3
  • 24
  • 33
  • Hi Andrew, I'm using a string containing insert statement. like insertAccount.CommandText = ("INSERT INTO [meetingMinutes] (title,location,perioddate,periodtime,attenders,agenda,accountID,facilitator,datetime) VALUES ('" + title + "','" + location + "', '" + date + "','" + time + "', '" + attender + "','" + agenda + "', '" + accountID + "','" + facilitator + "'," + theDateTime + ")"); I would like to try to insert it using string containing elements before changing it to parameters.. but now even using the insert string containing elements wont work.. – melvg Jul 06 '12 at 02:01
  • I really think you should use parameters from the start. There shouldn't be any reason to build SQL this way and, if you use parameters, you won't need to do any formatting of the date. – Andrew Kennan Jul 06 '12 at 02:31
  • insertAccount.Parameters.Add("title", title); insertAccount.Parameters.Add("location", location); insertAccount.Parameters.Add("date", date); insertAccount.CommandText = ("INSERT INTO [meetingMinutes] (title,location,perioddate VALUES (?,?,?(; – melvg Jul 06 '12 at 02:39
0

SQL Anywhere 12 has a default date format of YYYY-MM-DD HH:NN:SS.SSS

This can be configured/changed with the timestamp_format database option however:

timestamp_format option

The setting can be permanently changed through SQL like:

SET OPTION PUBLIC.timestamp_format = '<format here>';

Or temporarily changed (per connection basis) like:

SET TEMPORARY OPTION timestamp_format = '<format here>';

Of course, if you already have a datetime object in your code, you should be able to pass the value into a parameterized query. It doesn't have to be passed as a string.

Tyson Lewis
  • 91
  • 4