[gdal-dev] ogr2ogr SQL Date Calculation

[Langford, Robert]

Michael,

Thanks for that; got me going in the right direction.

I was still experiencing 2 main problems:

1) Command "DATE /T" returning date format as DD/MM/YYYY (SQL needs YYYY/MM/DD)

2) Still couldn't calculate one month ago in the SQL string (i.e. TODAY - 30 Days)

However I think I've cracked all in a Batch file.  I'll post it below in case anyone needs anything similar, I will state that this might not be the most elegant of scripts but it does appear to work!  If anyone has any better ideas please let me know.

:: Break down date and store as variables ::
FOR /f "tokens=1-3 delims=/ " %%a in ('date /t') do (SET day=%%a)
:: For Month variable minus 1 | for one month ago ::
FOR /f "tokens=1-3 delims=/ " %%a in ('date /t') do (SET /a lastmonth=%%b-1)
FOR /f "tokens=1-3 delims=/ " %%a in ('date /t') do (SET year=%%c)

:: If it's Jan (month = 01) last month will = 0 (ERROR)       ::
:: Therefore change month to December (12) & minus 1 for year ::
IF %lastmonth%==0 SET /a year=%year%-1
IF %lastmonth%==0 SET lastmonth=12

:: Check if month is one with less than 31 days ::
IF %lastmonth%==2 GOTO FEB
IF %lastmonth%==4 GOTO APR
IF %lastmonth%==6 GOTO JUN
IF %lastmonth%==9 GOTO SEP
IF %lastmonth%==11 GOTO NOV
GOTO CONT

:: Check if day is greater than number of days in that month ::
:: Change if required ::
:FEB
IF %day% GTR 28 SET day=28
GOTO CONT

:APR
IF %day% GTR 30 SET day=30
GOTO CONT

:JUN
IF %day% GTR 30 SET day=30
GOTO CONT

:SEP
IF %day% GTR 30 SET day=30
GOTO CONT

:NOV
IF %day% GTR 30 SET day=30
GOTO CONT

:CONT
:: If month < 10 (before Oct) leading zero required ::
IF 1%lastmonth% LSS 100 SET lastmonth=0%lastmonth%

:: Combine date variables to SQL format date ::
SET monthago=%year%/%lastmonth%/%day%

:: ogr2ogr command using combined date variable ::
ogr2ogr -f "MapInfo File" -a_srs "EPSG:27700" -sql "SELECT * FROM DCPOLY WHERE DATE_CREA1>='%monthago%'" D:\Data\Plan_Apps.tab D:\Data\DCPOLY.shp
Thanks again,

Rob

________________________________
From: Michael Shishcu [mailto:micklesh at gmail.com]
Sent: 11 July 2011 16:19
To: Langford, Robert
Cc: gdal-dev at lists.osgeo.org
Subject: Re: [gdal-dev] ogr2ogr SQL Date Calculation

Hi Robert,

there is a Date command for windows commandline
http://ss64.com/nt/date.html

hope this helps

regards,
michael

On Mon, Jul 11, 2011 at 6:11 PM, Langford, Robert <Robert.Langford at salford.gov.uk<mailto:Robert.Langford at salford.gov.uk>> wrote:
All,

I am trying to use ogr2ogr as a scheduled command line task to run weekly to convert a .SHP file to a .TAB file whilst filtering the data based on a date field.  I would like to only convert records that have a date from the last 30 days.

Therefore If the task ran today (11/07/2011) the below line would return the required data:

ogr2ogr -f "MapInfo File" -sql "select * from DCPOLY where DATE_CREA1>'2011/06/11'" D:\Data\Plan_Apps.tab D:\Data\DCPOLY.shp

Obviously if the task ran tomorrow the date would have to be changed to '2011/06/12'.  So I need the field SQL to be something like:

DATE_CREA1> Todays_Date - 30

Does anyone know of a way to automate this using something like; GetDate(), Now(), sysdate, Date /t, etc?  Any help would be greatly appreciated.

Many thanks,

Rob


Robert Langford
Systems Developer (GIS)
Corporate Applications Team
Salford City Council

Tel:      0161 793 2492
Email:  robert.langford at salford.gov.uk<mailto:robert.langford at salford.gov.uk>
Web:    www.salford.gov.uk<http://www.salford.gov.uk/>

Salford Civic Centre,
Chorley Road, Swinton, M27 5DA




DISCLAIMER: The information in this message is confidential and may be legally privileged. It is intended solely for the addressee.

Access to this message by anyone else is unauthorised. If you are not the intended recipient, any disclosure, copying, or distribution of the message, or any action or omission taken by you in reliance on it, is prohibited and may be unlawful.
As a public body, Salford City Council may be required to disclose this email [or any response to it] under the Freedom of Information Act 2000, unless the information in it is covered by one of the exemptions in the Act.
Please immediately contact the sender if you have received this message in error.

For the full disclaimer please access http://www.salford.gov.uk/e-mail.  Thank you.


_______________________________________________
gdal-dev mailing list
gdal-dev at lists.osgeo.org<mailto:gdal-dev at lists.osgeo.org>
http://lists.osgeo.org/mailman/listinfo/gdal-dev


DISCLAIMER: The information in this message is confidential and may be legally privileged. It is intended solely for the addressee.

Access to this message by anyone else is unauthorised. If you are not the intended recipient, any disclosure, copying, or distribution of the message, or any action or omission taken by you in reliance on it, is prohibited and may be unlawful.
As a public body, Salford City Council may be required to disclose this email [or any response to it] under the Freedom of Information Act 2000, unless the information in it is covered by one of the exemptions in the Act. 
Please immediately contact the sender if you have received this message in error. 

For the full disclaimer please access http://www.salford.gov.uk/e-mail.  Thank you.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/gdal-dev/attachments/20110713/7952d6f7/attachment.html