Tanggal - PostgreSQL

UBAH FORMAT TANGGAL
CONTOH DI DATABASE FORMATNYA YYYY/MM/DD, KITA UBAH JADI DD/MM/YYYY
select a.nomor_register_aplikasi no_rekening, a.nama_nasabah nama_nasabah,
concat(b.jangka_waktu,' ','B') JK, b.plafon,
TO_CHAR(b.tanggal_pencairan,'DD/MM/YYYY') Tgl_Pencairan, b.provisi from adm_permohonan_kredit a,
adm_simulasi_permohonan_kredit b
where a.id=id_kredit
and a.nomor_register_aplikasi='K060000136'

select TO_CHAR(d_entry, 'YYYY-MM-dd') tgl_mulai from mbl_kolektor_setoran_group
where TO_CHAR(d_entry, 'YYYY-MM-dd')=TO_CHAR(now(), 'YYYY-MM-dd')

CONVERT NOW()
select * from adm_kredit_angsuran where batasan_permohonan_kredit=91898  AND tanggal_jatuh_tempo <= (CAST(NOW() AS DATE))

MENAMBAHKAN TANGGAL
(select a.tanggal,b.id, a.kolektibilitas, a.par, a.baki, a.tgk_pokok, a.tgk_bunga, a.tot_hari  from rpt_history_kol_n_ppap_kredit a
join adm_permohonan_kredit b on a.kredit_id=b.id
where a.tanggal = CAST('2019-06-16' as date) + INTERVAL '1 day'
and b.id_bpr=7 and b.id_bpr_cabang=18)

AMBIL TANGGAL AWAL DAN AKHIR BULAN
select
( ( date_trunc( 'MONTH', CAST ( concat ( '2019-', '09-', '01' ) AS DATE ) ) ) :: DATE ) xx,
((date_trunc( 'MONTH', CAST ( concat ( '2019-', '09-', '01' ) AS DATE ) ) + INTERVAL '1 MONTH - 1 day') :: DATE ) yyy

AMBIL TAHUN, BULAN DAN TANGGAL AJA
select (date_part('year', tanggal_beli)) tanggal from m_aset_inventaris where id in (2659, 2658)
select date_part('month', tanggal_beli), date_part('month', now()) from m_aset_inventaris
select (date_part('day', tanggal_beli)) tanggal from m_aset_inventaris where id in (2659, 2658)

CEK UMUR
select age(timestamp '1995-02-11')

JIKA UPDATE TANGGAL
set jwl_depresiasi=cast(CONCAT(DATE_PART('YEAR',jwl_depresiasi),'-',DATE_PART('MONTH',jwl_depresiasi),'-',tanggal) as date)

MENDAPATKAN JUMLAH HARI DARI SUATU BULAN
SELECT extract(days FROM date_trunc('month', now()) + interval '1 month - 1 day');

SELECT DATE_PART('days', DATE_TRUNC('month', NOW()) + '1 MONTH'::INTERVAL - '1 DAY'::INTERVAL)

GABUNGAN
select
jwl_depresiasi,
DATE_PART('days', DATE_TRUNC('month', jwl_depresiasi) + '1 MONTH'::INTERVAL - '1 DAY'::INTERVAL) jumlah_hari,
DATE_PART('YEAR',jwl_depresiasi) tahun,
DATE_PART('MONTH',jwl_depresiasi) bulan,
CONCAT(DATE_PART('YEAR',jwl_depresiasi),'-',DATE_PART('MONTH',jwl_depresiasi),'-',31) gabungan
from m_kartu_aset_inventaris
where id_aset_inventaris = 2658 order by depresiasi_bulan_ke

FORMAT
--tanggal_jurnal >= '2019-06-01'
--AND tanggal_jurnal < CAST ( '2019-06-30' AS DATE ) + INTERVAL '1 day'
CAST ( tanggal_jurnal AS DATE ) >= ( ( date_trunc( 'MONTH', CAST ( concat ( '2019-', '06-', '01' ) AS DATE ) ) ) :: DATE )
                AND CAST ( tanggal_jurnal AS DATE ) <= ((date_trunc( 'MONTH', CAST ( concat ( '2019-', '06-', date_part( 'days', now()) ) AS DATE ) ) + INTERVAL '1 MONTH - 1 day') :: DATE )

BETWEEN
SELECT
apd.id,
apd.nomor_permohonan no_register,
apd.tanggal_permohonan,
apd.nomor_register no_rek,
apd.nama_nasabah,
mkpd.kode_deposito kode,
apd.nominal saldo,
au.sso_id ao
FROM adm_permohonan_deposito apd
join m_kriteria_produk_deposito mkpd on apd.id_produk_deposito=mkpd.id_kriteria_produk_deposito
join app_user au on apd.account_officer=au.id
where apd.id_bpr=3 and apd.id_bpr_cabang=3
and apd.tanggal_permohonan between cast('2019-07-01' as date) and cast('2019-07-31' as date)