CODEWITHSUNDEEP
c#asp.netsql-server
sam
sam

Reputation: 105

How to get current system date and time without am/pm Designators using c#

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.

Upvotes: 0

Views: 1973

Answers (3)

Asons
Asons

Reputation: 87191

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.

Upvotes: 4

W.sn0w
W.sn0w

Reputation: 3

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.

Upvotes: 0

George Harnwell
George Harnwell

Reputation: 804

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

Upvotes: 0

Related Questions