Alpha Video Training
Results 1 to 20 of 20

Thread: Date Query

  1. #1
    Member
    Real Name
    Alex Muir
    Join Date
    Nov 2000
    Location
    Stocksfield UK
    Posts
    438

    Default Date Query

    Hello

    I am trying to convert a week number and a year into a date.

    For example:

    Monday September 2nd 2019 = Week Number 38.


    Thanks you for your help. It is much appreciated.

    Kind regards

    Alex
    Database Software Solutions Limited
    www.dbasesolution.co.uk
    PRECISE360 -
    LYNCHPIN -

  2. #2
    "Certified" Alphaholic Lance Gurd's Avatar
    Real Name
    Lance Gurd
    Join Date
    Jun 2005
    Location
    Southampton, UK
    Posts
    1,433

    Default Re: Date Query

    Hi Alex have a look at WEEK() ,WEEK_ISO() and WEEK_ISO_FULL() Functions in the documentation. They should point you in the right direction.

  3. #3
    Member
    Real Name
    Alex Muir
    Join Date
    Nov 2000
    Location
    Stocksfield UK
    Posts
    438

    Default Re: Date Query

    Hello Lance

    Sorry, I made a mistake in explaining the query.

    The example should be
    38 2019 = I need to see 02/09/2019

    Thanks you for your help. It is much appreciated.

    Kind regards

    Alex
    Database Software Solutions Limited
    www.dbasesolution.co.uk
    PRECISE360 -
    LYNCHPIN -

  4. #4
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,609

    Default Re: Date Query

    Oops wrong function..
    nth_dow()
    Last edited by Al Buchholz; 09-18-2019 at 02:49 PM.
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  5. #5
    Member
    Real Name
    Allen Klimeck
    Join Date
    Apr 2000
    Location
    Colorado
    Posts
    537

    Default Re: Date Query

    Misunderstood the question sorry
    Last edited by Allen Klimeck; 09-18-2019 at 12:12 PM.

  6. #6
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,355

    Default Re: Date Query

    Not if you only have the year and week number, Al. ( Unless of course I am talking rubbish.)

    For the sake of simplicity, I'd create a table with the week numbers and dates - either the start or end of the week as you wish - and do a lookup based on the week number.
    Ted Giles
    Example Consulting - UK
    .

    http://ec12.example-software.com//
    See our site for Alpha Support, Conversion and Upgrade.

  7. #7
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,609

    Default Re: Date Query

    Quote Originally Posted by Ted Giles View Post
    Not if you only have the year and week number, Al. ( Unless of course I am talking rubbish.)

    For the sake of simplicity, I'd create a table with the week numbers and dates - either the start or end of the week as you wish - and do a lookup based on the week number.
    Does bring up the issue of what date of the week is one looking for? First day through 7th day...

    A date is only one day. A week is seven days.
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  8. #8
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,355

    Default Re: Date Query

    Yep, but week numbers are more of a universally agreed metric.
    If you lookup the process in Excel, you can set a range of 7 days to define a week.
    So, Mon - Sun is one week no., Sun -Sat is another.
    I think you can change the start and end days of the week.

    Interesting challenge though.
    Ted Giles
    Example Consulting - UK
    .

    http://ec12.example-software.com//
    See our site for Alpha Support, Conversion and Upgrade.

  9. #9
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,355

    Default Re: Date Query

    So, what about
    01/01/2019 + Week# times 7
    Ted Giles
    Example Consulting - UK
    .

    http://ec12.example-software.com//
    See our site for Alpha Support, Conversion and Upgrade.

  10. #10
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,609

    Default Re: Date Query

    Quote Originally Posted by Ted Giles View Post
    So, what about
    01/01/2019 + Week# times 7
    Doesn't that give the date of the start of the next week?
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  11. #11
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,609

    Default Re: Date Query

    Quote Originally Posted by alexmuir View Post
    The example should be
    38 2019 = I need to see 02/09/2019

    Alex
    This link says that week 38 of 2019 starts on 16 Sept 2019

    http://week-number.net/calendar-with...bers-2019.html

    This also begs the question of what day of the week 38 do you want? I see implied day 1 - ie Monday.
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  12. #12
    Member
    Real Name
    Allen Klimeck
    Join Date
    Apr 2000
    Location
    Colorado
    Posts
    537

    Default Re: Date Query

    I think it need to be:

    (FristDayOfTheYear + (Week# * 7)) - (7 + dow(FristDayOfTheYear)-1)) = FirstDayOfWeek#
    Last edited by Allen Klimeck; 09-18-2019 at 03:08 PM.

  13. #13
    Member
    Real Name
    Jon P Moody
    Join Date
    Nov 2016
    Posts
    159

    Default Re: Date Query

    how about
    Code:
    yearstart_iso(2019)+(38*7)

  14. #14
    Member
    Real Name
    Alex Muir
    Join Date
    Nov 2000
    Location
    Stocksfield UK
    Posts
    438

    Default Re: Date Query

    Ted

    Thank you for your assistance.

    We have decided to pursue you advice of creating a table.

    Thanks to everyone who offered suggestions.

    Kind regards


    Alex
    Database Software Solutions Limited
    www.dbasesolution.co.uk
    PRECISE360 -
    LYNCHPIN -

  15. #15
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,355

    Default Re: Date Query

    You are welcome.
    It's probably easiest but the down side is that you will need to recreate one for next year.
    Still, with the process done once, it should be relatively easy to replicate.
    Ted Giles
    Example Consulting - UK
    .

    http://ec12.example-software.com//
    See our site for Alpha Support, Conversion and Upgrade.

  16. #16
    "Certified" Alphaholic MoGrace's Avatar
    Real Name
    Robin
    Join Date
    Mar 2006
    Location
    Los Angeles
    Posts
    3,662

    Default Re: Date Query

    Is this query used to find a payroll pay date?
    Robin

    Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

  17. #17
    Member
    Real Name
    Jon P Moody
    Join Date
    Nov 2016
    Posts
    159

    Default Re: Date Query

    Again, why not use
    Code:
    teststr="38 2019"
    ?yearstart_iso(val(word(teststr,2)))+((val(word(teststr,1))*7)-1)
    Single line of code, no tables, no maintenance required.

  18. #18
    Member
    Real Name
    Alex Muir
    Join Date
    Nov 2000
    Location
    Stocksfield UK
    Posts
    438

    Default Re: Date Query

    Quote Originally Posted by MoGrace View Post
    Is this query used to find a payroll pay date?
    No its not. But there is a tale!

    We are implementing our PRECISE360 application into a supplier to a multi international who supplies their product orders via schedules.

    Initially it was hard copy.
    Despite a lot of effort using scanning software, we couldn't get it to provide reliable results.


    After months and scores of calls we eventually got them to supply the schedules in a csv format.
    These schedules list weekly requirements for products starting each Monday.

    We import the schedules into PRECISE360 which eliminates 3+ hours of highly error prone admin time and automatically creates works orders for our client.
    Another boring, tedious job eliminated thanks to the capability of Alpha!

    Kind regards


    Alex
    Database Software Solutions Limited
    www.dbasesolution.co.uk
    PRECISE360 -
    LYNCHPIN -

  19. #19
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,355

    Default Re: Date Query

    @ Jon, that's a cool piece of logic!
    Changing the -1 to -7 gives the Monday for week 38.
    A challenge might be - with a Multi-National - to keep the dates in some sort of order or compliance across regional boundaries.

    Anyway, nice job.
    Ted Giles
    Example Consulting - UK
    .

    http://ec12.example-software.com//
    See our site for Alpha Support, Conversion and Upgrade.

  20. #20
    "Certified" Alphaholic MoGrace's Avatar
    Real Name
    Robin
    Join Date
    Mar 2006
    Location
    Los Angeles
    Posts
    3,662

    Default Re: Date Query

    teststr="38 2019"
    vdate=yearstart_iso(val(word(teststr,2)))+((val(word(teststr,1))*7)-7)
    ?ctod(ui_get_date_calendar("",dtoc(vdate)))
    = {09/16/2019}

    pretty cool
    Robin

    Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

Similar Threads

  1. form query on Date()
    By hov333 in forum Alpha Five Version 10 - Desktop Applications
    Replies: 7
    Last Post: 01-25-2016, 06:04 PM
  2. Date Query filter
    By Mike Wilson in forum Alpha Five Version 7
    Replies: 13
    Last Post: 08-14-2007, 11:35 AM
  3. Date Query
    By Phil Rolf in forum Alpha Five Version 6
    Replies: 2
    Last Post: 04-06-2005, 09:37 AM
  4. Query by Name and Date
    By David in forum Alpha Five Version 5
    Replies: 9
    Last Post: 01-13-2005, 06:54 AM
  5. Query on date
    By scottly in forum Alpha Five Version 5
    Replies: 29
    Last Post: 11-18-2003, 05:49 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •