<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.2900.6104" name=GENERATOR></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=109463810-13072011><FONT face=Arial
color=#0000ff size=2>Michael,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=109463810-13072011><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=109463810-13072011><FONT face=Arial
color=#0000ff size=2>Thanks for that; got me going in the right
direction.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=109463810-13072011><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=109463810-13072011><FONT face=Arial
color=#0000ff size=2>I was still experiencing 2 main
problems:</FONT></SPAN></DIV>
<DIV dir=ltr style="MARGIN-RIGHT: 0px" align=left><SPAN
class=109463810-13072011><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV dir=ltr style="MARGIN-RIGHT: 0px" align=left><SPAN
class=109463810-13072011><FONT face=Arial color=#0000ff size=2>1) Command "<FONT
face="Courier New" color=#000000>DATE /T</FONT>" returning date format as
DD/MM/YYYY (SQL needs YYYY/MM/DD)</FONT></SPAN></DIV>
<DIV dir=ltr style="MARGIN-RIGHT: 0px" align=left><SPAN
class=109463810-13072011><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV dir=ltr style="MARGIN-RIGHT: 0px" align=left><SPAN
class=109463810-13072011><FONT face=Arial color=#0000ff size=2>2) Still couldn't
calculate one month ago in the SQL string (i.e. TODAY - 30
Days)</FONT></SPAN></DIV>
<DIV dir=ltr style="MARGIN-RIGHT: 0px" align=left><SPAN
class=109463810-13072011><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV dir=ltr style="MARGIN-RIGHT: 0px" align=left><SPAN
class=109463810-13072011><FONT face=Arial color=#0000ff size=2>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.</FONT></SPAN></DIV>
<DIV dir=ltr style="MARGIN-RIGHT: 0px" align=left><SPAN
class=109463810-13072011><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV><SPAN class=109463810-13072011><FONT face="Courier New" size=2>:: Break
down date and store as variables ::<BR>FOR /f "tokens=1-3 delims=/ " %%a in
('date /t') do (SET day=%%a)<BR>:: For Month variable minus 1 | for one month
ago ::<BR>FOR /f "tokens=1-3 delims=/ " %%a in ('date /t') do (SET /a
lastmonth=%%b-1)<BR>FOR /f "tokens=1-3 delims=/ " %%a in ('date /t') do (SET
year=%%c)</FONT></SPAN></DIV>
<DIV><FONT face="Courier New"></FONT> </DIV>
<DIV><SPAN class=109463810-13072011><FONT face="Courier New" size=2>:: If it's
Jan (month = 01) last month will = 0
(ERROR) ::<BR>:: Therefore change month to
December (12) & minus 1 for year ::<BR>IF %lastmonth%==0 SET /a
year=%year%-1<BR>IF %lastmonth%==0 SET lastmonth=12</FONT></SPAN></DIV>
<DIV><FONT face="Courier New"></FONT> </DIV>
<DIV><SPAN class=109463810-13072011><FONT face="Courier New" size=2>:: Check
if month is one with less than 31 days ::<BR>IF %lastmonth%==2 GOTO FEB<BR>IF
%lastmonth%==4 GOTO APR<BR>IF %lastmonth%==6 GOTO JUN<BR>IF %lastmonth%==9
GOTO SEP<BR>IF %lastmonth%==11 GOTO NOV<BR>GOTO CONT</FONT></SPAN></DIV>
<DIV><FONT face="Courier New"></FONT> </DIV>
<DIV><SPAN class=109463810-13072011><FONT face="Courier New" size=2>:: Check
if day is greater than number of days in that month ::<BR>:: Change if
required ::<BR>:FEB<BR>IF %day% GTR 28 SET day=28<BR>GOTO
CONT</FONT></SPAN></DIV>
<DIV><FONT face="Courier New"></FONT> </DIV>
<DIV><SPAN class=109463810-13072011><FONT face="Courier New" size=2>:APR<BR>IF
%day% GTR 30 SET day=30<BR>GOTO CONT</FONT></SPAN></DIV>
<DIV><FONT face="Courier New"></FONT> </DIV>
<DIV><SPAN class=109463810-13072011><FONT face="Courier New" size=2>:JUN<BR>IF
%day% GTR 30 SET day=30<BR>GOTO CONT</FONT></SPAN></DIV>
<DIV><FONT face="Courier New"></FONT> </DIV>
<DIV><SPAN class=109463810-13072011><FONT face="Courier New" size=2>:SEP<BR>IF
%day% GTR 30 SET day=30<BR>GOTO CONT</FONT></SPAN></DIV>
<DIV><FONT face="Courier New"></FONT> </DIV>
<DIV><SPAN class=109463810-13072011><FONT face="Courier New" size=2>:NOV<BR>IF
%day% GTR 30 SET day=30<BR>GOTO CONT</FONT></SPAN></DIV>
<DIV><FONT face="Courier New"></FONT> </DIV>
<DIV><SPAN class=109463810-13072011><FONT face="Courier New"
size=2>:CONT<BR>:: If month < 10 (before Oct) leading zero required
::<BR>IF 1%lastmonth% LSS 100 SET lastmonth=0%lastmonth%</FONT></SPAN></DIV>
<DIV><FONT face="Courier New"></FONT> </DIV>
<DIV><SPAN class=109463810-13072011><FONT face="Courier New" size=2>:: Combine
date variables to SQL format date ::<BR>SET
monthago=%year%/%lastmonth%/%day%</FONT></SPAN></DIV>
<DIV><FONT face="Courier New"></FONT> </DIV>
<DIV><SPAN class=109463810-13072011><FONT face="Courier New" size=2>:: ogr2ogr
command using combined date variable ::<BR>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</FONT></SPAN></DIV></BLOCKQUOTE>
<DIV dir=ltr style="MARGIN-RIGHT: 0px" align=left><SPAN
class=109463810-13072011><FONT face=Arial color=#0000ff size=2>Thanks
again,</FONT></SPAN></DIV>
<DIV dir=ltr style="MARGIN-RIGHT: 0px" align=left><SPAN
class=109463810-13072011><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV dir=ltr style="MARGIN-RIGHT: 0px" align=left><SPAN
class=109463810-13072011><FONT face=Arial color=#0000ff
size=2>Rob</FONT></SPAN></DIV><BR>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B> Michael Shishcu
[mailto:micklesh@gmail.com] <BR><B>Sent:</B> 11 July 2011 16:19<BR><B>To:</B>
Langford, Robert<BR><B>Cc:</B> gdal-dev@lists.osgeo.org<BR><B>Subject:</B> Re:
[gdal-dev] ogr2ogr SQL Date Calculation<BR></FONT><BR></DIV>
<DIV></DIV>Hi Robert,<BR><BR>there is a Date command for windows
commandline<BR><A
href="http://ss64.com/nt/date.html">http://ss64.com/nt/date.html</A><BR><BR>hope
this helps<BR><BR>regards,<BR>michael<BR><BR>
<DIV class=gmail_quote>On Mon, Jul 11, 2011 at 6:11 PM, Langford, Robert <SPAN
dir=ltr><<A
href="mailto:Robert.Langford@salford.gov.uk">Robert.Langford@salford.gov.uk</A>></SPAN>
wrote:<BR>
<BLOCKQUOTE class=gmail_quote
style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0.8ex; BORDER-LEFT: #ccc 1px solid">
<DIV><FONT face="Arial, sans-serif" size=2>
<DIV>All,</DIV>
<DIV> </DIV>
<DIV>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.</DIV>
<DIV> </DIV>
<DIV>Therefore If the task ran today (11/07/2011) the below line would return
the required data:</DIV>
<DIV> </DIV>
<DIV>ogr2ogr -f "MapInfo File" -sql "select * from DCPOLY where
DATE_CREA1>'2011/06/11'" D:\Data\Plan_Apps.tab D:\Data\DCPOLY.shp</DIV>
<DIV> </DIV>
<DIV>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:</DIV>
<DIV> </DIV>
<DIV>DATE_CREA1> Todays_Date - 30</DIV>
<DIV> </DIV>
<DIV>Does anyone know of a way to automate this using something like;
GetDate(), Now(), sysdate, Date /t, etc? Any help would be greatly
appreciated.</DIV>
<DIV> </DIV>
<DIV>Many thanks,</DIV>
<DIV> </DIV>
<DIV>Rob</DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV><B>Robert Langford</B></DIV>
<DIV><B>Systems Developer (GIS)</B></DIV>
<DIV><B>Corporate Applications Team</B></DIV>
<DIV><B>Salford City Council</B></DIV>
<DIV> </DIV>
<DIV>Tel: 0161 793 2492</DIV>
<DIV>Email: <A href="mailto:robert.langford@salford.gov.uk"
target=_blank><FONT
color=#0000ff><U>robert.langford@salford.gov.uk</U></FONT></A></DIV>
<DIV>Web: <A href="http://www.salford.gov.uk/"
target=_blank><FONT color=#0000ff><U>www.salford.gov.uk</U></FONT></A></DIV>
<DIV> </DIV>
<DIV><FONT size=1>Salford Civic Centre,</FONT></DIV>
<DIV><FONT size=1>Chorley Road, Swinton, M27 5DA</FONT></DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV> </DIV></FONT></DIV>
<TABLE>
<TBODY>
<TR>
<TD bgColor=#ffffff><FONT color=#000000><PRE>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 <A href="http://www.salford.gov.uk/e-mail" target=_blank>http://www.salford.gov.uk/e-mail</A>. Thank you.</PRE></FONT></TD></TR></TBODY></TABLE><BR>_______________________________________________<BR>gdal-dev
mailing list<BR><A
href="mailto:gdal-dev@lists.osgeo.org">gdal-dev@lists.osgeo.org</A><BR><A
href="http://lists.osgeo.org/mailman/listinfo/gdal-dev"
target=_blank>http://lists.osgeo.org/mailman/listinfo/gdal-dev</A><BR></BLOCKQUOTE></DIV><BR></BODY></HTML>
<table><tr><td bgcolor=#ffffff><font color=#000000><pre>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.</pre></font></td></tr></table>