nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

GerberMatthew

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

Encontre mais publicações marcadas com

Accepted answers

Omar_A

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.

All comments

Omar_A

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.

GerberMatthew

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.

Omar_A

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,

GerberMatthew

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.

Participe da Conversa

Membros conectados podem postar, seguir atualizações e mais. Novo aqui? Registre uma conta gratuita.

Links Rápidos

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.