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

1294
4
Jump to solution
04-01-2023 08:43 PM
GerberMatthew
New Contributor III

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

0 Kudos
1 Solution

Accepted Solutions
Omar_A
by Esri Contributor
Esri Contributor

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.

I hope this helps
Best,
Omar

View solution in original post

4 Replies
Omar_A
by Esri Contributor
Esri Contributor

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.

I hope this helps
Best,
Omar
GerberMatthew
New Contributor III

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.

0 Kudos
Omar_A
by Esri Contributor
Esri Contributor

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, 

 

I hope this helps
Best,
Omar
0 Kudos
GerberMatthew
New Contributor III

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.

0 Kudos