Subscribe to this thread
Home - General / All posts - Subtracting hours
vincent

1,972 post(s)
#27-Mar-20 14:11

This is an adding to this thread : http://www.georeference.org/forum/t143915.12#143925

I'm trying to remove 4 hours from my GPS point's date, to make time local.

I didn't find a DateAdd function like in M8.

I'm more fluent in javascript, but I tried in C#, following the example above. It doesn't work. Any help appreciated.

So far I have :

// C#

// Adaptation du script de adamw -- http://www.georeference.org/forum/t136681.3#136693

class Script

{

public static DateTime FixHour(System.DateTime p)

{

return (p.AddHours(-4));

}

static Manifold.Context Manifold;

static void Main() 

{

}

}

and

--SQL

FUNCTION FixHour(d DATETIME) DATETIME AS SCRIPT [Script]

  ENTRY 'Script.FixHour';

VALUES (FixHour(3/19/2020 19:08:32));

adamw


10,447 post(s)
#30-Mar-20 13:05

There are three small things to fix.

1. The return value of the script function should be System.DateTime, not DateTime. Attempting to run the script fails with an error message that points to this.

2. The name of the argument to FixHour should be @d, not d. Trying to parse the FUNCTION statement fails because of this but does not explain why - we'll make it say what the problem is.

3. The date literal passed to FixHour in VALUES should be enclosed in #...#. Trying to parse the VALUES statement fails because of this, also without giving you any idea what specifically the problem is - it is kind of hard to guess in this case that the problem is a lack of #...#, but we will try to at least say something about the value passed to FixHour.

Corrections in bold:

// C#

// Adaptation du script de adamw -- http://www.georeference.org/forum/t136681.3#136693

class Script

{

public static System.DateTime FixHour(System.DateTime p)

{

return (p.AddHours(-4));

}

static Manifold.Context Manifold;

static void Main() 

{

}

}

Query:

--SQL

FUNCTION FixHour(@d DATETIME) DATETIME AS SCRIPT [Script]

  ENTRY 'Script.FixHour';

VALUES (FixHour(#3/19/2020 19:08:32#));

Hope this helps.

vincent

1,972 post(s)
#30-Mar-20 13:36

Thank you, this will be helpful indeed.

I found the comment in the manual regarding the parameter @ reference. But I found nothing about wrapping dates in #...#. Just found examples.

Dimitri


7,413 post(s)
#30-Mar-20 14:17

wrapping dates in #...#.

Easy to miss. In case you need other literals, syntax, etc., those are covered in the Queries topic and in the SQL Constants and Literals topic.

vincent

1,972 post(s)
#30-Mar-20 19:23

It's working nicely. But I wonder what can replace the date in bold in the code below ? Something more generic ?

--SQL

FUNCTION FixHour(@d DATETIME) DATETIME AS SCRIPT [Script]

  ENTRY 'Script.FixHour';

VALUES (FixHour(#3/19/2020 19:08:32#));

Update [20031901] Set [date_time_ajuste] = 

FixHour([date_time]

The whole thing is updating a field with the corrected time value from another field.

Thanks again.

adamw


10,447 post(s)
#01-Apr-20 16:16

Wasn't the VALUES statement just a test to see if the function works? It seems to me, you can just remove it.

vincent

1,972 post(s)
#01-Apr-20 18:15

Obviously. Sorry about that one ;)

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.