How can I convert date format of a column in oledb select statement?

[anoop s]

Hi,

In my web application I want fetch data from fixed width text file. I am using OLEDB connection for retrieving data. But the problem is the date format is dd/mm/yyyy in text file. Can I change it into mm/dd/yyyy format while fetching. Thanks in advance.

[GaryTexmo]

There's a few ways you can do this... the first way would be to just tokenize your date string and use the pieces accordingly. If your format will always be dd/mm/yyyy separated by slashes like that, you can always do something like this...

string[] dateComponents = dateTimeString.Split(new char[] { '/' });
 
DateTime theDate;
 
if (dateComponents.Length == 3)
{
  theDate = new DateTime(
    (int)dateComponents[2],
    (int)dateComponents[1],
    (int)dateComponents[0]
  );
}

(Depending on your input, you might want to use int.TryParse there)

For an alternative, have a look at this: http://msdn.microsoft.com/en-us/library/ms131044.aspx

You can use the DateTime.TryParseExact (or DateTime.ParseExact) method to specify the desired format. Here's an example...

DateTime dt;
 
if (DateTime.TryParseExact("01/02/03", "MM/dd/yy", null, System.Globalization.DateTimeStyles.None, out dt))
    Console.WriteLine("Success -- " + dt.ToString("MMM dd, yyyy"));
else
    Console.WriteLine("Failure");

[anoop s]

Hi I got answer

select FORMAT(Voucherdate,'dd/MM/yyyy')from tbl.

its working.