473,320 Members | 2,003 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

UDB date query

I'd like to write a query which will get all results from last month
from a table.

It should look something like this:
Select [fields] from [table] where [timestamp field] >= '[first day of
last month at midnight]'

I cannot hardcode the date or programatically generate it. The same
exact query must be used every month.

Is this possible? How can I do this?

Jan 27 '06 #1
3 5147
figured it out..if anyone is interested

SELECT [FIELDS] from [TABLE] where [TIMESTAMP FIELD] >= (SELECT current
timestamp - HOUR (current timestamp) HOURS
- MINUTE (current timestamp) MINUTES
- SECOND (current timestamp) SECONDS
- MICROSECOND (current timestamp) MICROSECONDS
- 1 MONTH
FROM sysibm.sysdummy1)

Jan 27 '06 #2
Select [fields]
from [table]
where [timestamp field] >=
CURRENT_TIMESTAMP - 1 MONTH
- (DAY(CURRENT_DATE - 1 MONTH) - 1) DAYS
- MIDNIGHT_SECONDS(CURRENT_TIME) SECONDS
- MICROSECOND(CURRENT_TIMESTAMP) MICROSECOND

Jan 28 '06 #3
WHERE DATE([timestamp field]) BETWEEN
CURRENT DATE - (DAY(CURRENT DATE) - 1) DAYS - 1 MONTH
AND
CURRENT DATE - DAY(CURRENT DATE) DAYS
B.

Jan 30 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: carverk | last post by:
Hello All I'm in the middle of moving a MS Access DB to a MySql backend. I have figured out about 90% of the problems I have faced, execpt for this one. I have 3 Queries, which pull records...
4
by: Russell | last post by:
I'm having a fit with a query for a range of dates. The dates are being returned from a view. The table/field that they are being selected from stores them as varchar and that same field also...
3
by: Josh | last post by:
I need a query that will select the closest date. I have to tables Pricing and InventoryItem. tblInventoryItem InventoryItemID <- Pk Description tblPricing
3
by: Jim | last post by:
I'm having a problem with a date query..im trying to pull customer data based on a date specified from a form to 3 months prior to the date specified. So lets say in the form I specified 1/2/2004....
5
by: Alicia | last post by:
Yes, but will that skip a week and group by the date for me? I basically wanted something that would do a count of the dates, then group them by their week name.. BEFORE: Resource Date ...
6
by: kevin carter | last post by:
hi i have a table conataining several fields one of which is date i want to be able to search the table on the date field using code. the code below generates the query from a form, however i get...
3
by: Robert | last post by:
I need to set up a query that will pick out records for the current winter season. I.e., each season runs from October 1 until March 31. The catch is, the year can't be hard coded. So, if the...
2
by: jennwilson | last post by:
I am trying to generate a report based on a query that will list any records where an individual has a date listed that matches the specified time for one or both of the date fields. The two fields...
2
by: scott | last post by:
Hi Everyone, I have a table which has many fields in it but I need to pull some specific info via a query and I don't know if it's possible. I want to run a query which includes two tables....
9
by: Millie18 | last post by:
Hi, I’m trying to set up a query to find all dates on or between a start and end date. Table name and field names I’ve used: Tbl_bookings Booking No Boarding Arrival Date Boarding Departure...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.