Date is not taking proper format as Expected (dd/MM/yyyy)

Question

I want to insert date into database in dd/MM/yyyy format. For that I have written like below:

drExpInfo[0]["CHEQUE_DT"] = string.IsNullOrWhiteSpace(e.Record["CHEQUE_DT"].ToString())
    ? DBNull.Value : (object)Convert.ToDateTime(e.Record["CHEQUE_DT"]);

And it is working perfectly fine on my local machine, but on my server it is taking format as dd/MM/yyyy hh:mm:ss. So how to set the same format there too. Kindly suggest.

What DBMS are you using? And what type is `CHEQUE_DT` in db? — M. Wiśnicki, Jan 24 '17 at 09:52
@M.Wiśnicki: I am using `Oracle` sorry forgot to tag that. and `cheque_dt` is date datatype — Nad, Jan 24 '17 at 09:53
Don't store dates as a *string* that *has* a format. Store them in an appropriate data type in your database (e.g. `datetime`). Such types don't *have* formats. — Damien_The_Unbeliever, Jan 24 '17 at 09:53
@Damien_The_Unbeliever; thanks for the suggestion. I am using `date` in db. I just want to customize here in my code. how to handle it here — Nad, Jan 24 '17 at 09:55
So, immediately after the date is input, parse it into a .NET `DateTime`. Then, use parameters when sending it *to* the database (and rely on ADO.Net and the ODP to perform appropriate transforms to Oracle's `date`) and retrieve it back from the database back into a `DateTime` and, only at the point of actually displaying it back to the user should you transform it back into a string. At that point, you can pick whatever format you like - but it's translating it (inappropriately) into strings when you don't *need* a string that **leads** to formatting errors. — Damien_The_Unbeliever, Jan 24 '17 at 09:57
The question appears to be about C#'s formatting of dates as strings (or, if more properly implemented, passing a date to a parameterized SQL query) and the database being used is tangential to the question (i.e. the same technique will be used regardless of the RDBMS). As such, I've removed the Oracle tag. If you decide to edit the question include the SQL query to actually make it RDBMS specific then you can reapply the tag. — MT0, Jan 24 '17 at 10:14

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

Use DateTime.ToShortDateString:

drExpInfo[0]["CHEQUE_DT"] = string.IsNullOrWhiteSpace(e.Record["CHEQUE_DT"].ToString())
    ? DBNull.Value : (object)Convert.ToDateTime(e.Record["CHEQUE_DT"]).ToShortDateString();

But, I suggest you keep the time part of your date (without using the ToShortDateString as it will insert a time set to midnight, cf. highlighted text in Oracle documentation below) when inserting and get the format you want (without time) when you're using the date.

From Oracle documentation:

Oracle Database automatically converts character values that are in the default date format into date values when they are used in date expressions.

If you specify a date value without a time component, then the default time is midnight. If you specify a date value without a date, then the default date is the first day of the current month.

Oracle Database DATE columns always contain fields for both date and time. If your queries use a date format without a time portion, then you must ensure that the time fields in the DATE column are set to midnight. You can use the TRUNC (date) SQL function to ensure that the time fields are set to midnight, or you can make the query a test of greater than or less than (<, <=, >=, or >) instead of equality or inequality (= or !=). Otherwise, Oracle Database may not return the query results you expect.

So you can convert you're date in any format you want in c#, removing the time part, Oracle will automatically set the time part of your date as midnight (00:00:00).

Because your oracle type is a date, so it should insert the time part as 00:00:00. Am I correct? I suggest you keep the time part. — krlzlx, Jan 24 '17 at 10:10

Eldaniz Ismayilov · Answer 2 · 2017-01-24T10:37:56.600

0

Try:

public static DateTime? DateFromString(string value)
{
   if (string.IsNullOrWhiteSpace(value))
     {
       return null;
     }
   else
     {
        return DateTime.ParseExact(dateString, "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture);
     }
}

DateTime? date=DateFromString(e.Record["CHEQUE_DT"]);
drExpInfo[0]["CHEQUE_DT"] = date==null? DBNull.Value : (object)date);

edited Jan 24 '17 at 10:37

answered Jan 24 '17 at 09:52

Eldaniz Ismayilov

856
6
18

The best overload method has some invalid arguments at line `DateTime? date= DateFromString(e.Record["CHEQUE_DT"];` – Nad Jan 24 '17 at 10:04

Mehmet · Answer 3 · 2017-01-24T09:55:02.170

0

You can set CultureInfo;

var cultureInfo = new CultureInfo("en-GB", false).DateTimeFormat;
string result = Convert.ToDateTime(e.Record["CHEQUE_DT"], cultureInfo).ToString();

edited Jan 24 '17 at 09:55

answered Jan 24 '17 at 09:52

Mehmet

1,824
3
19
28

sorry I didnt try code. But you can add ToString() or result as var – Mehmet Jan 24 '17 at 09:55
I also need to check for NULL also. if no date is provided then your code will throw an error – Nad Jan 24 '17 at 09:55

score 0 · Answer 4 · edited May 23 '17 at 12:08

0

Check this Discussion Here you can find two or more ways to setting date format while insert records into database.

insert into sampleDate(Start_Date) values (to_date('25-Jun-2017','YYYYMMDD'))

edited May 23 '17 at 12:08

Community

1
1

answered Jan 24 '17 at 10:02

Kathir07

231
2
3

insert into sampleDate(Start_Date) values (to_date('25-Jun-2017','YYYYMMDD')) – Kathir07 Jan 24 '17 at 10:05
2

This is a link only answer; it should be a comment on the question or include the relevant parts in the answer. However, the linked question relates to SQL Developer and not to formatting dates in C# and appears to only be tangentially relevant. – MT0 Jan 24 '17 at 10:07
1

@Kathir07 You can edit and improve your answer instead of commenting it. – wmk Jan 24 '17 at 10:22

Date is not taking proper format as Expected (dd/MM/yyyy)

4 Answers4