Datetime field format issue

Question

0.00/5 (No votes)

See more:

C#

i am saving 'patientadmissiondate' field as DateTime.Now;. When i view table data i see the values in this column in this format '11/24/2015 10:46:05 AM'.. 
however when i do select * from tbpatientadmissiondate i see the values in this column as '2015-11-24 10:46:05.443'
Due to this i am having issue returning the records which match a particular 'patientadmissiondate' .Please guide!

Posted 23-Nov-15 18:31pm

Member 12156793

Add a Solution

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)

deepankarbhatnagar · Answer 1 · 2015-11-23T20:11:00

Solution 2

Hi, Try this,

SQL

select CONVERT(VARCHAR(50),GETDATE(),22)

It will gie the result as

11/24/15 12:39:11 PM

Please see the links for furture formats which us can use for your requirement.

http://www.sql-server-helper.com/sql-server-2012/format-function-vs-convert-function.aspx[^]

http://www.sqlusa.com/bestpractices/datetimeconversion/[^]

Posted 23-Nov-15 20:11pm

deepankarbhatnagar

Comments

Member 12156793 24-Nov-15 2:15am

plz explain.. in sql table patientadmissiondate field in datetime format..

deepankarbhatnagar 24-Nov-15 2:27am

select CONVERT(VARCHAR(50),patientadmissiondate ,22)

Hope this will solve your query

sanjay243657 · Answer 2 · 2015-11-23T19:02:00

Solution 1

Hi ,


By Default Sql server saving datetime in yyyy-mm-dd hh:mm:ss formate.

if you have issue in date formate while retrieving  you can change date formate and compare with patientadmissiondate.

e.g.

SQL

select CONVERT(varchar(10), CONVERT(datetime, GETDATE(), 101), 101)

in C# Side :-

C#

Please try Below code

  var rep = from s in ctx.tbreports
           where s.dateofreport.ToString("yyyy-MM-dd") ==                  date.ToString("yyyy-MM-dd")
                      select s;

Posted 23-Nov-15 19:02pm

sanjay243657

Updated 23-Nov-15 21:10pm

v3

Comments

Member 12156793 24-Nov-15 1:35am

Please guide how can i keep data in 11/24/2015 10:46:05 AM format in sql field

sanjay243657 24-Nov-15 1:42am

i have updated the solution please check..

Member 12156793 24-Nov-15 1:49am

thanks for the update. However, i still am not able to do the comparison as using the above update i now get the date in this format i.e 11/24/2015.. i need it in this format 11/24/2015 10:46:05 AM. what change is required for that and Do i change the query where i insert date in database?

sanjay243657 24-Nov-15 1:55am

where you are comparing date in sql side or C# side...

Member 12156793 24-Nov-15 2:07am

c#(MVC) var rep = from s in ctx.tbreports
where s.dateofreport == date
select s;

sanjay243657 24-Nov-15 3:11am

i have updated solution Please check..

Member 12156793 24-Nov-15 4:47am

linq does not allow such conversion