How to remove "." in a.m. ,i just want am, dot causes to exception

Question

1.00/5 (1 vote)

See more:

Hi,

I set my system timezone to spanish,

I am getting currentdate time as: 17/02/2016 02:31:09 a.m.

due to having "." in a.m ,I am getting exception from sql database side while inserting a record. So how can I convert this time a.m to just am

I have to convert this on either C# side or SQL database query side.

I have to pass this value as a datetime varaible only.

What I have tried:

I have tried with System.DateTime.ParseExact(....) method, but it didn't give me the solution.

Posted 17-Feb-16 0:18am

Member 10714689

Add a Solution

Comments

Sascha Lefèvre 17-Feb-16 6:24am

Are you free to change the database schema?

Member 10714689 17-Feb-16 6:50am

no..

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2016-02-17T00:27:00

Solution 1

As I explained to you yesterday: don't do that.
Conversion failed when converting date and/or time from character string.[^]
All that will happen is you will get the next error coming up.
Convert it in your C# code, and pass it as a DateTime parameter value to SQL.

Posted 17-Feb-16 0:27am

OriginalGriff

Comments

Member 10714689 17-Feb-16 6:42am

thanks for reply,
But how can I change the current system datetime format? every where using .ToString() only,But I have to pass it as datetime type without . in a.m/p.m, I am able to pass it as am/AM, all the headache is with "a.m" format, Plz suggest me in this regard

OriginalGriff 17-Feb-16 7:01am

Don't even try. All you will do is annoy the heck out of people.

Stop converting DateTime objects to strings - you only do that at the last possible moment for presentation to the user, as you want it presented in the format he expects. Which may not be the format you inserted into a database!
Always convert to DateTime as soon as possible - while you have access to the user culture info - and to string as late as possible.
Remember, the SQL server is likely to be on a different machine - even on a different continent - and the local default date format is not known or relevant!
If you must pass string based Dates to SQL - and it's always a bad idea - then you should only ever send them in ISO 8601 format: yyyy-MM-dd hh:mm:ss.dddd
If you don't, it doesn't know if 01/02/03 is 1st Feb 3003, 2nd Jan 2003, 3rd of Feb 2001 - and it will always assume the wrong value! :laugh:
And the problem with that is you don't find out until your database is chock full of rubbish and you have no way of knowing absolutely which ones are right and which are wrong...
I know it seems like a lot of effort to change existing code to use the right thing, but it's the only sensible thing to do in the long term. Unless you like sitting down for a month trying to manually work out if each date is actually correct, of course...

*Suvabrata Roy* · Answer 2 · 2016-02-17T00:31:00

Hi,

Go through this link : https://msdn.microsoft.com/en-us/library/5hh873ya(v=vs.90).aspx[^]

Quote:
You can retrieve the format strings from the CultureInfo DateTimeFormat property, which is a DateTimeFormatInfo instance. This in turn has properties like ShortDatePattern and ShortTimePattern, containing the format strings:

CultureInfo us = new CultureInfo("en-US");
string shortUsDateFormatString = us.DateTimeFormat.ShortDatePattern;
string shortUsTimeFormatString = us.DateTimeFormat.ShortTimePattern;

CultureInfo uk = new CultureInfo("en-GB");
string shortUkDateFormatString = uk.DateTimeFormat.ShortDatePattern;
string shortUkTimeFormatString = uk.DateTimeFormat.ShortTimePattern;
If you simply want to format the date/time using the CultureInfo, pass it in as your IFormatter when converting the DateTime to a string, using the ToString method:

string us = myDate.ToString(new CultureInfo("en-US"));
string uk = myDate.ToString(new CultureInfo("en-GB"));

ref : c# - How can I get date and time formats based on Culture Info? - Stack Overflow[^]

F-ES Sitecore · Answer 3 · 2016-02-17T00:58:00

Solution 3

C#

string dateAsString = "17/02/2016 02:31:09 a.m";
DateTime dt = DateTime.ParseExact(dateAsString.Replace(".m", "m"), "dd/MM/yyyy hh:mm:ss tt", System.Globalization.CultureInfo.CurrentCulture);

Posted 17-Feb-16 0:58am

F-ES Sitecore