how to validate the date in sql server

Question

2.00/5 (1 vote)

See more:

Table as follows

Scheduledate               Description    Mobile     Status
2014-02-06 16:48:09.000       AFF           xxxx       Yes
2014-02-07 16:48:09.000       MFA           xxxx       Yes

From the above table i want to view Scheduledate (date from 6th and 7th date)
for that i written query as follows

SQL

select Scheduledate,Description,Mobile,Status from Shortcode_Course_SMS where Msgdelivered > '20140205' and Msgdelivered < '20140208'

When i execute the above query output as follows

Scheduledate               Description    Mobile     Status
2014-02-06 16:48:09.000       AFF           xxxx       Yes
2014-02-07 16:48:09.000       MFA           xxxx       Yes

But when i write the above query as follows

SQL

select Scheduledate,Description,Mobile,Replymsg  from Shortcode_Course_SMS where Msgdelivered = '20140206' and Msgdelivered = '20140206'

i want to view particular date for example 6th date(2014-02-06 16:48:09.000)
When i run above query output is empty no record is shown

Scheduledate               Description    Mobile     Status

SQL

select Scheduledate,Description,Mobile,Replymsg  from Shortcode_Course_SMS where Msgdelivered = '20140206' and Msgdelivered = '20140206'

from my above query what is the problem please help me.
Regards,
Narasiman P.

Posted 14-Apr-14 20:56pm

Member 8054539

Updated 14-Apr-14 21:03pm

Aarti Meswania

v2

Add a Solution

Comments

Bernhard Hiller 15-Apr-14 3:25am

You hide somethings from us. You do not show us a column "Msgdelivered" - how do you expect us to be able to help you when you do not provide the information?

1 solution

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 · 2014-04-14T21:25:00

There are two reasons: first your AND condition, which potentially requires that a date has two different values at the same time (I would suggest you want OR instead)

Second because "=" needs exactly the same value on each side - and since these are DATETIME values that means they need to be the same right down to the microsecond. Since you don;t specify a time, '20140206' is midnight on the 6th Feb, which does not match 16:48:09.000 on the 6th Feb.

You can change your query to use

SQL

SELECT Scheduledate,Description,Mobile,Replymsg 
FROM Shortcode_Course_SMS 
WHERE DATEADD(dd, 0, DATEDIFF(dd, 0, Msgdelivered)) = '20140206' 
   OR DATEADD(dd, 0, DATEDIFF(dd, 0, Msgdelivered)) = '20140207'

There is also a better way to write your first query:

SQL

FROM Shortcode_Course_SMS
WHERE Msgdelivered BETWEEN '20140205' AND '20140208'