Get Date Based on TimeOffSet and Day

  • Team

    I have a table which carries the below columns and values

    TimeOffSet Column > Values are : +00:00, -05:00, -04:00, -08:00, -07:00, -05:30

    WeekDay Column > Values are: Sun, Mon, Tue, Wed, Thu, Fri, Sat, Sun

    Time Field  Column> 02:00, 10:00, 23:00

    Expected : I want to O/P which is the Date Field, Time Field (Date field concatenated with Time Field) based on Input value TimeOffset, Time Field &  WeekDay.

    For Ex: Assume Current Date is 03/01/2021.

    Input Value  1 -  TimeOffSet Value as -07:00 , Week Day as Sun, TimeField 00:15.

    OutPut Expected - It should give me Sunday of SAC TimeZone > 02/28/2020, 00:15

    Can anyone help?

     

     

     

     

    • This topic was modified 1 month, 1 week ago by  Rock.
    • This topic was modified 1 month, 1 week ago by  Rock.
  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • This was removed by the editor as SPAM

  • Sorry, hit enter. It would be good to understand if you want 1 or 2 fields in the result. The way you write this, I'm not sure. I think you want a datetimeoffset single value, but I would be wrong.

    You might look at the AT TIME ZONE as well.

     

  • Steve Jones - SSC Editor wrote:

    Sorry, hit enter. It would be good to understand if you want 1 or 2 fields in the result. The way you write this, I'm not sure. I think you want a datetimeoffset single value, but I would be wrong.

    You might look at the AT TIME ZONE as well.

    AT TIME ZONE is not available in SQL Server 2012.

    It would help if we had sample data and expected results - but I will say this is going to be problematic.  It would be much easier and less prone to errors if 2016 or higher were available.

    In the example - the expected return is '02/28/2021, 00:15' - which isn't correct given the offset of -07:00.  I think the return should be '02/28/2021,17:15'.

    You should also clarify how the days are determined.  For example, if today is Monday and the value passed in is Fri - is that the previous Friday or next Friday?

     

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • The date expected is always less than current date. For your queries, here is my reply.

    At Time Zone is not available in 2012

    In the example - the expected return is '02/28/2021, 00:15' - which isn't correct given the offset of -07:00.  I think the return should be '02/28/2021,17:15'. - I should only get DATE ONLY (02/18/2021) and concatenate with time field which is 00:15  my table

    You should also clarify how the days are determined.  For example, if today is Monday and the value passed in is Fri - is that the previous Friday or next Friday? - It should get me previous Friday since the date which I expect is less than current date.

    The table reference is given with expected value

     

     

    • This reply was modified 1 month, 1 week ago by  Rock.
    Attachments:
    You must be logged in to view attached files.
  • This really isn't clear - how is the offset being used?  It doesn't seem to be considered in the calculation - it seems that you want to get the previous 'day' based on the date parameter and the day parameter only.

    What are you using this for?

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • I got your point on omitting the offset field.  Based on the week day, we can populate date and concatenate with time field. Thank you for clearing this out.

    • This reply was modified 1 month, 1 week ago by  Rock.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply