Author Topic: Search db for pictures with specific date  (Read 3342 times)

0 Members and 1 Guest are viewing this topic.

kdaube

  • Member
  • **
  • Posts: 11
    • View Profile
Search db for pictures with specific date
« on: 2014-11-18 05:33:10 »
Dear all,

I try to find a picture in my database with this SQL statement (set up in Edit > Find > Advanced):
 tn.[file_time_iso] >= 2014-11-05

The result in Found Files is the complete database (all pictues)! Even with = rather than >= I get this whole list.
Adding a time
 tn.[file_time_iso] >= 2014-11-05 00:00:00.000
yields in a Databse error: Syntax error (missing operator) in query expression.

How must the query look like to get the pictures with the file_time_iso starting with november 5 of this year (2014-11-05) ?

Daan van Rooijen

  • Administrator
  • Sr. Member
  • *****
  • Posts: 933
    • View Profile
Search db for pictures with specific date
« Reply #1 on: 2014-11-20 20:09:09 »
I don't speak SQL, but file_time_iso looks to be a 'widestring' field. I suspect that this is a regular text string that cannot be used directly in calculations or numeric comparisons.

It follows the ISO8601 format: yyyy-mm-ddTHH:MM:SS.SSS (where the capital T separates the date from the time).

You'd probably have to use a type conversion in your query, so that the file_time_iso can be compared to a date of your choice, but I really have no idea what command and syntax you'd have to use.. Sorry!
I'm volunteering as a moderator - I do not work for Cerious Software, Inc.

kdaube

  • Member
  • **
  • Posts: 11
    • View Profile
Search db for pictures with specific date
« Reply #2 on: 2014-11-21 05:46:24 »
Problem solved!
At [link=http://stackoverflow.com/questions/5611570/how-i-can-retrieve-rows-by-specific-date-in-sql-server]required syntax[/link] I have found the required syntax:

 tn.[file_time_iso] between '2014-11-05 00:00:00' And '2014-11-08 23:59:59'

has to be entered into the SQL statement area in the Find dialogue.

Daan van Rooijen

  • Administrator
  • Sr. Member
  • *****
  • Posts: 933
    • View Profile
Search db for pictures with specific date
« Reply #3 on: 2014-11-21 17:52:29 »
Excellent!! So the only problem was those missing apostrophes (') :-)

a query like tn.[file_time_iso] >= '2014-11-20' works fine too!

I guess the file_time_iso field is a date/time field after all. That makes things a whole lot easier.
I'm volunteering as a moderator - I do not work for Cerious Software, Inc.

Christian

  • Member
  • **
  • Posts: 9
    • View Profile
Search db for pictures with specific date
« Reply #4 on: 2014-12-06 12:00:42 »
tn.[file_time_iso] is a text field in the standard iso format '[yyyy-mm-tt]' & 'T' & '[hh-mm-ss]'. A search compares character for character from left to right. So a search like between '2*' and '2000-01-13*' will find the files in the first 2 weeks of 2000. In T9 yon can compare direct, in T7 you had to use the function ^udate([yyyy-mm-tt]. The change in T9 makes the search easier IMHO.