Grab and calculate days between two dates

[InfernoJaffa]

Hi All,

Firstly, thank you for clicking onto my thread.

What I have is two fields, one is a simple date and the other is slightly more complex as it holds a date but in a different structure (a dated case number).

The case numbers are for example, 150211551223 the date being the first 6 numbers and equaling to 11/02/2015. so the code will need to grab this data, spin it around and convert it.

The formula will then be:
simpledate - convertedcasenumberdate = days between.

(simpledate will always be the latest date of the two).

I hope that has made sense and thank you in advance for any support given.

 

[CJ_London]

use the datevalue function

datevalue(left(casenumber,2),mid(casenumber,3,2),mid(casenumber(5,2))

 

[jdraw]

inferno,

First, I agree with CJ's response to your question.

However, if you are developing this database, I highly recommend you do not try to concoct codes. Use proper date data types for dates (as you have done in the Date field). Often, concoctions of various "things" into a code with embedded significance, causes issues for extractions and leads to anomalies that were not evident when the "code" was envisioned.
1 fact 1 field is a simple by proven approach to relational database.

Good luck with your project.

 

[InfernoJaffa]

inferno,

First, I agree with CJ's response to your question.

However, if you are developing this database, I highly recommend you do not try to concoct codes. Use proper date data types for dates (as you have done in the Date field). Often, concoctions of various "things" into a code with embedded significance, causes issues for extractions and leads to anomalies that were not evident when the "code" was envisioned.
1 fact 1 field is a simple by proven approach to relational database.

Good luck with your project.

Hi jdraw,

Thanks for your reply, sadly the date data is what I have to use. With what CJ has said, how can I then work out the difference between the two dates? I have zero to none knowledge.

 

[jdraw]

Cj has shown you how to create a data from the coded value.

See the datediff() function for details and examples

 

[namliam]

isnt cj mixing DateValue and DateSerial functions?

I think he means
dateSERIAL(left(casenumber,2),mid(casenumber,3,2),mid(casenumber(5,2))


You can use the datediff function or simply do
Date1 - Date2

 

[InfernoJaffa]

isnt cj mixing DateValue and DateSerial functions?

I think he means
dateSERIAL(left(casenumber,2),mid(casenumber,3,2),mid(casenumber(5,2))


You can use the datediff function or simply do
Date1 - Date2

Hi namliam,

Thanks for confirming this, it wasn't working for me so i took it out and just used the mid and left grabs with "/" to make the date.
I've managed to get a result.

Thank you to all those who have helped.

 

[CJ_London]

I think he means
dateSERIAL

Whoops! I did.