Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL Server
Hi Friends,
 
I have one table and columns are fromtime(datetime) and totime(datetime) and etc...
 
how to get the records between two date&time. my
query like this
 
select * from tblEmployeeTimeCards where
 ((FromTime between Convert(datetime,'From-Time',103) and Convert(datetime,'ToTime',103)) or (ToTime between Convert(datetime,'FormTime',103) and Convert(datetime,'Totime',103)))
 
but not wroking perfectly
 
please give me any ideas
 
thanking you
Posted 8-Apr-13 7:02am
Comments
Sergey Alexandrovich Kryukov at 8-Apr-13 12:22pm
   
"Not working perfectly" is not informative.
—SA
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Try this:
SELECT * FROM tblEmployeeTimeCards 
WHERE 
CONVERT(DATETIME, dtcreated) >= fromdate AND 
CONVERT(DATETIME, dtcreated)<= todate
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Better help could be provided if you included DDL for pertinent columns in the database tables, real samples of data in the database table and real samples of 'FormTime' and 'Totime'. Also, you did not say what the problem was. "Not working perfectly" could mean a syntax error or the wrong rows are returned or who knows what?
 
My examples below use SQL Parameters instead of literals in the query string. Better performance and prevents SQL Injection attacks.
 
The two examples below are equivalent. One uses the BETWEEN operator and one does not.
 
Rows with FromTime >= fromdatetime-parameter and FromTime <= todatetime-parameter
OR
ToTime >= fromdatetime-parameter and ToTime <= todatetime-parameter
 
Dim cn As SqlConnection = New SqlConnection(connectionString)
Dim obCommand As SqlCommand = New SqlCommand("select * from tblEmployeeTimeCards " & _
"Where " & _
(FromTime BETWEEN @FromDateTime AND @ToDateTime) OR " & _
(ToTime BETWEEN @FromDateTime AND @ToDateTime) ;", cn)
obCommand.Parameters.AddWithValue("FromDateTime", CDate("2013-03-01 08:00:00 AM"))
obCommand.Parameters.AddWithValue("ToDateTime", CDate("2013-03-01 10:59:59 AM"))
Dim rs As SqlDataReader = obCommand.ExecuteReader
 

Dim cn As SqlConnection = New SqlConnection(connectionString)
Dim obCommand As SqlCommand = New SqlCommand("select * from tblEmployeeTimeCards " & _
"Where " & _
(FromTime>=@FromDateTime AND FromTime<=@ToDateTime) OR " & _
(ToTime>=@FromDateTime AND FromTime<=@ToDateTime) ;", cn)
obCommand.Parameters.AddWithValue("FromDateTime", CDate("2013-03-01 08:00:00 AM"))
obCommand.Parameters.AddWithValue("ToDateTime", CDate("2013-03-01 10:59:59 AM"))
Dim rs As SqlDataReader = obCommand.ExecuteReader
  Permalink  
v5
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

(((FromTime BETWEEN DATEADD(hh, DATEDIFF(hh, 0,'2013-04-21 07:30:00'), 0)and  --'2013-04-21 07:30:00'
 DATEADD(hh, DATEDIFF(hh, 0,'2013-04-21 13:00:00'), 0))--@To
or
(FromTime <= DATEADD(hh, DATEDIFF(hh, 0,'2013-04-21 07:30:00' ), 0)and  --From
ToTime >= DATEADD(hh, DATEDIFF(hh, 0,'2013-04-21 07:30:00' ), 0)) --From
  )
or
(ToTime BETWEEN DATEADD(hh, DATEDIFF(hh, 0, '2013-04-21 07:30:00'), 0)AND --From
 DATEADD(hh, DATEDIFF(hh, 0, '2013-04-21 11:30:00'), 0) --To
or
(FromTime <= DATEADD(hh, DATEDIFF(hh, 0,'2013-04-21 13:00:00'), 0) --To
 and ToTime >= DATEADD(hh, DATEDIFF(hh, 0, '2013-04-21 13:00:00'), 0)) --To
))
 
 AND
 (( (FromTime BeTween DATEADD(MINUTE, DATEDIFF(MINUTE, 0,'2013-04-21 07:30:00' ), 0)and  --From
    DATEADD(MINUTE, DATEDIFF(MINUTE, 0,'2013-04-21 13:00:00' ), 0)--To
    or
     FromTime <= DATEADD(MINUTE, DATEDIFF(MINUTE, 0,'2013-04-21 07:30:00' ), 0)and  --From
   ToTime >= DATEADD(MINUTE, DATEDIFF(MINUTE, 0,'2013-04-21 07:30:00' ), 0) --From
    )
 or
  (ToTime between DATEADD(MINUTE, DATEDIFF(MINUTE, 0,'2013-04-21 07:30:00'), 0)and  --from
 DATEADD(MINUTE, DATEDIFF(MINUTE, 0,'2013-04-21 13:00:00'), 0)--TO
 or
 FromTime <= DATEADD(MINUTE, DATEDIFF(MINUTE, 0,'2013-04-21 13:00:00'), 0)and  --To
ToTime >= DATEADD(MINUTE, DATEDIFF(MINUTE, 0,'2013-04-21 13:00:00'), 0) --To
 ) ))
   and ETC.EmployeeID=71 and ETC.ClientID=20
 order by ETC.TCID desc
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web03 | 2.8.1411023.1 | Last Updated 22 Apr 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100