Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2014
    Posts
    2

    Unanswered: SQL return only business days

    I have a Sybase ASE database that I'm trying to pull the current day and previous business day from. I wrote the following, however if run it on a Monday, it doesn't get previous Friday as the weekend gets in the way. Can anyone tell me how I can resolve this?

    Code:
     select distinct price_date
        from table
        where price_date IN (convert(varchar(10), dateadd(day, 0, getdate()), 101), 
            convert(varchar(10), dateadd(day, -1, getdate()),101))
    The convert statements are used to trim the time from the datetime stamp, messy I know but it works. I'm unsure if a better solution may be to use the businessday() function in sybase, but I have no idea how to use this. Any help would be greatly appreciated!

  2. #2
    Join Date
    Aug 2014
    Posts
    2

    SQL return only business days

    I have a Sybase ASE database that I'm trying to pull the current day and previous business day from. I wrote the following, however if run it on a Monday, it doesn't get previous Friday as the weekend gets in the way. Can anyone tell me how I can resolve this?

    Code:
     select distinct price_date
        from table
        where price_date IN (convert(varchar(10), dateadd(day, 0, getdate()), 101), 
            convert(varchar(10), dateadd(day, -1, getdate()),101))
    The convert statements are used to trim the time from the datetime stamp, messy I know but it works. I'm unsure if a better solution may be to use the businessday() function in sybase, but I have no idea how to use this. Any help would be greatly appreciated!

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You really need a calendar table. This is a table that contains a column with every date that is valid/usable for your application and information about each date... That information would include things like holidays, working days (there may be several kinds of working days such as storefront and back office), and anything else yoiu need to track about calendars.

    You would run into a more complex version of the "Monday" problem in a few weeks when Labor Day means that Monday is a holiday so youi need to back up from Tuesday... With a calendar table it is easy to find the previous working day!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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