Date+time

Discuss the database features
Post Reply
dentopolis
Posts: 19
Joined: Mon Sep 23, 2013 2:44 pm

Date+time

Post by dentopolis »

how to limit query to date range?I've tried NOW(), CUDATE(), CURRENT_TIMESTAMP(). nothing works. data in columns has values in format:2020-05-25 18:00:00
LibreOffice 7.4
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: date+time

Post by Sliderule »

Per your few words description of what you want . . . I will have to make some assumptions ( bad that I have to make assumptions about your environment ):
  1. Assuming you want to 'hard-code' a date range for a result set:

    Code: Select all

    Select 
       "MY_TABLE".*  
    From "MY_TABLE" 
    Where CAST("MY_TIMESTAMP_COLUMN" as DATE) Between '2020-05-13' and '2020-050-21'
    
  2. Assuming you want to 'to be prompted' for a date range for a result set:

    Code: Select all

    Select 
       "MY_TABLE".*  
    From "MY_TABLE" 
    Where CAST("MY_TIMESTAMP_COLUMN" as DATE) Between :Enter_From_Date and :Enter_To_Date
    
  3. Assuming you want to 'return a date for current date' the Query is executed':

    Code: Select all

    Select 
       "MY_TABLE".*  
    From "MY_TABLE" 
    Where CAST("MY_TIMESTAMP_COLUMN" as DATE) = CURRENT_DATE
    
Explanation:
  1. The above SQL Query assumes your database back-end is HSQL. If not, change it to use the syntax of your database back-end. You have NOT told us which database back-end you are using so I had to make assumptions.
  2. Change the TABLE NAME and COLUMN NAMES to match your need.
I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: date+time

Post by FJCC »

This works for me. I used two formats to specify the DateTime just to show either would work.

Code: Select all

ELECT * FROM "Table1"
WHERE
"DateTime" > '2020-01-15 04:25:18' AND
"DateTime" < '2020-02-15'
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Post Reply