CODEWITHSUNDEEP
mysqlmysql-error-1064
rachmaddt
rachmaddt

Reputation: 21

Error Code: 1582 Incorrect parameter count in the call to native function 'STR_TO_DATE'

i hava a query on mysql, and i like to get the date value. here's the sql

SELECT DISTINCT pegawai.NIP_BARU, pegawai.NAMA_PEGAWAI, pegawai.KODE_PANGKAT_TERAKHIR,pangkat_golongan.NAMA_PANGKAT, pangkat_golongan.GOLONGAN, pegawai.MASA_KERJA_THN_AKHIR, gaji.MASA_KERJA_GOLONGAN, gaji.NOMINAL_GAJI,CASE PEGAWAI.TMT_GOL
WHEN (pangkat_golongan.JENIS_GOLONGAN = '2') AND pegawai.MASA_KERJA_THN_AKHIR MOD 2 = 0 AND 12 - pegawai.MASA_KERJA_BLN_AKHIR + MONTH(pegawai.TMT_GOL)> 12 THEN 2 + YEAR(pegawai.TMT_GOL)
WHEN (pangkat_golongan.JENIS_GOLONGAN = '1') AND pegawai.MASA_KERJA_THN_AKHIR MOD 2 = 1 AND 12 - pegawai.MASA_KERJA_BLN_AKHIR + MONTH(pegawai.TMT_GOL)> 12 THEN 2 + YEAR(pegawai.TMT_GOL)
WHEN (pangkat_golongan.JENIS_GOLONGAN = '1') AND pegawai.MASA_KERJA_THN_AKHIR MOD 2 = 0 AND 12 - pegawai.MASA_KERJA_BLN_AKHIR + MONTH(pegawai.TMT_GOL)< 12 THEN YEAR(pegawai.TMT_GOL)
WHEN (pangkat_golongan.JENIS_GOLONGAN = '2') AND pegawai.MASA_KERJA_THN_AKHIR MOD 2 = 1 AND 12 - pegawai.MASA_KERJA_BLN_AKHIR + MONTH(pegawai.TMT_GOL)< 12 THEN YEAR(pegawai.TMT_GOL)
ELSE 1 + YEAR(pegawai.TMT_GOL) END AS TAHUN_HITUNG,CASE PEGAWAI.MASA_KERJA_BLN_AKHIR
WHEN (12 -(pegawai.MASA_KERJA_BLN_AKHIR) + MONTH(pegawai.TMT_GOL) > 12)
THEN (12 - (pegawai.MASA_KERJA_BLN_AKHIR) + MONTH(pegawai.TMT_GOL) - 12)
ELSE (12 - (pegawai.MASA_KERJA_BLN_AKHIR) + MONTH(pegawai.TMT_GOL)) END AS BULAN_HITUNG, STR_TO_DATE(TAHUN_HITUNG, BULAN_HITUNG, '01','%Y-%m-%d') AS TMT_HITUNG FROM pegawai, gaji, pangkat_golongan WHERE gaji.KODE_GOLONGAN = pegawai.KODE_PANGKAT_TERAKHIR AND gaji.MASA_KERJA_GOLONGAN = pegawai.MASA_KERJA_THN_AKHIR AND pangkat_golongan.KODE_PANGKAT = pegawai.KODE_PANGKAT_TERAKHIR;

and there's always error on this line

STR_TO_DATE(TAHUN_HITUNG, BULAN_HITUNG, '01','%Y-%m-%d') AS TMT_HITUNG

Error Code: 1582 Incorrect parameter count in the call to native function 'STR_TO_DATE'

Upvotes: 2

Views: 23467

Answers (2)

Roman Che
Roman Che

Reputation: 137

You pass 4 params here:

STR_TO_DATE(TAHUN_HITUNG, BULAN_HITUNG, '01','%Y-%m-%d')

mysql doc here

Upvotes: 0

CLAbeel
CLAbeel

Reputation: 1088

As the MySQL documentation states, the STR_TO_DATE function takes only two parameters. MySQL STR_TO_DATE The first parameter should be the string that you want to convert, and the second is the date format. It looks like you have the second and not the first.

Do you mean to concatenate the year, month, and date? Try this: STR_TO_DATE(concat(TAHUN_HITUNG,'-', BULAN_HITUNG,'-', '01'),'%Y-%m-%d') AS TMT_HITUNG

UPDATE

I made your main query a subquery, and then applied the str_to_date function on the results of the subquery, selecting all of the other columns. You could also add your tahun_hitung and bulan_hitung to the first select list. You can't use aliases for your calculations as column names within the same select statement. That's why you were getting the unknown column error.

select NIP_BARU, NAMA_PEGAWAI, KODE_PANGKAT_TERAKHIR, NAMA_PANGKAT, 
GOLONGAN, MASA_KERJA_THN_AKHIR, MASA_KERJA_GOLONGAN, NOMINAL_GAJI,
STR_TO_DATE(concat(TAHUN_HITUNG,'-', BULAN_HITUNG,'-', '01'),'%Y-%m-%d') AS TMT_HITUNG 
from (
        SELECT DISTINCT pegawai.NIP_BARU, pegawai.NAMA_PEGAWAI, pegawai.KODE_PANGKAT_TERAKHIR,pangkat_golongan.NAMA_PANGKAT, pangkat_golongan.GOLONGAN, pegawai.MASA_KERJA_THN_AKHIR, gaji.MASA_KERJA_GOLONGAN, gaji.NOMINAL_GAJI,CASE PEGAWAI.TMT_GOL
        WHEN (pangkat_golongan.JENIS_GOLONGAN = '2') AND pegawai.MASA_KERJA_THN_AKHIR MOD 2 = 0 AND 12 - pegawai.MASA_KERJA_BLN_AKHIR + MONTH(pegawai.TMT_GOL)> 12 THEN 2 + YEAR(pegawai.TMT_GOL)
        WHEN (pangkat_golongan.JENIS_GOLONGAN = '1') AND pegawai.MASA_KERJA_THN_AKHIR MOD 2 = 1 AND 12 - pegawai.MASA_KERJA_BLN_AKHIR + MONTH(pegawai.TMT_GOL)> 12 THEN 2 + YEAR(pegawai.TMT_GOL)
        WHEN (pangkat_golongan.JENIS_GOLONGAN = '1') AND pegawai.MASA_KERJA_THN_AKHIR MOD 2 = 0 AND 12 - pegawai.MASA_KERJA_BLN_AKHIR + MONTH(pegawai.TMT_GOL)< 12 THEN YEAR(pegawai.TMT_GOL)
        WHEN (pangkat_golongan.JENIS_GOLONGAN = '2') AND pegawai.MASA_KERJA_THN_AKHIR MOD 2 = 1 AND 12 - pegawai.MASA_KERJA_BLN_AKHIR + MONTH(pegawai.TMT_GOL)< 12 THEN YEAR(pegawai.TMT_GOL)
        ELSE 1 + YEAR(pegawai.TMT_GOL) END AS TAHUN_HITUNG,CASE PEGAWAI.MASA_KERJA_BLN_AKHIR
        WHEN (12 -(pegawai.MASA_KERJA_BLN_AKHIR) + MONTH(pegawai.TMT_GOL) > 12)
        THEN (12 - (pegawai.MASA_KERJA_BLN_AKHIR) + MONTH(pegawai.TMT_GOL) - 12)
        ELSE (12 - (pegawai.MASA_KERJA_BLN_AKHIR) + MONTH(pegawai.TMT_GOL)) END AS BULAN_HITUNG 
        FROM pegawai, gaji, pangkat_golongan 
        WHERE gaji.KODE_GOLONGAN = pegawai.KODE_PANGKAT_TERAKHIR AND gaji.MASA_KERJA_GOLONGAN = pegawai.MASA_KERJA_THN_AKHIR AND pangkat_golongan.KODE_PANGKAT = pegawai.KODE_PANGKAT_TERAKHIR
) subquery;

Upvotes: 3

Related Questions