I need help selecting data where the date is in the previous 3 months. (note: I'm not asking for the last 3 months i.e. DATEADD(MONTH, -3, GETDATE()) )  The reason I don't want to use the DATEADD method is that this query could be run in the middle of the month and I don't want it to return data leading up til that point
121968
I have a query going that gets data for an ID for the last 3 months.  I need to tweak it so I get the highest value for each of the three months.  I've tried a couple of things with the aggregate function MAX, but I'm not getting anywhere.  I'm trying to get the max value for each of the past months ....  Here's the data from the query, currently sorted by date (asc):   ID      Date               Value 12410   01/03/2017 12:17    0.000178 12410   01/10/2017 11:36    0.000186 12410   01/17/2017 11:27    0.000189 12410   01/24/2017 13:09    0.000182 12410   01/31/2017 10:37    0.000169 12410   02/07/2017 11:03    0.000214 12410   02/14/2017 11:52    0.000176 12410   02/21/2017 10:51    0.000200 12410   02/28/2017 12:29    0.000194 12410   03/07/2017 08:39    0.000206   Here's the query:   select AnalysisID as "ID" , AnalysisDateTime as "Date", AnalysisValue as "Value" from AnalysisValueTbl where AnalysisID = 12410 and DatePart(m, AnalysisDateTime) = DatePart(m, DateAdd(m, -3, getdate())) and DatePart(yyyy, AnalysisDateTime) = DatePart(yyyy, DateAdd(m,-3, getdate())) or AnalysisID = 12410 and DatePart(m, AnalysisDateTime) = DatePart(m, DateAdd(m, -2, getdate())) and DatePart(yyyy, AnalysisDateTime) = DatePart(yyyy, DateAdd(m,-2, getdate())) or AnalysisID = 12410 and DatePart(m, AnalysisDateTime) = DatePart(m, DateAdd(m, -1, getdate())) and DatePart(yyyy, AnalysisDateTime) = DatePart(yyyy, DateAdd(m,-1, getdate())) order by AnalysisValue desc
13495
I have 2 tables: Employees, attendanceIn   When I query using LEFT JOIN   SELECT employees.eno,employees.name,employees.dept,attendanceIn.attIn FROM `employees`  LEFT JOIN attendanceIn ON employees.eno = attendanceIn.eno WHERE date(attIn) like '2016-07-02%'   What I got is,     Whats wrong with my Query? How do I get my Expected result?
109382
How to view the result between 2 dates and time ?  My current Code:  SELECT *  FROM [DB].[dbo].[TABLE]  WHERE [USER_ID] = '005'     AND [DATE] BETWEEN '03-04-2016' AND '04-04-2016' AND [TIME] &gt;= '21:00:00'                   -- for date 03-04-2016 AND [TIME] &lt;= '05:00:00'                   -- for date 04-04-2016  ORDER BY [DATE] ASC, [TIME] ASC   This code does not work and I do not know how to fix it  why doesn't this request work ?
99276
How can i insert missing dates from a query i created.  The result below:  Date          Frequency 2014-05-18    5 2014-05-20    7 2014-05-25    7 2014-05-27    6   I want the result to have missing dates with 0 value as shown below:  Date          Frequency 2014-05-18    5 2014-05-19    0 2014-05-20    7 2014-05-21    0 2014-05-22    0 2014-05-23    0 2014-05-24    0 2014-05-25    7 2014-05-26    0 2014-05-27    6   Please note that I have read only access to the server.
101689
I have a table tabName with the following rows:  id(PK) .. Created(datetime)     Resolved(datetime) Status(new/resolved/open) 1          2015-05-10 00:00:00   1970-01-01 ..       open 2          2015-05-10 ..         1970-01-01 ..       new 3          2015-05-10            2015-05-12          resolved 4          2015-05-11 ..         1970-01-01 ..       open 5          2015-05-11 ..         1970-01-01 ..       new 6          2015-05-11            2015-05-11          resolved 7          2015-05-12 ..         1970-01-01 ..       open 8          2015-05-12 ..         1970-01-01 ..       new 9          2015-05-12            2015-05-12          resolved   NOTE:  1970-01-01 .. is default time for all the tickets which are still open.  I have to frame a query to get the count of tickets still open, date wise. Say like on 10 May I have 3 tickets opened. Noting that for id = 3, resolved date is not 10 but it's closed on 12 May.  I framed a query like  SELECT DATE_FORMAT(Created, '%d-%m') AS createdd, COUNT(1) FROM tabName WHERE DATE_FORMAT(Resolved, '%d-%m') &gt; DATE_FORMAT(Created, '%d-%m')       AND YEAR(Created) = '2015' GROUP BY createdd;   The above query gets all the tickets whose Resolved date is greater than the date which is considered for the count. This may count newer tickets as well. And also if the older tickets which are not resolved yet is not counted.  Is there any other way to frame the required query? I have been juggling with this query from last 3 days. Not able to find out still.  Updated: As suggested by Abhik, i have tried in sqlfiddle site.  Here is what i created:  CREATE TABLE test    (idint,createddatetime,resolveddatetime, status varchar(15), PRIMARY KEY (id));    INSERT INTO test       (id,'created','resolved',status`) VALUES       (1,'2015-05-10 00:00:00','1970-01-01 00:00:00','open'),    (2,'2015-05-10 00:00:00','1970-01-01 00:00:00','new'),    (3,'2015-05-10 00:00:00','2015-05-12 00:00:00','resolved'),    (4,'2015-05-11 00:00:00','1970-01-01 00:00:00','open'),    (5,'2015-05-11 00:00:00','1970-01-01 00:00:00','new'),    (6,'2015-05-11 00:00:00','2015-05-11 00:00:00','resolved'),    (7,'2015-05-12 00:00:00','1970-01-01 00:00:00','open'),    (8,'2015-05-12 00:00:00','1970-01-01 00:00:00','new'),    (9,'2015-05-12 00:00:00','1970-01-01 00:00:00','open')    ;   `   When i run  ` select created_date, @open:= @open+open_ticket-resolved as open from(  select   date_format(created,'%d-%m') as created_date ,  sum( case when status='open' then 1 else 0 end )+  sum( case when status='new' then 1 else 0 end )+  sum( case when status='resolved' and ( date(resolved) > date(created) or  date(resolved) = date(created)) then 1 else 0 end ) as open_ticket,  sum(case when status = 'resolved' and date(resolved) = date(created) then 1   else 0 end) as resolved  from test  where  YEAR(Created) = '2015'  group by created_date )x,  (select @open:=0)r order by created_date ; `  I got following output  created_date   open 10-05   3 11-05   5 12-05   8  Here count of still open tickets for 12-05 must be 7. The above query includes ticket id "3" , which is closed on 12-05. So is there any way to exclude this? If i am able to get this right, then i will have full fledged query.
9891
I have a series of ranges with start dates and end dates. I want to check to see if a date is within that range.  Date.before() and Date.after() seem to be a little awkward to use. What I really need is something like this pseudocode:  boolean isWithinRange(Date testDate) {     return testDate &gt;= startDate &amp;&amp; testDate &lt;= endDate; }   Not sure if it's relevant, but the dates I'm pulling from the database have timestamps.
81396
I am trying to convert a date with individual parts such as 12, 1, 2007 into a datetime in SQL Server 2005. I have tried the following:  CAST(DATEPART(year, DATE)+'-'+ DATEPART(month, DATE) +'-'+ DATEPART(day, DATE) AS DATETIME)   but this results in the wrong date. What is the correct way to turn the three date values into a proper datetime format.
51700
MySQL column > sdate, edate ( its 2 column).  sdate is start date for project starting and edate is end date for project ending.  so i need to make search between them..  &lt;strong&gt;Search&lt;/strong&gt;&lt;br /&gt; &lt;form method="post" action="search.php"&gt;   Start Report Date : &lt;input type="text" name="sdate" /&gt;   End Report Date : &lt;input type="text" name="edate" /&gt;   &lt;input type="submit" name="Submit" value="Search" /&gt; &lt;/form&gt;  This is example data in mysql sdate             Project Name      edate 22 December 2008  project 1         23 December 2008 25 December 2008  project 2         26 December 2008 24 December 2008  project 3         27 December 2008 1  January 2008   project 4         20 January 2008 10 December 2008  project 5         12 December 2008   so let say a user entered sdate ( eg, 22 December 2008 ) and edate ( eg, 30 December 2008 ).  It should display  22 December 2008  project 1         23 December 2008 25 December 2008  project 2         26 December 2008 24 December 2008  project 3         27 December 2008   So i need a php code sql query which should display entries lies between those 2 dates..  Please help me..   Thanks very much..
107720
If I have a table that (among other columns) has two DATETIME columns, how would I select the most recent date from those two columns.  Example:  ID     Date1     Date2  1      1/1/2008   2/1/2008  2      2/1/2008   1/1/2008  3      1/10/2008  1/10/2008   If I wanted my results to look like   ID     MostRecentDate  1      2/1/2008  2      2/1/2008  3      1/10/2008   Is there a simple way of doing this that I am obviously overlooking? I know I can do subqueries and case statements or even write a function in sql server to handle it, but I had it in my head that there was a max-compare type function already built in that I am just forgetting about.
60004
Is it possible to convert a timestamp to a date format in a query?  This is the code I am using.  $query = db_select('my_submissions', 's'); $query-&gt;fields('s', array('sid','name','company','email','phone','is_customer','submitted','count')); $entries = $query-&gt;execute();  $rows = array(); foreach ($entries as $entry) {   $row = array_map('check_plain', (array) $entry);   $row['submitted'] = $row['submitted'] ? date('d.m.Y H:i', $row['submitted']) : "";   $rows[] = $row; }
74540
I have a query pattern that must be very common, but I don't know how to write an efficient query for it.  I want to look up the rows of a table that correspond to "the most recent date not after" the rows of another table.  I have a table, inventory say, which represents the inventory I hold on a certain day.  date       | good | quantity ------------------------------ 2013-08-09 | egg  | 5 2013-08-09 | pear | 7 2013-08-02 | egg  | 1 2013-08-02 | pear | 2   and a table, "price" say, which holds the price of a good on a given day  date       | good | price -------------------------- 2013-08-07 | egg  | 120 2013-08-06 | pear | 200 2013-08-01 | egg  | 110 2013-07-30 | pear | 220   How can I efficiently get the "most recent" price for each row of the inventory table, i.e.  date       | pricing date | good | quantity | price ---------------------------------------------------- 2013-08-09 | 2013-08-07   | egg  | 5        | 120 2013-08-09 | 2013-08-06   | pear | 7        | 200 2013-08-02 | 2013-08-01   | egg  | 1        | 110 2013-08-02 | 2013-07-30   | pear | 2        | 220   I know one way of doing this:  select inventory.date, max(price.date) as pricing_date, good from inventory, price where inventory.date &gt;= price.date and inventory.good = price.good group by inventory.date, good   and then join this query again to inventory. For large tables even doing the first query (without joining again to inventory) is very slow.  However, the same problem is quickly solved if I simply use my programming language to issue one max(price.date) ... where price.date &lt;= date_of_interest ... order by price.date desc limit 1 query for each date_of_interest from the inventory table, so I know there is no computational impediment.  I would, however, prefer to solve the whole problem with a single SQL query, because it would allow me to do further SQL processing on the result of the query.  Is there a standard way to do this efficiently? It feels like it must come up often and that there should be a way to write a fast query for it.  I'm using Postgres, but an SQL-generic answer would be appreciated.
84648
I've read many posts about using pivot to get the data in the format I want, but the more I read the more confused I get.  I have this data:    That I'm trying to get into a format similar to this:   For the most part, everything I try results in an SQL error, and the only successful attempt I've had didn't return the data in the format I'm looking for.  Any help would be appreciated.
94071
How do I get the difference in days between 2 dates in SQLite? I have already tried something like this:  SELECT Date('now') - DateCreated FROM Payment   It returns 0 every time.
18044
When I use , the results I get don't reflect recent activity. Sometimes the data I get back are two months out of date. What's wrong? This is intended to be a canonical reference for questions about SEDE not reflecting real-time results.
84049
So I am trying to find a formula for excel. Bascically I have two dates and I need to know the number of months between them. But to add complexity, I need a rule in there that if the earlier date is the 1st to 15th it will be a whole month but if it is 16th to end of the month it will be a half month.  For example: Date 1: April 4, 2012 Date 2: December 31, 2012 Answer: Number of months would be 9 months  If Date 1 was April 16, 2012, with the same Date 2 of December 31, 2012, then the number of months would equal 8.5.  Can anyone help me with this one? You help would be greatly appreciated. many thanks
95710
I have End Date column and I want to add 14 Days on it auto when created, can I do it by workflow? Can I do it via jQuery. If yes how? if no how I can do it?
99127
Is there a function or method to give the last day of a month? For example if the date is 27 feb 2013, the last date should be 28 feb 2013 , it become 29 if its a leap year. if the date was 2 jan then it should return 31 Jan.  If there is no function then any ideas on how we can get this?  Thanks