Using date-diff

OK - date-diff shouldn’t be difficult, so please pardon me if this is obvious and I’m missing this.

I want to know the number of days between a value in a table and today. Why doesn’t this work:

SELECT birth_date, Date_Diff(‘Day’,date (birth_date), Current_Date) as mydatediff
from pilot.“animal-dimension”

? Seems simple - replace the ‘birth_date’ with any date in any table. What am I missing?

Thanks…

Hi @iyotah !

Thanks for posting! Are you getting an error or the value you’re seeing is incorrect?

–n

Hi Nadine - I’m getting this:

Error [Query]

No valid overload found for function date with arguments (string)

Hello @iyotah

In your query above the key line is date (birth_date)
DATE vs. DATE() in SQL have two different use cases:

DATE ‘2018-01-01’ – example literal
DATE(2018, 1, 1) – constructor function

In your case changing your query to

SELECT birth_date, Date_Diff(‘Day’,date birth_date, Current_Date) as mydatediff
from pilot.“animal-dimension”

Should work. Let us know if you have any further questions.

Further reading on dates: Date And Time Functions | Rockset Documentation

-jeff

1 Like

Hi Jeff - I tried that exact query, and got this:

Error [Query]

Incorrect SQL syntax near ‘birth_date’. Please inspect your query for correct syntax… Position: line: 1, ch: 41

Hi @iyotah,

I’m going to explore this more - when you select birth_date what is an example of how the field is returned. i.e. is it 01-01-1990?

@iyotah,

I think there is an error copying from the community section here and some extra formatting that got applied to the query.

It looks like the single and double quotes might not be the same format as it should be. Hopefully this will work.
SELECT birth_date, Date_Diff('Day',date birth_date, Current_Date) as mydatediff from pilot."animal-dimension"

Edit: As @nadine mentioned It would be useful to know what data type birth_date is which would give us the best way to know what will work for your query.

it returns ‘2021-08-01’ format…

when I copy your code exactly - I get this:

Error [Query]

Incorrect SQL syntax near ‘birth_date’. Please inspect your query for correct syntax… Position: line: 1, ch: 41

And there are two little red dots under the ‘b’ in birth_date

I don’t know if you can do a phone call, if so please call on 720-810-3628, I bet we could solve this quickly with a screen share…

Hi @iyotah

Does this work if you replace the date with birth_date? Copy and paste my example, and then try it with your field. If it fails, please let me know:

SELECT Date_Diff('Day',cast ('2021-08-01' as date), Current_Date) as days ;

This is it:

Date_Diff(‘Day’,cast (birth_date as date), Current_Date)

Thanks so much!

1 Like

YAY! glad we resolved it :slight_smile: Also thanks to @jeff for suggesting this.