D60 data should be for purchases from 31 to 60 days ago, not everything purchased in the last 60 days. Note that each time period has separate data. The results might look similar to the following: Album D30 D60 D90 You can use this function to write your accountant's requested pivot query. MySQL subtracts the DateOfPurchase from "now" and returns an integer representing the number of days between those two dates. SELECT DATEDIFF(now(), DateOfPurchase) FROM Purchases MySQL has a function that can be used to compare two dates - DATEDIFF: He showed the accountant, and she said she wants to see a similar query, but with sales in the last 30, 60, and 90 days. Your boss loved the pivot query you wrote in the first project. SearchCursor (tbl, "START_DATE", sql ), sep = "\n" ) (datetime. datetime ( 2018, 7, 30, 0, 0 ), ) > # print records where START_DATE within 90 days of now > sql = "CURRENT_DATE - START_DATE > print ( *arcpy. SearchCursor (tbl, "START_DATE" ), sep = "\n" ) (datetime. ![]() 1 2 3 4 5 > # print all records > print ( *arcpy. InsertCursor (tbl, "START_DATE" ) as cur. AddField_management (tbl, "START_DATE", "DATE" ) > with arcpy. CreateTable_management (sgdb, "tbl" ) > arcpy. > from datetime import datetime as dt, timedelta as td If you want to select all records whose START_DATE is within 90 days of the current date, all you need is my suggestion above: "CURRENT_DATE - START_DATE > # python3 > import arcpy You can change this by adding 90 to second date listed. In both solutions, it assumes you are interested in only 90 days before the given date. ![]() This will return all dates between Jand Oct 22, 2017. Start_date >= date '' - 90 and start_date <= date '' You can easily substitute CURRENT DATE with any date (see below as example).Īs for the second one here it could be any date: ![]() That means for today this would return values like (EXTRACT (MONTH FROM Start_Date ) - 1 ) * 30 + EXTRACT (DAY FROM Start_Date ) BETWEEN ( (EXTRACT (MONTH FROM CURRENT_DATE ) - 1 ) * 30 + EXTRACT (DAY FROM CURRENT_DATE ) - 90 ) AND ( (EXTRACT (MONTH FROM CURRENT_DATE ) - 1 ) * 30 + EXTRACT (DAY FROM CURRENT_DATE ) ) It is not perfect since it assumes each month has exactly 30 days. Or are you trying to find all dates that are 90 days within a provided past date? Those two problems would have different solutions.įor the first question. Are you interested in finding all calendar dates that fall within 90 days of this time each year. It also has a problem with dates that fall in January as those records are not selected by add +3 from current month (10).Ĭan you combine EXTRACT(DAY.) AND EXTRACT(MONTH.) in the same query? I've been trying but keep getting a syntax error. This is using the MONTH field, and it works to a degree, but is not quite precise. (EXTRACT(MONTH FROM Start_Date) >= EXTRACT(MONTH FROM CURRENT_DATE)) and (EXTRACT(MONTH FROM Start_Date) <= EXTRACT(MONTH FROM CURRENT_DATE)+3) Is there a way to use the day & month of the "Start_Date" field to determine if the Start_Date is within 90 days of today's date or Current Date. The issue I have is that the Date field (DD/MM/YYYY) I am using, I can only use the DD/MM portion of this field as the year part is not a future year date. I have a feature class in a fgdb (ArcMap 10.6) and I want to add a definition query which shows me records that have a 'Start_Date' within 90 days of the current date.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |