Check whether date is even or odd

Asked

Viewed 1,172 times

3

I am in need of help with dates in SQL Server.

I have a date field and would like to know if this date is odd or even. Because I need to make this comparison to use in another field. I looked for some methods but I did not reach the solution. I thought about using DATEDIFF but I don’t have another date field to compare.

    CASE WHEN DATEPART(DW, CONVERT(DATE,data_saida)) IN (3) AND porto = 409 
THEN CONVERT(VARCHAR(15),DATEADD(DAY, -2, CONVERT(DATE,data_saida))) + ' TO ' + CONVERT(VARCHAR(15),DATEADD(DAY, -1, CONVERT(DATE, data_saida)))

This code I use that this delivery is only on Tuesday. I’m not taking into consideration whether data_output is odd or even. Now came this last-minute demand and I don’t know where to fit properly.

sql date
  • 4

    Can describe what an "even date is"?

  • 30 of any month/year is an even date? the validation would only be on the day of the month??

  • Excuse the lack of information. I wonder if on the full date (DD/MM/YYYY) the DD is even or odd. For example today 14/12/2018 (14 > even). These dates I have are within the current month.

1 answer

3

Use the operator % (mod) whether the date is even or odd.

If the rest of the day split by 2 equals 0 then we have an even number, otherwise it is odd.

Example for SQL Server:

SELECT CASE DAY(data_saida) % 2 WHEN 0
            THEN 'PAR'
       ELSE 'IMPAR'
        END

  • Thanks Pedro. I have a date field already predefined (I’ll leave the code) and I can’t use getdate. The dates already set represent the departure of products from the carriers. However, if the output dates are pair they deliver only from x to y and odd from x to z, for example. I will edit above.

  • I changed the answer to use the data_saida, now just implement the logic in place of 'PAR' and 'IMPAR''

  • Dear Pedro, I am trying to apply the rule in my case but I am not successful. As my case above, I am using this way: WHEN DATEPART(DW, CONVERT(VARCHAR(15),data_saida,103)%2 WHEN 0) IN (3) AND porto = 409 
 THEN CONVERT(VARCHAR(15),data_saida,103)
ELSE CONVERT(VARCHAR(15),data_saida,103) I am using datepart to specify the days of the week that the deliveries will take place. I understand you’ve already shed some light on me, but you’d have some alternative to that, or I’m doing it the wrong way?

  • Dw is playing the role to inform me that the days of the week, for example: on the dates (data_exit) that fall 1,2,3,4 (Monday, Tuesday, Wednesday and Thursday) the collection should be made from x to y days. But now I want to inform that if the data_output (dd/mm/yyyy) in addition to fall in 1,2,3,4 and is odd will be on other days and follows the same for even. This date is monthly, in this case the current month. I don’t know if I got it right, but overall it is: if the date left falls on a Friday (12/14/2018) is even, then the date of collection will have to be Monday to Thursday and if odd follows another rule.

Browser other questions tagged sql date

You are not signed in. Login or sign up in order to post.