Rectangle 27 53

So, if there is a need to write a query that can benefit from an index on a date field, then the following (rather convoluted) approach is necessary.

  • The indexed datefield (call it DF1) must be untouched by any kind of function.
  • So you have to compare DF1 to the full range of datetime values for the day of DF2.
  • That is from the date-part of DF2, to the date-part of the day after DF2.
(DF1 >= CAST(DF2 AS DATE)) AND (DF1 < DATEADD(dd, 1, CAST(DF2 AS DATE)))
  • NOTE: It is very important that the comparison is >= (equality allowed) to the date of DF2, and (strictly) < the day after DF2. Also the BETWEEN operator doesn't work because it permits equality on both sides.

PS: Another means of extracting the date only (in older versions of SQL Server) is to use a trick of how the date is represented internally.

  • Cast the date as a float.
  • Cast the value back to a datetime
CAST(FLOOR(CAST(DF2 AS FLOAT)) AS DATETIME)

Compare two DATETIME only by date not time in SQL Server 2008 - Stack ...

sql sql-server tsql sql-server-2008 datetime-conversion
Rectangle 27 2

I'm not near my computer with MS SQL on it but this should do the trick:

Essentially join the two tables based on the Date (without time) portion of the StartTime and FooDate, then just use a BETWEEN in the WHERE clause.

As I said, I can't test this on this laptop but I'd say it'd work.

Edit: This assumes that there is only 1 day per row in the tTime table as per your example

EDIT: Just remembered that you should be able to use BETWEEN in a JOIN criteria, so this should be better:

SELECT tData.*
FROM 
    tData
    INNER JOIN tTime
        ON tData.FooTime BETWEEN tTime.StartTime AND tTime.EndTime

Sign up for our newsletter and get our top new questions delivered to your inbox (see an example).

Query records where time in mutilple ranges in SQL Server - Stack Over...

sql sql-server tsql
Rectangle 27 1

I don't remember if a Between is allowed in a join clause, but if it is:

SELECT FooTime, Value
FROM tData INNER JOIN tTime 
  ON (tData.FooTime BETWEEN tTime.StartTime AND tData.EndTime)

Hi, your first query is not correct considering the time comparision, but your second one is correct.

sorry I did not mark your reply as answer, because both of you gave the correct answer finally, it's hard to decide. So according to the "first come first mark" rule, I gave it to mwan100. Thank you very much anyway.

Query records where time in mutilple ranges in SQL Server - Stack Over...

sql sql-server tsql
Rectangle 27 1

The query uses the following logic:

  • If an EndTime occurs between the other Begin and End times, then they overlap.
  • Similarly if a BeginTime occurs between the other Begin and End times, then they overlap.

NOTE: It doesn't consider NULL values because even if any of the columns are NULLABLE you'd need to clarify how NULLS should be interpretted. E.g. NULL EndTime might imply it is still ongoing and should assume current time as EndTime.

  • If there's no overlap, then one of the BeginTimes must be greater than the other EndTime.
  • So there is an overlap if the above condition is not true.
SELECT  *
FROM    TheTable
WHERE   NOT (BeginTime_1 > EndTime_2 OR BeginTime_2 > EndTime_1)

Applying logic rules, the WHERE clause can be changed to:

WHERE   BeginTime_1 <= EndTime_2 AND BeginTime_2 <= EndTime_1

sql server - How to find whether two times are over lapping or not usi...

sql sql-server sql-server-2008
Rectangle 27 3

Your first pair of queries compute the number of seconds since local midnight on the (local) date of the epoch. This difference is the same in any time zone, so when you set the database timezone to UTC and convert the previously-determined offset back to a timestamp you get the "same" date and time in the sense that the numbers match, but they represent a different absolute time because they are relative to a different TZ.

When you execute your query through JDBC, you are computing a Timestamp in the database timezone, GMT+03:00. java.sql.Timestamp represents absolute time, however, expressed as an offset from midnight GMT at the turn of the epoch. The JDBC driver knows how to compensate. What you then log, therefore, is the time difference between 1970-01-01 00:00:00 GMT+00:00 and 2015-04-15 03:30:00 GMT+03:00.

The getDate().getTime() version is a little less clear cut, but it appears that when you retrieve the timestamp as a Date, thereby truncating the time part, the truncation is being performed relative to the database time zone. Afterward, and similarly to the other case, java.sql.Date.getTime() returns an offset from the turn of the epoch to the resulting absolute time. That is, it is computing the difference between 1970-01-01 00:00:00 GMT+00:00 and 2015-04-15 00:00:00 GMT+03:00

May I ask for clarification - SQL Server datetime uses UTC timestamp? It can't hold timestamp adjusted to local zone as "day save" broke values for half of a year. Is SQL Server transform datetime type value into intermediate value that take TZ in account when it appear in select query? I don't understand why JDBC driver reads server TZ and try to compensate difference.

The internal format in which the database stores Timestamp values is irrelevant to the question. Especially so given that you're not querying any table. You got different results because the queries you fed directly to the database are not semantically equivalent to the combination of JDBC + result wrangling you performed in Java. If you ask a different question, you are likely to get a different answer.

JDBC drivers are required to use the local timezone of the JVM running the application, not that of the server (although SQL Server might break that rule, not sure).

java - Crazy MS SQL Server JDBC driver at datetime field or time zone ...

java sql-server datetime jdbc
Rectangle 27 1

An unguarded interval obviously starts either at the end of a watched period or at the beginning of the whole time range that you are checking. So you need a query that selects all elements from this set that don't have an overlapping shift. The query would look like:

select 1 
from shifts s1 where not exists
    (select 1 from shifts s2
     where s2.start<=s1.end and s2.end > s1.end
    )
    and s1.end>=start_of_range and s1.end<  end_of_range
union
select 1 
where not exists
    (select 1 from shifts s2 
      where s2.start<=start_of_range and s2.end > start_of_range
    )

If this is non-empty, then you have an unguarded interval. I suspect it will run in quadratic time, so it might be slower than "sort, fetch and loop".

I'm having some trouble making this work. I assume that you intend for this to somewhere filter for the end time as well, however even adding that, or just ensuring that only valid shifts are passed (by making shifts a view), the results seem unrelated to the range provided - the same results are given regardless of if the range is inside or outside the shifts.

Right, the top subquery totally ignored the range - added check

sql server 2005 - Checking for time range overlap, the watchman proble...

sql sql-server-2005 algorithm linq-to-sql tsql
Rectangle 27 0

Without testing, I believe the following will give you correct answer with no duplicates (trick to removing duplicates is to make sure t1.DEFF <= t2.DEFF always):

SELECT t1.ID,
        t1.DEFF AS DEFF1, t1.DEND AS DEND1
        t2.DEFF AS DEFF2, t2.DEND AS DEND2
FROM table1 t1
    -- exclude yourself in join (assuming that no two entries are identical)
    INNER JOIN table1 t2 ON t1.ID = t2.ID
        AND t1.DEFF <= t2.DEFF
            AND t1.DEFF != t2.DEFF
            AND t1.DEND != t2.DEND
WHERE
       -- check for overlap including t1 fully inside of t2
       (t1.DEFF BETWEEN t2.DEFF AND t2.DEND
           OR t1.DEND BETWEEN t2.DEFF AND t2.DEND)
   OR
       -- needed to additionally catch t2 fully inside of t1
       (t2.DEFF BETWEEN t1.DEFF AND t1.DEND
           OR t2.DEND BETWEEN t1.DEFF AND t1.DEND)

UPDATE: Realized that my JOIN limitation where t1.DEFF <= t2.DEFF means that t1 can never be inside of t2. Where clause can then be simplified to single check (eg, make sure that t2 does not start before t1 ends):

SELECT t1.ID,
        t1.DEFF AS DEFF1, t1.DEND AS DEND1
        t2.DEFF AS DEFF2, t2.DEND AS DEND2
FROM table1 t1
    -- exclude yourself in join (assuming that no two entries are identical)
    INNER JOIN table1 t2 ON t1.ID = t2.ID
        AND t1.DEFF <= t2.DEFF
            AND t1.DEFF != t2.DEFF
            AND t1.DEND != t2.DEND
WHERE
    t2.DEFF <= t1.DEND

You don't need to treat one range fully overlapping the other as special cases, because they aren't. If T2 starts before T1 ends AND T1 starts before T2 ends, they overlap, no matter how they are arranged. The extra checks are redundant

@Anon - good catch, my original query needed that, but my added JOIN condition made the extra checks unnecessary. I have updated the query.

sql server - Querying for records with overlapping time periods in SQL...

sql-server date duplicates overlapping
Rectangle 27 0

The link below should help point you in the right direction to identifying how the dates overlap.

SQL Server - Query to split time by count (overlapping offices) - Stac...

sql sql-server database tsql
Rectangle 27 0

If you have SQL 2005 + you can run the standard reports in management reports Right Click on database in management studio and select Object execution statistics - this only works since last reboot though. You can also query that using DMV's sys.dm_exec_query_stats

If you are on an older version of SQL use SQL Server Profiler this will show you execution time, reads, cpu, writes and you can filter on thresholds for any of these.

optimization - How to check which stored procedure is taking maximum t...

sql-server optimization performance query-optimization
Rectangle 27 0

select    empid,
          sum
          (
              datediff
              (
              MINUTE, 
              case when timesheet.timein < @timeframe_start 
                  then @timeframe_start  
                  else timesheet.timein end, 
              case when timesheet.timeout > @timeframe_end 
                  then @timeframe_end 
                  else timesheet.timeout end
              )
          ) as total_duration
from     (
         select    timein.empid, 
                   timein.swipe_time as timein, 
                   timeout.swipe_time as timeout
         from      tbltest timein
         left join tblTest timeout 
         on        timein.empid = timeout.empid
         and       timeout.eventtype = 'ex'
         and       timeout.swipe_time = 
             (
             select     MIN(swipe_time) 
             from       tblTest tcheck 
             where      tcheck.empid = timeout.empid 
             and        tcheck.eventtype = 'EX' 
             and        tcheck.swipe_time > timein.swipe_time
             )
         where      timein.eventtype = 'en'
         ) timesheet
where    timesheet.timein between @timeframe_start and @timeframe_end
or       timesheet.timeout between @timeframe_start and @timeframe_end
or       (
         timesheet.timein < @timeframe_start 
         and timesheet.timeout > @timeframe_end
         )
group by empid
order by 2 desc

Here's a SQLFiddle for you to play with: sqlfiddle.com/#!3/54e6f/3 (I changed one of your parameters, though)

sql server - sql query - Get the difference time between swipe in - Sw...

sql sql-server sql-server-2008
Rectangle 27 0

To do this, first you need to get both rows combined together as 1 row. Once you have all the data as a single row, you can do a series of union queries to build the resultant rows, one query for each condition.

INSERT INTO TData
    ([ID], [FName], [LName], [Status], [StartTime], [EndTime], [Hours], [Location])
VALUES
    (101, 'Andrew', 'Smith', 'Available', '2014-08-19 09:00:00', '2014-08-19 17:00:00', 8.00, 'London'),
    (101, 'Andrew', 'Smith', 'SICKNESS', '2014-08-19 09:00:00', '2014-08-19 17:00:00', 8.00, 'London'),
    (101, 'Andrew', 'Smith', 'SICKNESS', '2014-08-20 12:00:00', '2014-08-20 19:00:00', 7.00, 'London'),
    (101, 'Andrew', 'Smith', 'Available', '2014-08-20 09:00:00', '2014-08-20 17:00:00', 8.00, 'London'),
    (101, 'Andrew', 'Smith', 'Available', '2014-08-21 09:00:00', '2014-08-21 17:00:00', 8.00, 'London'),
    (101, 'Andrew', 'Smith', 'SICKNESS', '2014-08-21 04:00:00', '2014-08-21 12:00:00', 8.00, 'London'),
    (101, 'Andrew', 'Smith', 'SICKNESS', '2014-08-22 06:00:00', '2014-08-22 18:00:00', 12.00, 'London'),
    (101, 'Andrew', 'Smith', 'Available', '2014-08-22 09:00:00', '2014-08-22 17:00:00', 8.00, 'London'),
    (101, 'Andrew', 'Smith', 'Available', '2014-08-23 09:00:00', '2014-08-23 17:00:00', 8.00, 'London'),
    (101, 'Andrew', 'Smith', 'SICKNESS', '2014-08-23 11:00:00', '2014-08-23 14:00:00', 3.00, 'London'),
    (101, 'Andrew', 'Smith', 'Available', '2014-08-24 09:00:00', '2014-08-23 17:00:00', 8.00, 'London'),
    (101, 'Andrew', 'Smith', 'SICKNESS', '2014-08-24 09:00:00', '2014-08-23 14:00:00', 3.00, 'London'),
    (101, 'Andrew', 'Smith', 'Available', '2014-08-25 09:00:00', '2014-08-23 17:00:00', 8.00, 'London'),
    (101, 'Andrew', 'Smith', 'SICKNESS', '2014-08-25 11:00:00', '2014-08-23 17:00:00', 3.00, 'London')
;

And here is a start on the result. You need to add more UNION clauses for each row/condition that will finally be displayed.

;WITH Combined AS (
SELECT T1.*, t2.StartTime AS S_StartTime, t2.EndTime AS S_EndTime, 
        t2.Status AS S_Status, t2.Hours AS S_Hours
FROM TData T1 --Change TData to your table name
LEFT OUTER JOIN Tdata T2 ON T1.id = T2.id 
        AND CAST(T1.StartTime AS date) = CAST(T2.StartTime as date) 
        AND t1.Status <> t2.Status
WHERE T1.Status <> 'SICKNESS' 
)
--this is case 1 Show sickness only?  If not, add another row
SELECT ID, FName, LName, s_Status AS Status, 
        s_StartTime AS StartTime, s_EndTime AS EndTime, 
        S_Hours AS Hours, Location
FROM combined 
WHERE StartTime = S_StartTime and EndTime = s_EndTime 
UNION --Case 2 Row 1
SELECT ID, FName, LName, s_Status AS Status, 
        s_StartTime AS StartTime, StartTime AS EndTime, 
        DATEDIFF(minute,S_StartTime,StartTime)/60 AS Hours, Location
FROM combined 
WHERE S_StartTime < StartTime 
      AND S_EndTime BETWEEN StartTime AND EndTime 
UNION --case 2 row 2
SELECT ID, FName, LName, S_Status AS Status, 
       StartTime AS StartTime, S_EndTime AS EndTime, 
       DATEDIFF(minute,StartTime,S_EndTime)/60 AS Hours, Location
FROM combined 
WHERE S_StartTime < StartTime 
      AND S_EndTime BETWEEN StartTime AND EndTime 
UNION --case 2 row 3
SELECT ID, FName, LName, Status, 
       S_EndTime AS StartTime, EndTime AS EndTime, 
       DATEDIFF(minute,S_EndTime,EndTime)/60 AS Hours, Location
FROM combined 
WHERE S_StartTime < StartTime 
      AND S_EndTime BETWEEN StartTime AND EndTime

I can work out the rest of the clauses, but it should be fairly clear how to do it. Let me know if you need additional help.

sql server - Extrapolate overlapping time periods into the before, ove...

sql sql-server date sql-server-2012 overlapping
Rectangle 27 0

select * from table where 
(dtColumn between #3/1/2009# and #3/31/2009#) and 
(hour(dtColumn) between 6 and 22) and 
(weekday(dtColumn, 1) between 2 and 4)

Does this even work in 2008? Seems like useful date functions took until 2012 to become available.

I really do not think that is a real T-sql method.

datetime - Specific Time Range Query in SQL Server - Stack Overflow

sql-server-2005 datetime
Rectangle 27 0

I think your design of having one table at the [Hotel, Date] grain then rolling up into Hotel sounds fine. As Damir points out it keeps your read queries simple and makes it easy to add/remove aggregate measures going forward (keeping in mind that it's generally a bad idea to design around requirements that you may have in the future).

Pondlife makes good points as well. Your qualitative requirements might dictate how feasible it is to maintain an aggregate table, for example how often the system needs to update (daily, hourly, 15 mins, realtime?), how accurate the measures need to be (maybe the users just need a rough sense of how well each hotel is doing), how costly it is to read the source transaction data, how available the source transaction data is in the long-term (does it get archived), etc.

If you choose to add a [Hotel, StayDate] grain fact table and not maintain an aggregate then perhaps you can explore some tricks in your dimensions to save time. Maybe something like a 7-day date dimension containing [date, date_in_last_7_days] (so 7 records for each date) in case a straight join vs range querying the past 7 days saves you any time. That might be a stupid example but something along those lines. Date dimensions are small.

Lastly consider hardware optimizations like moving tables into memory (especially dimensions or non-gigantic fact tables) if you need to improve performance.

sql server - Data warehouse design - how to design for measures that a...

sql-server data-warehouse
Rectangle 27 0

First and foremost - Check the actual query being ran. I use SQL Server Profiler as I setup through my program and check that all my queries are using correct joins and referencing keys when I can.

c# - A checklist for fixing .NET applications to SQL Server timeout pr...

c# .net sql-server vb.net timeout
Rectangle 27 0

Using BETWEEN is probably a little more optimal than two AND statements (maybe not). Try not to do a calculation on each row if you don't have to. Doing DATEADD only on the current date will only be calculated once.

SELECT 
   whatever
FROM
   table
WHERE 
    StartDate
        BETWEEN FLOOR( CAST( GETDATE() AS FLOAT ) )
        AND DATEADD(minute, -5, GETDATE())

I interpret the question as looking for rows where the date is today (between the start of today) but not within the last 5 minutes (and less than 5 minutes ago). That might not be what you were going for.

SQL QUERY ( SQL SERVER) Date & Time Difference - Stack Overflow

sql-server sql-server-2005 tsql
Rectangle 27 0

Use a parametrized query and pass the DateTime through as parameter. Using strings is always a bad idea (especially strings that aren't culture-invariant).

Can u give me any example of parametrized query.

@Viktor, I think you may already be using one, since you have @name in your SQL statement. Edit your question to include the full SQL invocation in C# and I'll show you how to modify it. Oh, and the English word for adressing someone is "you", not "u".

Ok! But, my problem is not the sql injection, I'll takecare of it, my problem is to store actual date and time in database in India standard time. How to do this, can you give me an example of this?

I didn't even mention SQL injection... using paramterized queries is the way to go for many reasons, one of which is SQL injection, but also because of the data types, efficiency (no need to convert to and from strings), query re-use (queries can be cached by optimizer even if the parameter contents differ) etc.

c# - How to store date and time in SQL Server 2005? - Stack Overflow

c# asp.net sql-server datetime
Rectangle 27 0

Playing around with the cross-tab wizard I got this for an Access query:

transform 
    count(*) as CountOfReceipts
select
    Month(x.[Receipt Date]) as Month
from
    the_table x
where
    Department = 'marketing'
group by
    Month(x.[Receipt Date])
pivot
    x.Type;

This is specific to access, though. If you're running T-SQL as a pass-through query, the T-SQL syntax for pivot is different.

sql server - How can I select the count of a field multiple times for ...

sql sql-server tsql ms-access
Rectangle 27 0

Here's a query that will return all results within a range of days.

DECLARE @startDate DATETIME
DECLARE @endDate DATETIME

SET @startDate = DATEADD(day, -30, GETDATE())
SET @endDate = GETDATE()

SELECT *
FROM table
WHERE dateColumn >= DATEADD(day, DATEDIFF(day, 0, @startDate), 0)
  AND dateColumn <  DATEADD(day, 1, DATEDIFF(day, 0, @endDate))

using the -2 MS trick is poor practice, because it relies on the resolution of the datetime data type. People have ended up in unpleasant situations because of this. Instead, stop using BETWEEN and use >= and <.

sql server - MS SQL Date Only Without Time - Stack Overflow

sql sql-server tsql
Rectangle 27 0

The main issue with this query is that it is unsargable. Even if the DATEEND column is indexed then the index cannot be used.

The only way of making this query index friendly would be to have an computed column with definition CAST([DATEEND] as TIME) and then index it.

Even if you were to do that you may well find that the index isn't used though as it will depend how selective the query is. As your query uses * the index will need to do key lookups to retrieve the non covered columns. The exact selectivity at which the index would be used depends on the "Tipping Point"

You could also make the index covering by INCLUDE-ing the missing columns but as you are selecting all columns in the table that will make the index very wide which means more logical reads to scan it as well as more expensive to maintain for data modification statements.

Would a different method of comparison be better?

@KennethJ - Doubt it. Any difference between different comparison methods will likely only be extremely minor differences in CPU time and a very small contributor to the overall query cost.

SQL Server 2008 datetime type time comparison - Stack Overflow

sql sql-server-2008 datetime time comparison
Rectangle 27 0

Beyond the excellent indexing suggestions already given, be sure to read up on parameter sniffing. That could be the cause of the problem.

As a result you could have a bad query plan being re-used, or SQL's buffer could be getting full and writing pages out of memory to disk (maybe that's other allocated memory in your case).

You could run DBCC FreeProcCache and DBCC FreeSystemCache to empty it and see if you get a performance boost.

You should give SQL more memory too - as much as you can while leaving room for other critical programs and the OS. You might have 5gb of Ram on the VM, but SQL is only getting to play with a 1/2 gb, which seems REALLY small for what you're describing.

If those things don't move you in the right direction, install the SQL Management Data Warehouse so you can see exactly what is happening when your slow down begins. Running it takes up additional memory, but you will give the DBA's more to go on.

SQL Server query performance slows over time - Stack Overflow

sql sql-server performance