how to remove am/pm from a datetime in sql server

Question

0.00/5 (No votes)

See more:

I have following query

C#

string query1 = "select sum(total) from dbo.total_salary where user_id=@userid and paydate>=convert(datetime,'" + DateFrom + "') and paydate<=convert(datetime,'" + DateTo + "')";

where datefrom and dateto are parameters..
On debugging I get value in datefrom and dateto parameter as

C#

select sum(total) as total from dbo.total_salary where user_id='greencity' and paydate>(convert(datetime,'2/2/2014 12:00:00 AM')) and paydate<=(convert(datetime, '2/28/2014 12:00:00 AM'))

But it returns null..
when in sql I execute query as

SQL

select sum(total) as total from dbo.total_salary where user_id='greencity' and paydate>(convert(datetime,'2/2/2014 12:00:00')) and paydate<=(convert(datetime, '2/28/2014 12:00:00'))

It returns value..
So plz help me how to remove am/pm part in datetime

Posted 26-Feb-14 23:21pm

heta.dave

Updated 26-Feb-14 23:25pm

Snehasish_Nandy

v2

Add a Solution

3 solutions

Solution 3

In my opinion, SQL Server should convert it implicity, without using CONVERT and CAST[^] function (explicity).

Try this:

SQL

DECLARE @sdate VARCHAR(30)

SET @sdate = '2/28/2014 12:00:00 AM'

SELECT MyDate
FROM (
    SELECT CONVERT(DATETIME,'2/27/2014 12:00:00 PM') AS MyDate UNION ALL
    SELECT CONVERT(DATETIME,'2/28/2014 12:00:00 AM') AS MyDate UNION ALL
    SELECT CONVERT(DATETIME,'1/01/2014 12:00:00 AM') AS MyDate UNION ALL
    SELECT CONVERT(DATETIME,'12/31/2014 12:00:00 AM') AS MyDate UNION ALL
    SELECT CONVERT(DATETIME,'2/22/2014 12:00:00 AM') AS MyDate
) AS T
WHERE MyDate <= @sdate

On the other hand, i would suggest you to use stored procedure[^] instead construct sql command in code, because of SQL Injection[^].

For further information, please see: How to: Execute a Stored Procedure that Returns Rows[^]
How To: Protect From SQL Injection in ASP.NET[^]
Stop SQL Injection Attacks Before They Stop You[^]

Use SearchBox at the top-right corner of this site to find more information about that.

Posted 27-Feb-14 1:56am

Maciej Los

Comments

Peter Leow 27-Feb-14 10:21am

Best! +5.

Maciej Los 27-Feb-14 10:54am

Thank you, Peter ;)

Solution 1

You could either use a Parameterized query (strongly recommended), or format your date in SQL friendly format:

C#

string query1 = "SELECT SUM(total) FROM dbo.total_salary WHERE user_id=@userid AND paydate BETWEEN '" + DateFrom.ToString("yyyy-MM-dd") + "' AND '" + DateTo.ToString("yyyy-MM-dd") + "'";

Posted 26-Feb-14 23:29pm

OriginalGriff

Comments

heta.dave 27-Feb-14 5:35am

it not works..

OriginalGriff 27-Feb-14 6:01am

"it not works..." is a pointless thing to say.
If your car breaks down in the middle of nowhere, do you ring the garage, say "it no works.." and put the phone down? And if you did, how long are you going to be waiting until they arrive with exactly the parts to fix it?

Tell us what it does that you didn't expect, or doesn't do that you did!
Give us error messages, data, information!

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)

Aravindba · Accepted Answer · 2014-02-26T23:33:00

Hi u need to avoid time also,then it better to get result,try to search date time convert,u are try to get employee salary date,so no need time,if it is employee or student login or logout then u use time.

http://www.w3schools.com/sql/func_convert.asp

SQL Server Functions that helps to convert date and time values to and from string literals and other date and time formats.[^]