SQL dateAdded

[Jasso]

This was discussed a few years ago (topic: SQL date) but I'm asking whether this has changed.

Is it possible to use SQL to create Smart Groups to include refs entered before, after, or between certain dates?

Looking at how the "Added in last 2 weeks" group works, it seems funny that I can't run other SQL queries based on dateAdded. (At least I couldn't make it work.) If it's not possible at the moment, could it be added in the future?

I know, you can indeed sort your dabase by the date. But as the database grows larger it would be nice to have more ways to handle sub-sections of the library.

At the moment I'm circumventing this by using SQL query to find "Added in the last X weeks", always modifying the amount of weeks. But this might get me in trouble if I forget to change the amount at some point.

[Jon]

You should be able to do any legal SQL search based upon the date added. You have to convert the date to seconds after January 1, 1904 to do this. Off of the top of my head, if you wanted refs after Jan 1, 2014 it would be something like

dateAdded > datediff( '01/01/2014 00:00:00', '01/01/1904 00:00:00', 'second' )


Jon
Sonny Software

[Jasso]

Ok! Then it was just my bad SQL skills.

Let's say I want to find for example all refs added since the beginning of June this year. Or during 2013.

[Jon]

I edited my first reply to add an example.

Jon
Sonny Software

[Jasso]

Awesome, it works! Thank you!

I didn't realise you have to do it with the dateDiff query.

[Jon]

Maybe you don't, this was off the top of my head.

Jon
Sonny Software

[Jasso]

I tried entering different variations of

dateAdded > '01/01/2014 00:00:00'
dateAdded > '01/01/2014'

with or without quotation marks, parentheses, etc. But none of these worked. But I'd definitely like to know if you can just enter a simple query like that.

[Jon]

They don't work because you're entering the human-readable date. It needs to be converted to seconds after 1/1/1904. Datediff does that.

You're dealing with straight SQL now -- this is a power user feature. If you want to take advantage of the things you can do with it you'll need to read up on SQL.

Jon
Sonny Software

[Jasso]

Ok.

Thanks for the clarification and thanks for showing how to do the query. Works perfectly now.