CariDotMy

 Forgot password?
 Register

ADVERTISEMENT

View: 3320|Reply: 13

Need Help In PHP + SQL Statement

[Copy link]
Post time 9-8-2012 09:21 AM | Show all posts |Read mode
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.

Reply

Use magic Report


ADVERTISEMENT


Post time 13-8-2012 11:57 AM | Show all posts
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
Reply

Use magic Report

Post time 15-8-2012 08:18 AM | Show all posts
varchar by itself tak dikenali sebagai valid date oleh mysql. Gunakan fungsi STR_TO_DATE dulu sebelum buat comparison



Reply

Use magic Report

 Author| Post time 27-8-2012 08:01 AM | Show all posts
nalis posted on 15-8-2012 08:18 AM
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

Reply

Use magic Report

Post time 27-8-2012 08:51 AM | Show all posts
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 datetime
  1. SELECT * FROM tableName
  2. WHERE CAST(tarikh AS DATETIME) BETWEEN $dari AND $hingga
Copy the Code
nak selamat, setkan supaya mssql tau dateformat  data tu camne dulu.. bleh tambah code di bawah sebelum SELECT statement
  1. SET DATEFORMAT ymd
Copy the Code
Reply

Use magic Report

 Author| Post time 10-9-2012 10:40 AM | Show all posts
nalis posted on 27-8-2012 08:51 AM
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


Reply

Use magic Report

Follow Us
Post time 10-9-2012 03:11 PM | Show all posts
isms posted on 10-9-2012 10:40 AM
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
Reply

Use magic Report

 Author| Post time 10-9-2012 03:24 PM | Show all posts
nalis posted on 10-9-2012 03:11 PM
boleh bagi sample data dari TKH_KIRIM tu.. nak tengok rupa string dia

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

Reply

Use magic Report


ADVERTISEMENT


Post time 10-9-2012 03:29 PM | Show all posts
sebelum SELECT TU cuba bubuh
  1. SET DATEFORMAT dmy
Copy the Code
Reply

Use magic Report

Post time 10-9-2012 11:01 PM | Show all posts
isms posted on 10-9-2012 10:40 AM
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, [trkh_kirim], 105) between convert (datetime, '$findate', 105)
and convert (datetime, '$findDateTo', 105)

Reply

Use magic Report

 Author| Post time 10-9-2012 11:25 PM | Show all posts
alam. posted on 10-9-2012 11:01 PM
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?
Reply

Use magic Report

Post time 10-9-2012 11:41 PM | Show all posts
isms posted on 10-9-2012 11:25 PM
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       [set dateformat] ..

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

Reply

Use magic Report

 Author| Post time 11-9-2012 09:12 AM | Show all posts
alam. posted on 10-9-2012 11:41 PM
hehe... adik sayang... nk date saja... convert saja ke date...
*****************************
c ...

bro/sis,
tq so much, dh dpt.

Reply

Use magic Report

 Author| Post time 11-9-2012 09:12 AM | Show all posts
nalis posted on 10-9-2012 03:29 PM
sebelum SELECT TU cuba bubuh

bro/sis,
tq so much, dh dpt.
Reply

Use magic Report

You have to log in before you can reply Login | Register

Points Rules

 

ADVERTISEMENT



 

ADVERTISEMENT


 


ADVERTISEMENT
Follow Us

ADVERTISEMENT


Mobile|Archiver|Mobile*default|About Us|CariDotMy

31-12-2024 06:00 AM GMT+8 , Processed in 0.229652 second(s), 27 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

Copyright © 2001-2021, Tencent Cloud.

Quick Reply To Top Return to the list