Date issue in SQL server using ASP.NET MVC C#

Question

0.00/5 (No votes)

See more:

i face date issue to get value from sql server using asp.net mvc c#
it work fine on date 12 and after 12 it show error
sql server date formate is 2020-03-27

What I have tried:

this code work fine on date 12 and show error after 12, i think problem is in date format

DateTime fromDate = DateTime.ParseExact(date, "dd/MM/yyyy", CultureInfo.InvariantCulture);

Posted 30-Mar-20 7:52am

mtufail

Updated 30-Mar-20 19:26pm

Add a Solution

Comments

Maciej Los 30-Mar-20 14:42pm

Replace "dd/MM/yyyy" with "yyyy-MM-dd" and let me know if it helped.
[EDIT]
Can you show the method you use to get data from SQL Server?

2 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)

phil.o · Answer 1 · 2020-03-30T08:05:00

Solution 1

If you are using a datetime datatype in SQL, it has no notion of format. It just stores the datetime information as a numeric value, the format only becomes relevant when presenting the data to the end user; thus, in the ASP.NET side.

If you are storing the datetime values as string, then you are not using the proper datatype and you will run into several difficulties sooner or later. You should change that as soon as possible, or be prepared to poor performances of your queries on filtering/grouping by date fields. If you still don't want to use the proper datatype, then you will have to match the format in the database with the format of the ParseExact method. In this case, try

C#

DateTime fromDate = DateTime.ParseExact(date, "yyyy/MM/dd", CultureInfo.InvariantCulture);

instead.

Posted 30-Mar-20 8:05am

phil.o

Comments

mtufail 30-Mar-20 14:12pm

In SQL server i use date datatype.
When i use formate "yyyy/MM/dd" it show error on both before and after 12 date
when i use formate "dd/MM/yyyy" it show error after date 12 and work fine on or before 12 date

phil.o 30-Mar-20 14:19pm

Then use "MM/dd/yyyy" format for ParseExact.

Richard Deeming 1-Apr-20 14:10pm

If you're using the date datatype in SQL, then you shouldn't need to use Parse or ParseExact at all. You can just cast the returned value directly to a DateTime.

mtufail 30-Mar-20 14:25pm

when i use "MM/dd/yyyy" it work on or before 12 but not show data and give error after 12 date.
When i use "dd/MM/yyyy" it work fine on or before 12 date and also show data but give error after 12 date.

mtufail 30-Mar-20 14:47pm

"dd/MM/yyyy" work fine on local server and also work fine when i use sql server ip, username and password directly with my application. it show error on live web after date 12

phil.o 30-Mar-20 16:13pm

Please show the error message as well as the line of code triggering it.

Vijay Ahlawat · Answer 2 · 2020-03-30T19:28:00

There is no issue in code.
The issue is that you local machine and server have different datetime format.
Please change the datetime format for you server as your local machine then code will work fine.
Issue is server is considering you day as month and this is because of different date time format.