This article consistes of some of the frequently asked date related queries.
Hope the users find it useful.
=============== ===========
1.Determining the Date of the First and Last Occurrence of a Specific Weekday in a Month
------------------------------------ - Select next_day(trunc(sysdate,'mm')-1,'SUNDAY') First_Sunday, next_day(last_day(trunc(sysdate,'mm'))-7,'SUNDAY') Last_Sunday from dual
For other days please modify the name of the DAY in the query.
2.Determining the First and Last Day of a Month
------------------------------------------------------------------------ - select trunc(sysdate,'mm') Firstday,
-
last_day(sysdate) Lastday from dual
3.Extracting Units of Time from a Date
------------------------------------------------------------ - Select to_number(to_char(sysdate,'hh24')) hour,
-
to_number(to_char(sysdate,'mi')) min,
-
to_number(to_char(sysdate,'ss')) sec,
-
to_number(to_char(sysdate,'dd')) day,
-
to_number(to_char(sysdate,'mm')) mth,
-
to_number(to_char(sysdate,'yyyy')) year
-
from dual
4.To find out No of days in year.
--------------------------------- - select add_months(trunc(sysdate,'y'),12) - trunc(sysdate,'y') from dual
5.To find out if the year is a leap Year or not.
--------------------------------------------------------------------- - select decode(to_char(last_day(add_months(trunc(sysdate,'y'),1)),'DD'),28 ,'Not a Leap Year','Leap Year') from dual
6.Listing Quarter Start and End Dates for the Year
------------------------------------------------------------------------- - Select rownum qtr,
-
add_months(trunc(sysdate,'y'),(rownum-1)*3) q_start, add_months(trunc(sysdate,'y'),rownum*3)-1 q_end from emp where rownum <= 4
7.Retrivinging three most recent dates from a table
=============== =============== === - select e.hiredate from
-
(
-
select dense_rank() over (order by hiredate desc)n,hiredate
-
from emp )e where e.n<=3
8.Determining the Number of Months or Years Between Two Dates
--------------------------------------------------------------------------------------------------- - select months_between(max_hd,min_hd) MONTHS,
-
months_between(max_hd,min_hd)/12 YEARS
-
from (select min(hiredate) min_hd, max(hiredate) max_hd from emp ) x
9.Determining the Number of Seconds, Minutes, or Hours Between Two Dates
------------------------------------------------------------------------------------------------------------------ - select dy*24 as hr, dy*24*60 as min, dy*24*60*60 as sec from (select (max(case when ename = 'WARD' then hiredate end) -
-
max(case when ename = 'ALLEN' then hiredate end)) as dy from emp) x
10.Determining the Date Difference Between the Current Record and the Next Record
----------------------------------------------------------------------------------------------------------------- - select ename, hiredate, next_hd,
-
next_hd - hiredate diff from (
-
select deptno, ename, hiredate, lead(hiredate)over(order by hiredate) next_hd
-
from emp) ORDER BY HIREDATE
11.Counting the Occurrences of Weekdays in a Year
-------------------------------------------------------------------------------- - with x as ( select level lvl from dual connect by level <= ( add_months(trunc(sysdate,'y'),12)-trunc(sysdate,'y'))
-
) select to_char(trunc(sysdate,'y')+lvl-1,'DAY'), count(*) from x group by to_char(trunc(sysdate,'y')+lvl-1,'DAY')
Also check Important Date Related Queries - 2 0 5744 Sign in to post your reply or Sign up for a free account.
Similar topics |
by: DCM Fan |
last post by:
{CREATE TABLEs and INSERTs follow...}
Gents,
I have a main table that is in ONE-MANY with many other tables. For example, if
the main table is named A, there are these realtionships:
A-->B
A-->C
A-->D
|
by: Thomas R. Hummel |
last post by:
Hello,
I am importing data that lists rates for particular coverages for a
particular period of time. Unfortunately, the data source isn't very
clean. I've come up with some rules that I think will work to clean the
data, but I'm having trouble putting those rules into efficient SQL.
The table that I'm dealing with has just under 9M rows and I may need
to use similar logic on an even larger table, so I'd like something
that can be made...
|
by: BlackFireNova |
last post by:
Using Access 2002
I am writing a report which draws data from several different tables. I
can't link all the tables in a query, as some can not be related without
truncating the data. I plan to use sub-queries and sub-reports to
filter and display the data in the unrelated tables in my report. The
common information is a user-inputed date range.
I want to avoid having the user prompted for the and
variables repeatedly. Somehow I...
|
by: David B |
last post by:
I have a number of queries, running one after the other, which do quite a
complex calculation. A text box on a form provides the date for this routine.
I have another routine I wish to do and it happens that the final query in the
above routine contains the data I need. However I don`t want to fire this
routine from the same form as before.
I could make copies and rename the queries I suppose but is there a way I could
enter the date...
|
by: carl.barrett |
last post by:
Hi,
I'm back with the same question as I still can't get it to display
my data the way I want it to.
The table lists information about a perpetrator involved with an anti
social behaviour order. The table contains personal information about
the individual as well as (here is where the letter dates comes in:)
e.g.
| |
by: Tony Miller |
last post by:
All
I have an aggregate query using the function Month & Year on a
datereceived field ie:
TheYear: Year()
TheMonth: Month()
These are the group by fields to give me a Count on another field by
year & month
When I try to place a date filter 'Between x And y ' on an expression
field
|
by: PC Datasheet |
last post by:
Transaction data is given with date ranges:
Beginning End
4/1/06 4/4/06
4/7/06 4/11/06
4/14/06 4/17/06
4/18/06 4/21/06
426/06 4/30/06
I am looking for suggestions on how to find the date ranges where there were
no transactions.
|
by: mharrison |
last post by:
Hello,
I am developing a small java web-based car-pool booking system app which interacts with an access database. I am trying to write 2 queries: The first which will specify whether a given car is available on a given date range e.g. from: 1/12/05 to 12/12/05. the second which will run if the first query is unsuccessful e.g. a list of other cars available on the chosen dates. I have been looking at a Microsoft page which I believe may help...
|
by: debasisdas |
last post by:
12.Alternate method for determining Quarter Start and End Dates for Quarter(not recommended)
----------------------------------------------------------------------------------------------
select add_months(q_end,-2) q_start,
last_day(q_end) q_end
from (select to_date(substr(yrq,1,4)||mod(yrq,10)*3,'yyyymm') q_end
from (
select 20071 yrq from dual union all
select 20072 yrq from dual union all
select 20073 yrq from dual union all
select...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |