1

I have a DateTime variable (say, timestamp) that holds a date in its usual format like this:

11/1/2011

This variable is used to build a SQL command. The Oracle database only accepts dates in the format

YYYY-MM-DD

How can I manipulate my variable to store the date in this format?

MPelletier
  • 16,256
  • 15
  • 86
  • 137
Ayush
  • 41,754
  • 51
  • 164
  • 239
  • 2
    Is the variable a `DateTime` or a `string`? `DateTime`s "hold" their dates as 64-bit integers, not in a textual format. – Ani Jan 07 '11 at 19:16

4 Answers4

8

Don't format the date to include it in SQL at all.

Use a parameterized query, and then just include the value as a parameter. That way you don't have to get any formatting right at all.

You should use parameterized queries for all data - aside from formatting, it also protects you from SQL injection attacks.

Getting a date/time format which works for the particular installation of Oracle you're using right now is not the right fix. Do it properly: avoid including data in your code (the SQL).

On a different matter, your question is making incorrect assumptions to start with. A DateTime variable doesn't hold value in a "usual format" at all, any more than an int holds a decimal representation or a hex representation of a number. DateTime doesn't store text internally at all - it stores a number of ticks. How it is formatted when you call ToString depends on all kinds of cultural aspects. It's worth separating the notion of the fundamental value represented by a type from the formatted string representation you might happen to obtain by calling ToString.

Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194
  • Thanks. I'll look into this and try to incorporate it into my code – Ayush Jan 07 '11 at 19:27
  • 1
    +1 Brilliant! This is by far the smartest, easiest way to go. – MPelletier Jan 07 '11 at 19:27
  • Good answer Jon, but I think it's worth pointing out the existence of custom formats that can be passed to `DateTime.ToString`. It might help clear up the confusion the OP is experiencing. – jason Jan 07 '11 at 19:27
  • 1
    @Jason: I would have done so if that were the best way to go. It's probably the *fastest* way that the OP can get to "working" code at the moment, but I don't want to encourage bad practice by giving a short-term fix when the more appropriate solution is parameterized queries. Oh, and there are already three other formatting answers, so it wouldn't be adding much there, either :) – Jon Skeet Jan 07 '11 at 19:29
  • @Jon Skeet: Good point. I'll keep that idea in mind in the future when answering questions myself. – jason Jan 07 '11 at 19:30
  • @Jason: Well, don't take it as gospel or anything. On any other given day I *might* have included the formatting bit :) (as well, of course... never instead). – Jon Skeet Jan 07 '11 at 19:31
4

I assume you send the date as string in the SQL command.

DateTime date = ...your object...;
string formattedDate = date.ToString("yyyy-MM-dd");

If it´s in string format, then you need to parse it first. It´s hard to see from your string if it´s day/month/year or month/day/year.

But you could do something like this:

string sDateTime = "11/1/2011";
DateTimeFormatInfo format = new DateTimeFormatInfo();
format.ShortDatePattern = "dd/MM/yyyy"; // or MM/dd/yyyy
DateTime date = DateTime.Parse(sDateTime, format);
string formattedDate = date.ToString("yyyy-MM-dd");
Mikael Svenson
  • 39,181
  • 7
  • 73
  • 79
  • Thanks. This solves my problem. I know this is an easy fix though, and I will eventually incorporate parameterized queries. – Ayush Jan 07 '11 at 19:32
  • The available format strings like Mikael's "yyyy-MM-dd" for .Net are documented here: http://msdn.microsoft.com/en-us/library/8kb3ddd4.aspx – dumbledad Feb 21 '13 at 10:40
1
var dt = DateTime.Now;
var formatted = dt.ToString("yyyy-MM-dd");
WayneC
  • 2,530
  • 3
  • 31
  • 44
1

Try this:

string oracleTimeFomatDate = DateTime.Now.ToString("yyyy-MM-dd")
BrokenGlass
  • 158,293
  • 28
  • 286
  • 335