Search Results

Search found 128 results on 6 pages for 'datediff'.

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

Page 1/6 | 1 2 3 4 5 6  | Next Page >

  • mdx datediff error

    - by Roberto Durand
    select measures.name datediff("d", [Fecha].[Date].currentmember.member_value, [Dim Date].[Date].currentmember.member_value) on 1 from cube Error: Execution of the managed stored procedure datediff failed with the following error: Exception has been thrown by the target of an invocation.Argument 'Date1' cannot be converted to type 'Date' Is there any requirements to do datediff in mdx? In the dimension these member are defined as datetime, not sure if this influence in anyway the result...

    Read the article

  • SQL DATEDIFF Not working!?

    - by James
    Hi all, I am running a simple DATEDIFF query but it doesn't seem to calculate the days properly or i'm doing something wrong. If I run PRINT DATEDIFF(Day, 2010-01-20, 2010-01-01) RETURN 19 Which is correct. If i change the month in the first date to Feb (02) I get something strange. PRINT DATEDIFF(Day, 2010-02-20, 2010-01-01) RETURN 20 Now shouldn't it be 48 or something? Can anyone see what i'm doing wrong or is this not the correct function to be using if I want the No of days between these dates? I've tried taking one date from the other: PRINT (2010-02-20) - (2010-01-01) RETURN -20 Any help much appreciated. Thanks J.

    Read the article

  • SQL Function that calculates Shift from StartTime and EndTime

    - by Gentis
    Hello Folks I have been trying to get a function going that calculates what Shift the Employees worked from their StartTime and EndTime. Here is the code i have so far, there seems to be calculating the shift wrong. Shift 1 from 08:00:00 - 16:30:00 Shift 2 from 16:00:00 - 00:30:00 Shift 3 from 00:00:00 - 08:30:00 Also the shift with most hours wins for times between shifts. Thanks, G `FUNCTION [dbo].[ShiftDifferential] ( @StartTime time(0), @EndTime time(0) ) RETURNS int AS BEGIN --DECLARE @StartTime time(0) --DECLARE @EndTime time(0) -- Declare the return variable here DECLARE @Shift1StartTime time(0) DECLARE @Shift2StartTime time(0) DECLARE @Shift3StartTime time(0) DECLARE @Shift1EndTime time(0) DECLARE @Shift2EndTime time(0) DECLARE @Shift3EndTime time(0) DECLARE @HrsShift1 decimal(18,2) DECLARE @HrsShift2 decimal(18,2) DECLARE @HrsShift3 decimal(18,2) DECLARE @ShiftDiff int --SET @StartTime = '09:00:00' --SET @EndTime = '13:00:00' SET @Shift1StartTime = '08:00:00' SET @Shift2StartTime = '16:00:00' SET @Shift3StartTime = '00:00:00' SET @Shift1EndTime = '16:30:00' SET @Shift2EndTime = '00:30:00' SET @Shift3EndTime = '08:30:00' --SELECT DATEDIFF(HH, @Shift1StartTime, @EndTime) -- hours are between shift 3 and shift 1 if DATEDIFF(HH, @Shift1StartTime, @StartTime) < 0 AND (DATEDIFF(hh, @Shift1StartTime, @EndTime) < 8.0 AND DATEDIFF(hh, @Shift1StartTime, @EndTime) 0) begin --PRINT 'Shift 3-1 step1' SET @HrsShift3 = DATEDIFF(HH, @StartTime, @Shift1StartTime) SET @HrsShift1 = DATEDIFF(HH, @Shift1StartTime, @Endtime) --PRINT @HrsShift3 --PRINT @HrsShift1 -- get shift with most hours if @HrsShift3 @HrsShift1 begin SET @ShiftDiff = 3 end else begin SET @ShiftDiff = 1 end end -- hours are in shift 1 if (DATEDIFF(HH, @Shift1StartTime, @StartTime) = 0 AND DATEDIFF(HH, @Shift1StartTime, @EndTime) <= 8) OR (DATEDIFF(HH, @Shift1StartTime, @StartTime) 0 AND DATEDIFF(HH, @Shift1StartTime, @EndTime) <= 8) begin --PRINT 'Shift 1 step2' SET @HrsShift3 = 0 SET @HrsShift1 = DATEDIFF(HH, @StartTime, @EndTime) --PRINT @HrsShift3 --PRINT @HrsShift1 -- only one shift with hours SET @ShiftDiff = 1 end -- hours are between shift 1 and shift 2 if DATEDIFF(HH, @Shift2StartTime, @StartTime) < 0 and (DATEDIFF(HH, @Shift2StartTime, @EndTime) < 8.0 AND DATEDIFF(HH, @Shift2StartTime, @EndTime) 0) begin --PRINT 'Shift 1-2 step1' SET @HrsShift1 = DATEDIFF(HH, @StartTime, @Shift2StartTime) SET @HrsShift2 = DATEDIFF(HH, @Shift2StartTime, @Endtime) --PRINT @HrsShift1 --PRINT @HrsShift2 -- get the shift with most hours if @HrsShift1 @HrsShift2 begin SET @ShiftDiff = 1 end else begin SET @ShiftDiff = 2 end end -- hours are in shift 2 if (DATEDIFF(HH, @Shift2StartTime, @StartTime) = 0 AND DATEDIFF(HH, @Shift2StartTime, @EndTime) <= 8) OR (DATEDIFF(HH, @Shift2StartTime, @StartTime) 0 AND DATEDIFF(HH, @Shift2StartTime, @EndTime) <= 8) begin --PRINT 'Shift 2 step2' SET @HrsShift3 = 0 SET @HrsShift1 = DATEDIFF(HH, @StartTime, @EndTime) --PRINT @HrsShift3 --PRINT @HrsShift1 -- only one shift with hours SET @ShiftDiff = 2 end -- hours are between shift 2 and shift 3 - overnight shift if DATEDIFF(HH, @StartTime, @EndTime) < 0 begin --PRINT 'Shift 2-3 step1' SET @HrsShift2 = DATEDIFF(HH, @StartTime, '23:59:59') + DATEDIFF(HH, '00:00:00', '00:30:00') SET @HrsShift3 = DATEDIFF(HH, '00:30:00', @EndTime) --PRINT @HrsShift2 --PRINT @HrsShift3 -- get the shift with most hours if @HrsShift2 @HrsShift3 begin SET @ShiftDiff = 2 end else begin SET @ShiftDiff = 3 end end -- hours are in shift 3 if (DATEDIFF(HH, @Shift3StartTime, @StartTime) = 0 AND DATEDIFF(HH, @Shift3StartTime, @EndTime) <= 8) OR (DATEDIFF(HH, @Shift3StartTime, @StartTime) 0 AND DATEDIFF(HH, @Shift3StartTime, @EndTime) <= 8) begin --PRINT 'Shift 3 step2' SET @HrsShift2 = 0 SET @HrsShift3 = DATEDIFF(HH, @StartTime, @EndTime) --PRINT @HrsShift2 --PRINT @HrsShift3 -- only one shift with hours SET @ShiftDiff = 3 end RETURN @ShiftDiff; END`

    Read the article

  • MSSQL DATEDIFF accuracy

    - by jomi
    Hello, I have to store some intervals in mssql db. I'm aware that the datetime's accuracy is approx. 3.3ms (can only end 0, 3 and 7). But when I calculate intervals between datetimes I see that the result can only end with 0, 3 and 6. So the more intervals I sum up the more precision I loose. Is it possible to get an accurate DATEDIFF in milliseconds ? declare @StartDate datetime declare @EndDate datetime set @StartDate='2010-04-01 12:00:00.000' set @EndDate='2010-04-01 12:00:00.007' SELECT DATEDIFF(millisecond, @StartDate, @EndDate),@EndDate-@StartDate, @StartDate, @EndDate I would like to see 7 ad not 6. (And it should be as fast as possible) Thanks,

    Read the article

  • SQL SERVER – DATEDIFF – Accuracy of Various Dateparts

    - by pinaldave
    I recently received the following question through email and I found it very interesting so I want to share it with you. “Hi Pinal, In SQL statement below the time difference between two given dates is 3 sec, but when checked in terms of Min it says 1 Min (whereas the actual min is 0.05Min) SELECT DATEDIFF(MI,'2011-10-14 02:18:58' , '2011-10-14 02:19:01') AS MIN_DIFF Is this is a BUG in SQL Server ?” Answer is NO. It is not a bug; it is a feature that works like that. Let us understand that in a bit more detail. When you instruct SQL Server to find the time difference in minutes, it just looks at the minute section only and completely ignores hour, second, millisecond, etc. So in terms of difference in minutes, it is indeed 1. The following will also clear how DATEDIFF works: SELECT DATEDIFF(YEAR,'2011-12-31 23:59:59' , '2012-01-01 00:00:00') AS YEAR_DIFF The difference between the above dates is just 1 second, but in terms of year difference it shows 1. If you want to have accuracy in seconds, you need to use a different approach. In the first example, the accurate method is to find the number of seconds first and then divide it by 60 to convert it to minutes. SELECT DATEDIFF(second,'2011-10-14 02:18:58' , '2011-10-14 02:19:01')/60.0 AS MIN_DIFF Even though the concept is very simple it is always a good idea to refresh it. Please share your related experience with me through your comments. Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: Pinal Dave, PostADay, SQL, SQL Authority, SQL DateTime, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology

    Read the article

  • Work around for calculating age using the SQL Server DateDiff function

    Have you ever wanted to compute age, but the results from the DATEDIFF function seemed to be wrong some of the time? This tip covers why the DATEDIFF function does not always reliably compute age. New! SQL Monitor 3.0 Red Gate's multi-server performance monitoring and alerting tool gets results from Day One.Simple to install and easy to use – download a free trial today.

    Read the article

  • PHP Datediff days involved

    - by user3549835
    I need to know how many days are involved in a date diff. \For example: <? $start = new DateTime('2014-06-29 14:00:00'); $ende = new DateTime('2014-07-02 05:45:00'); $diff = $start->diff($ende); echo $diff->format('%R'); echo $diff->days; ?> The above code echos +2 My desired result would be 4, because the 29th, 30th, 1st and 2nd of July are "touched". I have no idea to achieve that with the given functions. Coding a day-subtraction seems to bean open door for errors.

    Read the article

  • How does DATEDIFF calculate week differences in SQL Server 2005?

    - by eksortso
    I would like to calculate the difference in weeks between two dates, where two dates are considered part of the same week if their preceding Sunday is the same. Ideally, I'd like to do this using DATEDIFF, instead of learning an elaborate idiom to calculate the value. But I can't tell how it works when weeks are involved. The following query returns 1 and 2. This might make sense if your calendar week begins with a Sunday, i.e. if you run SET DATEFIRST 7 beforehand or if @@DATEFIRST is 7 by default. SET DATEFIRST 7; -- SET DATEFIRST 1; DECLARE @d1 DATETIME, @d2a DATETIME, @d2b DATETIME ; SELECT @d1 = '2010-04-05', -- Monday @d2a = '2010-04-16', -- Following Friday @d2b = '2010-04-18' -- the Sunday following ; SELECT DATEDIFF(week, @d1, @d2a) AS weekdiff_a -- returns 1 ,DATEDIFF(week, @d1, @d2b) AS weekdiff_b -- returns 2 ; So I expected different results if SET DATEFIRST 1 is executed instead of SET DATEFIRST 7. But the return values are the same, regardless! What is going on here? What should I do to get the correct week differences?

    Read the article

  • Is SQL DATEDIFF(year, ..., ...) an Expensive Computation?

    - by rlb.usa
    I'm trying to optimize up some horrendously complicated SQL queries because it takes too long to finish. In my queries, I have dynamically created SQL statements with lots of the same functions, so I created a temporary table where each function is only called once instead of many, many times - this cut my execution time by 3/4. So my question is, can I expect to see much of a difference if say, 1,000 datediff computations are narrowed to 100?

    Read the article

  • datediff rounding

    - by derekcohen
    I have a db table in SQL Server which contains a start date for a project. On a web status page I want to show how many days/weeks/months the project has run, the units depending on the duration. So under 21 days I'd show days, under 7 weeks I'd show weeks, otherwise show completed months. So I get the days, weeks and months values and can then use some code to decide which one to display. Suppose the project starts on 30 Dec 2010 and I'm checking today (27 Feb 2011). select datediff(d,'30 Dec 2010',getdate()) as days, datediff(wk,'30 Dec 2010',getdate()) as weeks , datediff(m,'30 Dec 2010',getdate())as months produces days: 59 weeks: 9 months: 2 But in fact the difference is 8 whole weeks and some rounding takes place. I've tried doing it in ASP as well, getting the start date and then doing the datediff() but it's no better. Is there a better way? thanks

    Read the article

  • Using DateDiff in Entity Framwork on a SQL CE database

    - by deverop
    I have a method which should return a list of anonymous objects with a calculated column like this: var tomorrow = DateTime.Today.AddDays(1); return from t in this.Events where (t.StartTime >= DateTime.Today && t.StartTime < tomorrow && t.EndTime.HasValue) select new { Client = t.Activity.Project.Customer.Name, Project = t.Activity.Project.Name, Task = t.Activity.Task.Name, Rate = t.Activity.Rate.Name, StartTime = t.StartTime, EndTime = t.EndTime.Value, Hours = (System.Data.Objects.SqlClient.SqlFunctions.DateDiff("m", t.StartTime, t.EndTime.Value) / 60), Description = t.Activity.Description }; Unfortunately I get the following error from the DateDiff function: The specified method 'System.Nullable1[System.Int32] DateDiff(System.String, System.Nullable1[System.DateTime], System.Nullable`1[System.DateTime])' on the type 'System.Data.Objects.SqlClient.SqlFunctions' cannot be translated into a LINQ to Entities store expression. Any ideas what I could have done wrong here? EDIT: I also tried the EntityFunctions class mentioned here, but that did not work as well. Minutes = EntityFunctions.DiffMinutes(t.EndTime, t.StartTime),

    Read the article

  • How to write a CASE WHEN statement with multiple DATEDIFF variables

    - by Anne C
    I need to calculate the difference between two dates (facility_start_date, facility_end_date) for a report in Reporting Services in SQL 2005. If the facility_end_date is null then it needs to use the report parameter @EndDate in the calculation. However if the facility_end_date is greater than the parameter @EndDate, then it also needs to use the paramenter @EndDate. The code below works fine except that if the facility_end_date is greater than the parameter @EndDate it is still calculating between the facility_start_date and facility_end_date, rather than between the facility_start_date and @EndDate. Any help would be appreciated. CASE WHEN facility_start_date > facility_end_date THEN NULL WHEN DATEPART(day , facility_start_date) > DATEPART(day , facility_end_date) THEN DATEDIFF(d , facility_start_date , ISNULL(facility_end_date , @EndDate)) - 1 WHEN DATEPART(day , .facility_end_date) > DATEPART(day , @EndDate) THEN DATEDIFF(d , facility_start_date , @EndDate) - 1 ELSE DATEDIFF(d , facility_start_date , ISNULL facility_end_date , @EndDate)) END

    Read the article

  • C# (ADO.Net): Defining DateDiff for a calculated column in a datatable

    - by Nir
    I have the column DateTimeExpired, and I would like to create another column called "Expired" which will show "Yes" or "No" according to the expiration date - "Yes" if the date has already passed. I wrote this: DataColumn colExpirationDate = new DataColumn("DateTimeExpired", typeof(DateTime)); DataColumn colExpired = new DataColumn("Expired", typeof(string), "IIF(DateDiff(DateTimeExpired, date())>= 0,'No','Yes')"); But I get an exception "The expression contains undefined function call DateDiff()." (please note that I always want to get the row, no matter if it's expired or not) How do I set the column's text to the correct form? Thanks!

    Read the article

  • Defining DateDiff for a calculated column in a datatable

    - by Nir
    I have the column DateTimeExpired, and I would like to create another column called "Expired" which will show "Yes" or "No" according to the expiration date - "Yes" if the date has already passed. I wrote this: DataColumn colExpirationDate = new DataColumn("DateTimeExpired", typeof(DateTime)); DataColumn colExpired = new DataColumn("Expired", typeof(string), "IIF(DateDiff(DateTimeExpired, date())>= 0,'No','Yes')"); But I get an exception "The expression contains undefined function call DateDiff()." (please note that I always want to get the row, no matter if it's expired or not) How do I set the column's text to the correct form?

    Read the article

  • Daylight Savings Handling in DateDiff() in MS Access?

    - by PowerUser
    I am fully aware of DateDiff()'s inability to handle daylight savings issues. Since I often use it to compare the number of hours or days between 2 datetimes several months apart, I need to write up a solution to handle DST. This is what I came up with, a function that first subtracts 60 minutes from a datetime value if it falls within the date ranges specified in a local table (LU_DST). Thus, the usage would be: datediff("n",Conv_DST_to_Local([date1]),Conv_DST_to_Local([date2])) My question is: Is there a better way to handle this? I'm going to make a wild guess that I'm not the first person with this question. This seems like the kind of thing that should have been added to one of the core reference libraries. Is there a way for me to access my system clock to ask it if DST was in effect at a certain date & time? Function Conv_DST_to_Local(X As Date) As Date Dim rst As DAO.Recordset Set rst = CurrentDb.OpenRecordset("LU_DST") Conv_DST_to_Local = X While rst.EOF = False If X > rst.Fields(0) And X < rst.Fields(1) Then Conv_DST_to_Local = DateAdd("n", -60, X) rst.MoveNext Wend End Function Notes I have visited and imported the BAS file of http://www.cpearson.com/excel/TimeZoneAndDaylightTime.aspx. I spent at least an hour by now reading through it and, while it may do its job well, I can't figure out how to modify it to my needs. But if you have an answer using his data structures, I'll take a look. Timezones are not an issue since this is all local time.

    Read the article

  • SQL SERVER – Validating If Date is Last Day of the Year, Month or Day

    - by Pinal Dave
    Here is one more question I recently received in an email- “Pinal, is there any ready made function which will display if the given date is the last day or the year, month or day? For example, if a date is the last day of the Year and last day of the month, I want to display Last Day of the Year and if a date is the last date of the month and last day of the week, I want to display the last day of the week. “ Well, very interesting question and there is no such function available for the same. However, here is the function I have written earlier for my personal use where I almost accomplish same task. -- Example of Year DECLARE @Day DATETIME SET @Day = '2014-12-31' SELECT CASE WHEN CAST(@Day AS DATE) = CAST(DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,@Day)+1,0))) AS DATE) THEN 'LastDayofYear' WHEN CAST(@Day AS DATE) = CAST(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@Day)+1,0)) AS DATE) THEN 'LastDayofMonth' WHEN CAST(@Day AS DATE) = CAST(DATEADD(s,-1,DATEADD(wk, DATEDIFF(wk,0,@Day),0)) AS DATE) THEN 'LastDayofWeek' ELSE 'Day' END GO -- Example of Month DECLARE @Day DATETIME SET @Day = '2014-06-30' SELECT CASE WHEN CAST(@Day AS DATE) = CAST(DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,@Day)+1,0))) AS DATE) THEN 'LastDayofYear' WHEN CAST(@Day AS DATE) = CAST(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@Day)+1,0)) AS DATE) THEN 'LastDayofMonth' WHEN CAST(@Day AS DATE) = CAST(DATEADD(s,-1,DATEADD(wk, DATEDIFF(wk,0,@Day),0)) AS DATE) THEN 'LastDayofWeek' ELSE 'Day' END GO -- Example of Month DECLARE @Day DATETIME SET @Day = '2014-05-04' SELECT CASE WHEN CAST(@Day AS DATE) = CAST(DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,@Day)+1,0))) AS DATE) THEN 'LastDayofYear' WHEN CAST(@Day AS DATE) = CAST(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@Day)+1,0)) AS DATE) THEN 'LastDayofMonth' WHEN CAST(@Day AS DATE) = CAST(DATEADD(s,-1,DATEADD(wk, DATEDIFF(wk,0,@Day),0)) AS DATE) THEN 'LastDayofWeek' ELSE 'Day' END GO Let me know if you know any other smarter trick and we can post it here with due credit. Reference: Pinal Dave (http://blog.SQLAuthority.com)Filed under: PostADay, SQL, SQL Authority, SQL DateTime, SQL Query, SQL Server, SQL Tips and Tricks, T SQL

    Read the article

  • MySQL query puzzle - finding what WOULD have been the most recent date

    - by Hank
    I've looked all over and haven't yet found an intelligent way to handle this, though I feel sure one is possible: One table of historical data has quarterly information: CREATE TABLE Quarterly ( unique_ID INT UNSIGNED NOT NULL, date_posted DATE NOT NULL, datasource TINYINT UNSIGNED NOT NULL, data FLOAT NOT NULL, PRIMARY KEY (unique_ID)); Another table of historical data (which is very large) contains daily information: CREATE TABLE Daily ( unique_ID INT UNSIGNED NOT NULL, date_posted DATE NOT NULL, datasource TINYINT UNSIGNED NOT NULL, data FLOAT NOT NULL, qtr_ID INT UNSIGNED, PRIMARY KEY (unique_ID)); The qtr_ID field is not part of the feed of daily data that populated the database - instead, I need to retroactively populate the qtr_ID field in the Daily table with the Quarterly.unique_ID row ID, using what would have been the most recent quarterly data on that Daily.date_posted for that data source. For example, if the quarterly data is 101 2009-03-31 1 4.5 102 2009-06-30 1 4.4 103 2009-03-31 2 7.6 104 2009-06-30 2 7.7 105 2009-09-30 1 4.7 and the daily data is 1001 2009-07-14 1 3.5 ?? 1002 2009-07-15 1 3.4 && 1003 2009-07-14 2 2.3 ^^ then we would want the ?? qtr_ID field to be assigned '102' as the most recent quarter for that data source on that date, and && would also be '102', and ^^ would be '104'. The challenges include that both tables (particularly the daily table) are actually very large, they can't be normalized to get rid of the repetitive dates or otherwise optimized, and for certain daily entries there is no preceding quarterly entry. I have tried a variety of joins, using datediff (where the challenge is finding the minimum value of datediff greater than zero), and other attempts but nothing is working for me - usually my syntax is breaking somewhere. Any ideas welcome - I'll execute any basic ideas or concepts and report back.

    Read the article

  • In git, how can I get the diff between two dates?

    - by Weidenrinde
    Basically, I am looking for something equivalent to cvs diff -D"1 day ago" -D"2010-02-29 11:11". While collecting more and more information, I found a solution. I paste it here, for others that might have similar problems. Things I have tried: In git, how can I get the diff between all the commits that occured between two dates? was ansered here with: git whatchanged --since="1 day ago" -p But this gives a diff for each commit, even if there are multiple commits in one file. I know that "date" is a bit of a loose concept in git, I thought there must be some way to do this. git diff 'master@{1 day ago}..master gives some warning warning: Log for 'master' only goes back to Tue, 16 Mar 2010 14:17:32 +0100. and does not show old diffs. git format-patch --since=yesterday --stdout does not give anything. revs=$(git log --pretty="format:%H" --since="1 day ago");git diff $(echo "$revs"|tail -n1) $(echo "$revs"|head -n1) works somehow, but seems complicated and does not restrict to the current branch. git diff $(git rev-list -n1 --before="1 day ago" master) seems to work and a default way to do similar things, although more complicated than I thought. Funnily, git-cvsserver does not support "cvs diff -D" (without that it is documented somewhere).

    Read the article

  • Find date difference in php?

    - by Karthik
    Is there anyway to find the date difference in php? I have the input of from date 2003-10-17 and todate 2004-03-24. I need the results how many days is there within these two days. Say if 224 days, i need the output in days only. I find the solution through mysql but i need in php. Anyone help me, Thanks in advance.

    Read the article

  • SQL: Find difference between dates with grouping

    - by ajbeaven
    I have a problem that seems similar to this fellow - I just want to display the data slightly differently. I'm pretty terrible with SQL so can't modify it to suit, but perhaps someone else can. My table looks similar to this (date format is dd/mm/yyyy): ID User Date_start Role 1 Andy 01/04/2010 A 2 Andy 10/04/2010 B 3 Andy 20/04/2010 A 4 John 02/05/2010 A I want to show the total number of days that anyone was in a certain role. Users stay in the role until there is another entry into the table. Users can only be in one role at a time. So the summary data would look like this (assuming that the date is 04/05/2010): A: 26 days B: 10 days Thanks for any help :)

    Read the article

  • How to: group by month with SQL

    - by AngelEyes
    I took this particular code from http://weblogs.sqlteam.com/jeffs/archive/2007/09/10/group-by-month-sql.aspx, a good read. Shows you what to avoid and why.   The recommended technique is the following:   GROUP BY dateadd(month, datediff(month, 0, SomeDate),0)   By the way, in the "select" clause, you can use the following:   SELECT         month(dateadd(month, datediff(month, 0, SomeDate),0)) as [month],         year(dateadd(month, datediff(month, 0, SomeDate),0)) as [year],   Just remember to also sort properly if needed:   ORDER BY dateadd(month, datediff(month, 0, SomeDate),0)

    Read the article

  • T-SQL select where and group by date

    - by bconlon
    T-SQL has never been my favorite language, but I need to use it on a fairly regular basis and every time I seem to Google the same things. So if I add it here, it might help others with the same issues, but it will also save me time later as I will know where to look for the answers!! 1. How do I SELECT FROM WHERE to filter on a DateTime column? As it happens this is easy but I always forget. You just put the DATE value in single quotes and in standard format: SELECT StartDate FROM Customer WHERE StartDate >= '2011-01-01' ORDER BY StartDate 2. How do I then GROUP BY and get a count by StartDate? Bit trickier, but you can use the built in DATEADD and DATEDIFF to set the TIME part to midnight, allowing the GROUP BY to have a consistent value to work on: SELECT DATEADD (d, DATEDIFF(d, 0, StartDate),0) [Customer Creation Date], COUNT(*) [Number Of New Customers] FROM Customer WHERE StartDate >= '2011-01-01' GROUP BY DATEADD(d, DATEDIFF(d, 0, StartDate),0) ORDER BY [Customer Creation Date] Note: [Customer Creation Date] and [Number Of New Customers] column alias just provide more readable column headers. 3. Finally, how can you format the DATETIME to only show the DATE part (after all the TIME part is now always midnight)? The built in CONVERT function allows you to convert the DATETIME to a CHAR array using a specific format. The format is a bit arbitrary and needs looking up, but 101 is the U.S. standard mm/dd/yyyy, and 103 is the U.K. standard dd/mm/yyyy. SELECT CONVERT(CHAR(10), DATEADD(d, DATEDIFF(d, 0, StartDate),0), 103) [Customer Creation Date], COUNT(*) [Number Of New Customers] FROM Customer WHERE StartDate >= '2011-01-01' GROUP BY DATEADD(d, DATEDIFF(d, 0, StartDate),0) ORDER BY [Customer Creation Date]  #

    Read the article

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

1 2 3 4 5 6  | Next Page >