topic Reference Mosaic Dataset Definition Query "This Month", "Greater than 90 days ago" based on system date in Data Management Questions

Reference Mosaic Dataset Definition Query "This Month", "Greater than 90 days ago" based on system date

GerberMatthew — Sun, 02 Apr 2023 03:43:40 GMT

Hi,

I have a couple of use cases where I would like to set up reference mosaic datasets that only contain data with an Acquistion date of "This Month" or "Greater than 90 days ago" and it automatically updates what is returned as the system date moves forward and into next month.

Is there a query syntax that can be applied to the reference mosaic dataset that will honor current system date to do this raster return?

Raster Mosaic dataset is stored in SQL SDE Geodatabase Version 10.8

Re: Reference Mosaic Dataset Definition Query "This Month", "Greater than 90 days ago" based on system date

Omar_A — Sun, 02 Apr 2023 08:29:46 GMT

The syntax will depend on the specific database management system being used. For SQL Server, you can use the GETDATE() function to get the current date and then use the DATEADD() function to subtract the appropriate number of days to get the desired date range. Here is an example SQL query:

SELECT * FROM mosaic_dataset
WHERE acquisition_date >= DATEADD(day, -90, GETDATE())

You can modify the number of days to get the desired date range for your use case.

Re: Reference Mosaic Dataset Definition Query "This Month", "Greater than 90 days ago" based on system date

GerberMatthew — Mon, 03 Apr 2023 04:03:44 GMT

Can I get a validation/verification that this is the simplest syntax for the following 2 queries.

1)Get Current month captures only

eg. Today is the 03/04/2023... so this should return any capture from the 01/04/2023

AcquisitionDate >= DATEADD(month, -1, (DATEADD(day, ((DATEPART (day, GETDATE()))*-1), GETDATE())))

2)Get last month's captures only

eg. Today is the 03/04/2023... so this should return any capture from the 01/03/2023 to the 31/03/2023

AcquisitionDate >= DATEADD(month, -1, (DATEADD(day, ((DATEPART (day, GETDATE()))*-1), GETDATE()))) AND AcquisitionDate <= DATEADD(day, ((DATEPART (day, GETDATE()))*-1), GETDATE())

I feel like it could be simplified by calculating the values required to return this as a like statement to allow "AcquisitionDate LIKE '2023-04%'" AND "AcquisitionDate LIKE '2023-03%'" to be used.

Re: Reference Mosaic Dataset Definition Query "This Month", "Greater than 90 days ago" based on system date

Omar_A — Mon, 03 Apr 2023 14:20:26 GMT

It is possible to use a LIKE statement to retrieve any capture from a specific month, but this approach may not be as efficient as using the DATEADD function. Also, using LIKE statements would not work for the second query, as it needs to retrieve captures within a specific date range.

thanks,

Re: Reference Mosaic Dataset Definition Query "This Month", "Greater than 90 days ago" based on system date

GerberMatthew — Mon, 03 Apr 2023 22:48:15 GMT

Thanks... I did suspect the LIKE would be less efficient as I assumed the only way to do it would be to get the values of current date out as char/strings, do the similar math and then build the variable.