0

I set my system region settings to "spanish", So I'm getting the time format like: 20/02/2016 02:39:40 a.m. ;I have stored this value in datetime variable and passed to sql database table. I set my database default language also to "spanish", at the time of inserting datetime varaible..getting exception

conversion failed when converting date and/or time from character string

due to having "." (preiod) in a.m./p.m. I am getting this exception. I searched a lot on this issue, every where converting the datetime varaible to string format.But I should pass this as a datetime variable only.

So please let me know how to get current system datetime without am/pm or without having "." in am/pm

 //Here is my code:

    DomainSchema.DOMAINRow drDomain = domain_dataset.DOMAIN.NewDOMAINRow();
    //Assigning the values to the DataRow of the table Domain
       drDomain.CREATED_DATE = DateTime.Now;
       drDomain.LAST_UPDT_DATE = DateTime.Now;
       domain_dataset.DOMAIN.AddDOMAINRow(drDomain);
     CultureInfo i;                

  i = (CultureInfo)System.Threading.Thread.CurrentThread.CurrentCulture.Clone();
                if (i.Name == "es-PE")
                {
                   CultureInfo c = CultureInfo.CreateSpecificCulture("es-ES");
         String s = DateTime.Now.ToString(c); //getting s="20/02/2016 08:07:54"
   DateTime d = DateTime.Parse(s, c, System.Globalization.DateTimeStyles.AssumeLocal); //geting d="20/02/2016 08:07:54 a.m."
}

    //method that uses this dataset

    public override string Create(DataSet ds, string tableName, IDbTransaction transaction, IPrincipal user)
            {
                DataRow dRow = null;
                CommandStatement cmdStatemnt;
                string strResult = string.Empty;
                try
                {
                  dRow = ds.Tables[tableName].Rows[0];
            cmdStatemnt = Execute(dRow, tableName, transaction, true, null, user); 
                   //Here I'm getting exception,conversion failed when converting date and/or time from character string,due to having "." in "a.m." 
                }

        }

thanks in advance.

sam
  • 95
  • 1
  • 2
  • 13

3 Answers3

4

When passing date/time to databases I always use this

Datetime d = DateTime.Today;

String s = d.ToString("MM/dd/yyyy hh:mm:ss tt", CultureInfo.InvariantCulture);
// output   "02/20/2016 12:00:00 AM"

String s = d.ToString("MM/dd/yyyy HH:mm:ss", CultureInfo.InvariantCulture);
// output   "02/20/2016 00:00:00"

Using capitals HH gives 24-hour format

Here you can find a lot more regarding date time formatting

Update based on comments

In the situation described, you should be able to do something like this.

CultureInfo c = CultureInfo.CreateSpecificCulture("es-ES");
String s = DateTime.Now.ToString(c);
DateTime d = DateTime.Parse(s, c, System.Globalization.DateTimeStyles.AssumeLocal);

2nd update based on comments

2 more ways could be:

  • Toggle the culture when running the database transaction

    // Switch Current Culture
Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");

// run database code

// Switch Back To Origin Current Culture
Thread.CurrentThread.CurrentCulture = new CultureInfo("es-ES");

  • Change your dataset object and have the CREATED_DATE typed as string instead of datetime

    After all, when you execute a SQL query, datetime's are treated as string (quoted) in the query, even if the field they are stored in is of datetime type.

Asons
  • 84,923
  • 12
  • 110
  • 165
  • thanks for the reply, I am getting the value like:CREATED_DATE = DateTime.Now; //Here CREATED_DATE is datetime type in Schema which I can't change, So I can't pass value for this as string type. It throws exception. – sam Feb 20 '16 at 11:22
  • @sam The CREATED_DATE, is that a property in a class you use for the actual database read/write? – Asons Feb 20 '16 at 11:31
  • @sam I updated my answer with a suggestion. Also, could you please update your question with the commented code, as comments can be removed and then that info gets lost. – Asons Feb 20 '16 at 12:41
  • Yes, I have tried, the outputs that I got at these steps: – sam Feb 20 '16 at 13:11
  • In value "d", I am getting again datetime with a.m. – sam Feb 20 '16 at 13:13
  • I updated my question with your given suggestion, please check once – sam Feb 20 '16 at 13:23
  • @sam With what I know now, I can only see 2 ways; ...1. toggle the [culture at runtime](http://stackoverflow.com/questions/7000509/how-to-change-currentculture-at-runtime) ... 2. change your dataset date's to string (in a sql query, string is the normal way to write date to database) – Asons Feb 20 '16 at 13:34
  • Yes..switch the current culture method works for me.. thanks a lot Son – sam Feb 22 '16 at 07:14
0

How are you getting that datetime value? Are you doing it like: var dt = Datetime.Now;

George Harnwell
  • 784
  • 2
  • 9
  • 19
  • CREATED_DATE = DateTime.Now; //here CREATED_DATE is datetime type in schema which i can't change, so i don't have option to convert it as string. – sam Feb 20 '16 at 11:19
0

u can using DateTime.Now.ToString("your formate") ,so your time become string type,last, u can insert into database that convert this string to date type with a sql function.

W.sn0w
  • 3
  • 2