Oracle, Make Date Time's First Day of Its Month

Oracle, Make date time's first day of its month

According to http://psoug.org/reference/date_func.html, this should work a dandy...

SELECT TRUNC(yourDateField, 'MONTH') FROM yourTable

How Oracle SQL to get first day of month

Those conditions are, basically, equal. November has 30 days anyway, so your 1st condition is contained in the 2nd one. No difference at all.

Therefore, you'd just truncate date value to month, e.g.

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> select sysdate,
  2         trunc(sysdate, 'mm') first_of_month
  3  from dual;

SYSDATE             FIRST_OF_MONTH
------------------- -------------------
21.12.2021 09:01:22 01.12.2021 00:00:00

SQL>

Oracle SQL to get first day of month

Building on Tim Biegeleisen's solution, simplifying it and avoid the date-to-text-to-date conversions. Note the use of TRUNC to get the first date of the period.

SELECT
    CASE
        WHEN EXTRACT(MONTH FROM DATE_COL) = 11 AND EXTRACT(DAY FROM DATE_COL) >= 16
            THEN TRUNC(DATE_COL, 'MONTH') + 15
        ELSE TRUNC(DATE_COL, 'MONTH')
    END AS FIRST_OF_MONTH
FROM T1

Get first and last day of month into variables - Oracle

You can use such a query to detect first and last days of the current month :

SELECT TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-1)))+1,
       TRUNC(LAST_DAY(SYSDATE)) 
  INTO :startDate,:endDate 
  FROM DUAL;

with the contribution of ADD_MONTHS() function

Update : Alternatively use this PL/SQL code block :

DECLARE
  startDate date;
  endDate   date;
BEGIN
 startDate := TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-1)))+1;
 endDate   := TRUNC(LAST_DAY(SYSDATE)); 
 DBMS_OUTPUT.PUT_LINE ('startDate :  '||startDate); 
 DBMS_OUTPUT.PUT_LINE ('endDate :  '||endDate);  
END;
/

Demo

get the data starting from 1st month till current date in oracle sql

To get the firts day of current month you can simply turn the first TRUNC(SYSDATE,'IW') to TRUNC(SYSDATE,'MM') and turn -7 to -1 if you want data from preceeding month if run on first day of current month.

Then you want to get data till sysdate, so again turn the second TRUNC(SYSDATE,'IW') in TRUNC(SYSDATE):

SELECT t.day_id 
  FROM F_TIME t 
 WHERE t.day_id >= TO_NUMBER (TO_CHAR( TRUNC ( SYSDATE - 1 , 'MM' ) , 'YYYYMMDD' )) 
   AND t.day_id <= TO_NUMBER ( TO_CHAR ( TRUNC ( SYSDATE ), 'YYYYMMDD' ));

EDIT:

turned < to <= to get today.

in this way, if you run the query on 1st February you get entire January AND 1st February: if you want to exclude 1st February (i.e. today) you have to add a CASE WHEN statement.

how to find start date and end date of all weeks present in a month in oracle

Try this. Add more months in m table, or replace it with your actual table.
I am assuming that MAR-17 is in varchar data type. If not, replace to_date(mon,'MON-YY') with mon in the code everywhere.

SELECT mon,
       TRUNC(to_date(mon,'MON-YY'), 'iw') + nxt AS iso_week_start_date ,
       TRUNC(to_date(mon,'MON-YY'), 'iw') + 6 + nxt AS iso_week_end_date
FROM
  (SELECT LEVEL*7 - 7 AS nxt
   FROM dual CONNECT BY LEVEL <=6)
CROSS JOIN
  (SELECT 'MAR-17' AS mon
   FROM dual
   UNION ALL SELECT 'APR-17'
   FROM dual
   UNION ALL SELECT 'MAY-17'
   FROM dual
   ) m --Sample data. Replace with original table and change column mon to your `char` column.
WHERE --where clause to filter out of month dates.
    to_date(mon,'MON-YY') <= TRUNC(to_date(mon,'MON-YY'), 'iw') + nxt --iso_week_start_date
  AND last_day(to_date(mon,'MON-YY')) >= TRUNC(to_date(mon,'MON-YY'), 'iw') + nxt --iso_week_start_date
ORDER BY 2

Explanation:

• select TRUNC (sysdate, 'IW') from dual
  Returns first day of the week (monday) of the given date.

• (select level*7 - 7 as nxt from dual connect by level <=6) It will
  geneate column in range of 0,7,14,21,28,35 .Later cross join it to multiply each month 6 times to generate 6 different weeks.

• Utilize this while adding days to the fist week.

• Where clause is used to filter out of boundry dates.

get the previous last date of the month in oracle sql

You can use EXTRACT( DAY FROM SYSDATE ) to get the current day of the month. If this is below your threshold then you can get the last day of the previous month otherwise use the last day of the current month:

SELECT *
FROM   C_LOG
WHERE  C_DATE = CASE
                WHEN EXTRACT( DAY FROM SYSDATE ) <= 3
                THEN TRUNC( SYSDATE, 'MM' ) - INTERVAL '1' DAY
                ELSE LAST_DAY( TRUNC( SYSDATE ) )
                END

Note:

Do not use to_date(sysdate,'YYYYMMDD') since TO_DATE( date_string, format_model ) takes a string as its first argument so Oracle will implicitly cast the date to a string; effectively doing:

TO_DATE(
  TO_CHAR(
    SYSDATE,
    ( SELECT value FROM NLS_SESSION_PARAMETERS WHERE parameter = 'NLS_DATE_FORMAT' )
  ),
  'YYYYMMDD'
)

If the NLS_DATE_FORMAT is not YYYYMMDD then the query will fail. Since this is a session parameter, each user can change it at any time and your query will fail for any user who changes that parameter without ever changing sql of the query.

Instead, if you want to remove the time component of a DATE data type, use the TRUNC function.

---

Related Topics