Nak Replace Value 'null' Kepada '-' (dash)

[Nasrul Hakim Abdul Rahman 0]

Salam semua..

Minta sifu ajarkan sedikit ilmu..

Saya dah cuba google,tetap tak jumpa cara nak selesai masalah nih..

Info:

Nama Table: Tempah

Nama Field & Data type
Nama - [color=#ff0000]varchar (10)[/color]
Tarikh_tempah -[color=#ff0000] datetime[/color]


[b]select * from Tempah[/b]
[u]Result:[/u]
[u]Nama[/u] [u]Tarikh_tempah[/u]
Ali [color=#ff0000] Null[/color]
Abu [color=#ff0000] 2011-01-01 00:00:00.000[/color]

so, skrg saya nak tukar yang value 'Null' kepada '-' mcm mana?
harap sifu dapat ajar kan..
saya ada cuba guna isnull(convert(varchar,Tarikh_tempah),'-')

[b]select Nama, isnull(convert(varchar,Tarikh_tempah),'-') As Tarikh_tempah from Tempah[/b]
[u]Result:[/u]
[u]Nama[/u] [u]Tarikh_tempah[/u]
Ali [color=#ff0000]-[/color]
Abu [color=#ff0000] Jan 1 2015 12:00AM[/color]

sekali dia tukar yang bukan value 'Null',saya nak tukar yang value Null sahaja.
boleh tak sifu2 tolong...dah penat google..huhuhu

kalau saya tak convert dia bagitau error nih..
'The conversion of a char data type to a [b]datetime data type[/b] resulted in an out-of-range datetime value.' Edited November 24, 2011 by Nasrul Hakim Abdul Rahman

[akuccputsedut 147]

Cuba ini... mungkin dapat membantu

[CODE]SELECT COALESCE(VARCHAR(Tarikh_Tempah), '-') FROM Tempah[/CODE]

[Nasrul Hakim Abdul Rahman 0]

[quote name='akuccputsedut' timestamp='1322039001' post='1075519']
Cuba ini... mungkin dapat membantu

[CODE]SELECT COALESCE(VARCHAR(Tarikh_Tempah), '-') FROM Tempah[/CODE]
[/quote]

dia kuar error msg nih.. ''VARCHAR' is not a recognized built-in function name."

[akuccputsedut 147]

[quote name='Nasrul Hakim Abdul Rahman' timestamp='1322039820' post='1075520']
dia kuar error msg nih.. ''VARCHAR' is not a recognized built-in function name."
[/quote]

this is syntax error... bukan masalah program..

ko nye Tarikh_Tempah guna type apa..??? change it accordingly Edited November 23, 2011 by akuccputsedut

[Nasrul Hakim Abdul Rahman 0]

[quote name='akuccputsedut' timestamp='1322040734' post='1075523']
this is syntax error... bukan masalah program..

ko nye Tarikh_Tempah guna type apa..??? change it accordingly
[/quote]

dah tulis kat atas..guna [u]Data type[/u] datetime..
kalau tukar ke VARCHAR dia akan papar data yang sedia ada [color=#ff0000]2011-01-01 00:00:00.000[/color] kepada [color=#ff0000]Jan 1 2015 12:00AM.[/color]
so Data type saya nak kekal kan datetime. Just nak tukar yang Null kepada '-'.

[akuccputsedut 147]

[quote name='Nasrul Hakim Abdul Rahman' timestamp='1322041558' post='1075526']
dah tulis kat atas..guna [u]Data type[/u] datetime..
kalau tukar ke VARCHAR dia akan papar data yang sedia ada [color=#ff0000]2011-01-01 00:00:00.000[/color] kepada [color=#ff0000]Jan 1 2015 12:00AM.[/color]
so Data type saya nak kekal kan datetime. Just nak tukar yang Null kepada '-'.
[/quote]

sayang ku.. this is your code.. please change to what ever you want.. I just give some example for you to refer to.... don't copy bulat2... try mixed and match..

[EncikMalas 2]

email aku or pm aku jika nk belajar mysql n php
aku ade offer kelas private

[Nasrul Hakim Abdul Rahman 0]

ada sesaper lagi tak yg boleh tolong?
saya dah cuba..tapi tetap tak berjaya..

[ahaksz! 44]

apa kata ko convert Null tu dekat client side. boleh tak?

[Nasrul Hakim Abdul Rahman 0]

[quote name='ahaksz28' timestamp='1322104970' post='1075546']
apa kata ko convert Null tu dekat client side. boleh tak?
[/quote]
Maksud ko buat dalam coding? tak faham..

[ahaksz! 44]

[CODE]
SELECT ISNULL(cast(Tarikh_Tempah as varchar(10)), '-') FROM Tempah
[/CODE]

Try la ni even though aku rasa syntax akuccputsedut tu patot dah boleh.

Pakai PHP ka?

[Nasrul Hakim Abdul Rahman 0]

[quote name='ahaksz28' timestamp='1322105985' post='1075548']
[CODE]
SELECT ISNULL(cast(Tarikh_Tempah as varchar(10)), '-') FROM Tempah
[/CODE]

Try la ni even though aku rasa syntax akuccputsedut tu patot dah boleh.

Pakai PHP ka?
[/quote]

mcm mana erk nak terang..
bro cuba create table baru...

[size=2][u]Step 1:[/u][/size]
[size=2]USE [DBTEST]
GO
/****** Object: Table [dbo].[test] Script Date: 11/24/2011 12:45:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[test](
[Nama] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Tarikh] [datetime] NULL
) ON [PRIMARY][/size]

[size=2][u]Step 2:[/u][/size]
[size=2]INSERT INTO test (Nama,Tarikh)
VALUES ('ABU','2015-02-28 00:00:00.000')

INSERT INTO test (Nama,Tarikh)
VALUES ('ALI',Null)

INSERT INTO test (Nama,Tarikh)
VALUES ('Amin','2013-12-12 00:00:00.000')[/size]

[u][size=2]step 3:[/size][/u]
[size=2]Select * from TEST[/size]

[u][b][size=2]Result:[/size][/b][/u]
ABU [color=#ff0000]2015-02-28 00:00:00.000[/color]
ALI [color=#ff0000]NULL[/color]
Amin [color=#ff0000]2013-12-12 00:00:00.000[/color]

so kat sini la aku cuba nak tukar value Null kepada '[color=#ff0000]-[/color]'...

bila aku guna cara [b]akuccputsedut[/b];
[i]SELECT COALESCE(VARCHAR(Tarikh), '-') FROM test[/i]
Result:
[size=2]'VARCHAR' is not a recognized built-in function name.[/size]

Then aku guna cara ko bagi:
SELECT Nama,ISNULL(cast(Tarikh as varchar(10)), '-') as Tarikh FROM test
Result:
ABU [color=#ff0000] Feb 28 201[/color]
ALI [color=#ff0000]-[/color]
Amin [color=#ff0000]Dec 12 201[/color]

Result dah cantik,Value Null dah tukar ke '[color=#ff0000]-[/color]'..
tapi format tarikh dia ubah sekali..

aku nak dapat kan result mcm nih:
[u][b][size=2]Result:[/size][/b][/u]
ABU [color=#ff0000]2015-02-28 00:00:00.000[/color]
ALI [color=#ff0000]-[/color]
Amin [color=#ff0000]2013-12-12 00:00:00.000[/color]

aku guna asp, kalau aku nak tukar kat application, banyak page terlibat.. so aku nak edit kat storeproc jer..
sebab tu aku nak dapat idea camner nak ubah value [i]null[/i] datatype [i]datetime[/i] ke '-'.

thanks sudi membantu.. Edited November 24, 2011 by Nasrul Hakim Abdul Rahman

[apis_ff 5]

Rasanya en Nasrul lupa nak beritahu database apa yang digunakan MySQL? MSSQL?

Kalau MySQL boleh cuba try yang ni

[CODE]
SELECT IFNULL(cast(Tarikh as datetime),'-') as Tarikh FROM test
[/CODE] Edited November 24, 2011 by apis_ff

[Nasrul Hakim Abdul Rahman 0]

[quote name='apis_ff' timestamp='1322119760' post='1075562']
Rasanya en Nasrul lupa nak beritahu database apa yang digunakan MySQL? MSSQL?

Kalau MySQL boleh cuba try yang ni

[CODE]
SELECT IFNULL(cast(Tarikh as datetime),'-') as Tarikh FROM test
[/CODE]
[/quote]
ya ker? hehehe..semalam dah tulis, hari ni edit dah buang..sori..
saya guna Microsoft SQL Server 2005.

[ahaksz! 44]

[size=3][size=3][CODE] SELECT ISNULL(CONVERT(VARCHAR(10),Tarikh_Tempah,101), '-') FROM Tempah [/CODE][/size][/size]

Boleh try code atas tu. Tapi aku tak sure jadi ke tak pasal takde tempat nak test.
Aku refer link ni [url="http://linesofcode.net/snippets/45"]linesofcode.net[/url]

[Nasrul Hakim Abdul Rahman 0]

[quote name='ahaksz28' timestamp='1322123181' post='1075564']
[size=3][size=3][CODE] SELECT ISNULL(CONVERT(VARCHAR(10),Tarikh_Tempah,101), '-') FROM Tempah [/CODE][/size][/size]

Boleh try code atas tu. Tapi aku tak sure jadi ke tak pasal takde tempat nak test.
Aku refer link ni [url="http://linesofcode.net/snippets/45"]linesofcode.net[/url]
[/quote]
Thanks Bro... akhirnya...
[i]SELECT ISNULL(CONVERT(VARCHAR(10),Tarikh,103), '-') FROM test[/i]

dah boleh tutup kot rasanya topik nih.. Edited November 24, 2011 by Nasrul Hakim Abdul Rahman

[ahaksz! 44]

Topik perbincangan ini ditutup kerana perbincangan telah tamat atau persoalan telah diselesaikan. Harap maklum.