How to select record where from date and todate between two dates but todate(last) missing

Question

0.00/5 (No votes)

See more:

Hi Friends,

between query missing last day records.
my query like this..

SQL

select DATEADD(dd, 0, DATEDIFF(dd, 0,SubmittedOn)) as WorkingDate,DATENAME(dw, SubmittedOn) as DaName,
  ClientID,EmployeeID
 from dbo.tblEmployeeTimeCards where
  
  --and  SubmittedOn >=@From AND SubmittedOn <=@To
and   SubmittedOn between DATEADD(dd, 0, DATEDIFF(dd, 0,@From)) AND DATEADD(dd,0, DATEDIFF(dd, 0,@To))

am trying different ways but am not get perfect output. in my output missing last date records

fromdate---'3/29/2013 12:00:00'
todate---'4/11/2013 23:59:00'

output
--------------
3/29/2013 01:00:00
3/30/2013 01:00:00
4/1/2013 01:00:00
4/2/2013 01:00:00
4/3/2013 01:00:00
4/4/2013 01:00:00
4/10/2013 01:00:00

but not showing 4/11/2013 record

please help me..

thanks and regards

Posted 11-Apr-13 21:34pm

Santhosh23

Add a Solution

Comments

Dnyaneshwar Kondbale 12-Apr-13 3:43am

select DATEADD(dd, 0, DATEDIFF(dd, 0,SubmittedOn)) as WorkingDate,DATENAME(dw, SubmittedOn) as DaName,
ClientID,EmployeeID
from dbo.tblEmployeeTimeCards where

and convert(Datetime,convert(char(10),SubmittedOn,103),103) between convert(Datetime,convert(char(10),@From,103),103) AND convert(Datetime,convert(char(10),@To,103),103)

Santhosh23 12-Apr-13 7:06am

thanks dude.. its working fine..

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)

Aarti Meswania · Answer 1 · 2013-04-11T22:28:00

Solution 1

Try this

SQL

select DATEADD(dd, 0, DATEDIFF(dd, 0,SubmittedOn)) as WorkingDate,DATENAME(dw, SubmittedOn) as DaName,
  ClientID,EmployeeID
 from dbo.tblEmployeeTimeCards where
  
  --and  SubmittedOn >=@From AND SubmittedOn <=@To
and   convert(datetime,convert(varchar(10),SubmittedOn,103)) between convert(datetime,convert(varchar(10),@From,103)) AND convert(datetime,convert(varchar(10),@To,103))

Happy Coding!
:)

Posted 11-Apr-13 22:28pm

Aarti Meswania

Comments

Santhosh23 12-Apr-13 4:57am

hi Aarti..
thanks for your replay.
just now execute but i got the error

Msg 242, Level 16, State 3, Procedure USP_GetTimeCardReportForEmployee, Line 254
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Aarti Meswania 12-Apr-13 5:00am

@From and @To and SubmittedOn
datatype must be datetime

so you should pass
@Form and @To in 'yyyy-MM-dd hh:mm:ss' format
and
SubmittedTo in sql table should have datatype datetime

Santhosh23 12-Apr-13 7:03am

yes.. my colomn datatype is datetime.. but i got the error..

Aarti Meswania 12-Apr-13 7:06am

what about @Form and @To in 'yyyy-MM-dd hh:mm:ss' format

Santhosh23 12-Apr-13 7:07am

convert(Datetime,convert(char(10),SubmittedOn,103),103) between convert(Datetime,convert(char(10),@From,103),103) AND convert(Datetime,convert(char(10),@To,103),103)

its working fine

Aarti Meswania 12-Apr-13 7:12am

set
Pass
@From = '2013=03-29 12:00:00'
@To = '2013-04-11 23:59:00'