Forum › Internet Service & Networking › Need Help In PHP + SQL Statement

isms Publish time 9-8-2012 09:21 AM

Need Help In PHP + SQL Statement

Salam.

sapa2 leh bantu sy tok mebetulkan coding sy.

saya ada nak buat carian data dari dua tarikh. cth carian dari tarikhA to tarikh B.
sy menggunkana coding mcm ne


1select * from

2tableName


3where datediff(day,'2011-06-27 12:28:34.480',theDate)>=0 and


4datediff(day,'2011-06-28 12:28:34.480',theDate)<=0


tp masalahnya date saya pulak format dia adalah varchar? Kalau tarikh saya tuh saya x nak hardcode tarikh tp tukar kepada nama field boleh ker saya buat cam ne


1select * from

2tableName


3where datediff(day,'$Dari',Dari)>=0 and


4datediff(day,'$hingga',hingga)<=0

sapa2 boleh tolong.

kmkd Publish time 13-8-2012 11:57 AM

datediff hanya boleh guna untuk type date, datetime dan timestamp kalau tak salah/
kalau boleh date sila simpan dalam date. kalau tak memang payah nak buat compare

so kalau dalam kes ni cadangkan guna between

nalis Publish time 15-8-2012 08:18 AM

varchar by itself tak dikenali sebagai valid date oleh mysql. Gunakan fungsi STR_TO_DATE dulu sebelum buat comparison

isms Publish time 27-8-2012 08:01 AM

nalis posted on 15-8-2012 08:18 AM static/image/common/back.gif
varchar by itself tak dikenali sebagai valid date oleh mysql. Gunakan fungsi STR_TO_DATE dulu sebelu ...

mcm mana tuh? x bp nak fhm
btw sy pakai ms sql server, bukan mysql, sintak dia masih sm ker
sorry ler
br nak merangkak2 buat programiing

nalis Publish time 27-8-2012 08:51 AM

kalau mssql boleh guna fungsi CAST atau CONVERT

contoh: katakanlah field yg simpan date tu nama dia tarikh dan variable $dari/$hingga tu mmg dalam datetimeSELECT * FROM tableName
WHERE CAST(tarikh AS DATETIME) BETWEEN $dari AND $hingga nak selamat, setkan supaya mssql tau dateformatdata tu camne dulu.. bleh tambah code di bawah sebelum SELECT statementSET DATEFORMAT ymd

isms Publish time 10-9-2012 10:40 AM

nalis posted on 27-8-2012 08:51 AM static/image/common/back.gif
kalau mssql boleh guna fungsi CAST atau CONVERT

contoh: katakanlah field yg simpan date tu nama d ...

sis/bro,sy dh buat tp kuar error gni "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value'"

$sql = "SELECT * FROM CITY WHERE CAST(TKH_KIRIM AS DATETIME) BETWEEN '$findDate' AND '$findDateTo'"

nak betuikan kt mn ek

nalis Publish time 10-9-2012 03:11 PM

isms posted on 10-9-2012 10:40 AM static/image/common/back.gif
sis/bro,sy dh buat tp kuar error gni "The conversion of a char data type to a datetime data type r ...

boleh bagi sample data dari TKH_KIRIM tu.. nak tengok rupa string dia

isms Publish time 10-9-2012 03:24 PM

nalis posted on 10-9-2012 03:11 PM static/image/common/back.gif
boleh bagi sample data dari TKH_KIRIM tu.. nak tengok rupa string dia

ne sample data:14/02/2012
data type :char
length :10

nalis Publish time 10-9-2012 03:29 PM

sebelum SELECT TU cuba bubuhSET DATEFORMAT dmy

alam. Publish time 10-9-2012 11:01 PM

isms posted on 10-9-2012 10:40 AM static/image/common/back.gif
sis/bro,sy dh buat tp kuar error gni "The conversion of a char data type to a datetime data type r ...

jgn lupa utk cast datetime juga pada parameter $finddate and $findDateTo..
*************
macam post atas tuh.. nalis ada bgtahu 2 cara : cast dgn convert..
***************
utk "convert" bleh try mcm nih sebagai info tambahan...
****************
select * from city where convert (datetime, , 105) between convert (datetime, '$findate', 105)
and convert (datetime, '$findDateTo', 105)

isms Publish time 10-9-2012 11:25 PM

alam. posted on 10-9-2012 11:01 PM static/image/common/back.gif
jgn lupa utk cast datetime juga pada parameter $finddate and $findDateTo..
*************
macam p ...

Datetime tuh dia akn bc time sekali kn? Klu kita nk date shj mcm mn?

alam. Publish time 10-9-2012 11:41 PM

isms posted on 10-9-2012 11:25 PM http://mforum.cari.com.my/static/image/common/back.gif
Datetime tuh dia akn bc time sekali kn? Klu kita nk date shj mcm mn?

hehe... adik sayang... nk date saja... convert saja ke date...
*****************************
convert(date, '$fromdate', 105)...
**************************
tp jgn risau guna saja convert(datetime).. kalo parameter yg di hantar ada lh '31/8/2012'..
dia akan insert'2012-08-31 00:00:00.000'... (by default)
*****************************
tp katakan kalo byk nk run query... better guna cara nalis tuh..
awal2 lg       ..

Last edited by alam. on 10-9-2012 11:44 PM

isms Publish time 11-9-2012 09:12 AM

alam. posted on 10-9-2012 11:41 PM static/image/common/back.gif
hehe... adik sayang... nk date saja... convert saja ke date...
*****************************
c ...

bro/sis,
tq so much, dh dpt.

isms Publish time 11-9-2012 09:12 AM

nalis posted on 10-9-2012 03:29 PM static/image/common/back.gif
sebelum SELECT TU cuba bubuh

bro/sis,
tq so much, dh dpt.

View full version: Need Help In PHP + SQL Statement