Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Next challenges

Recent achievements

  • Global
  • Personal

Recognition

  • Give kudos
  • Received
  • Given

Leaderboard

  • Global

Trophy case

Kudos (beta program)

Kudos logo

You've been invited into the Kudos (beta program) private group. Chat with others in the program, or give feedback to Atlassian.

View group

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Calculate a date 10 days after a specific date

Sofie I'm New Here Sep 28, 2020

Hi,

I have already the date difference between 'Start date' and 'End date' but I also want to calculate the date 10 days after the "End date" in a table.

The first line is OK but when I add the second one the result in column 'End date plus 2 weeks' says Invalid date. It also duplicates the rows. 

SELECT *, DATEDIFF(day,'Start date','End date')+ " days" as 'Duration' from T1 UNION
SELECT *, DATE_ADD(DAY, 10, "End date") AS 'End date plus 2 weeks' from T1

How can I fix this?

 

Thanks

Sofie2020-09-28_9-04-05.jpg

 

1 answer

Hi Sofie,

Please, try this query:

SELECT *, DATEDIFF(day,'End date','Start date')+ " days" as 'Duration', DATE_ADD(DAY, 10, 'End date') AS 'End date plus 2 weeks' from T1
Sofie I'm New Here Sep 28, 2020

Hi,

Thank you for your answer. Almost there ...

The duplicated row disappeared, but now 'Duration' is negative date and 'End date plus 2 weeks' still invalid Date.

I'm a complete beginner so really appreciate your help !

2020-09-28_9-28-07.jpg

I reproduced your usw case and this query works for me:

SELECT *, DATEDIFF(day,'Start date','End date')+ " days" as 'Duration',
FORMATDATE('End date' + "240h") AS 'End date plus 2 weeks' FROM T1

 Also, you can use "10d" instead of "240h", but make sure 1 day is set to 24 hours in the settings (by default it is set to 8 working hours).

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Confluence Cloud

Get to know the Confluence team!

Go “behind the screen” to meet some of the Confluence Cloud team. In this video series, we tackle some of the hard-hitting questions you never knew you wanted the answer to!  Meet some of the ...

236 views 0 10
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you