can't convert data into char - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

can't convert data into char

Author

Topic

Porsche.V
Starting Member

4 Posts

Posted - 2008-05-26 : 10:47:17

if i use the query

SELECT iditem, CONVERT(char(20), dt, 105) FROM planeamento

it works just fine.. but in this case the dt (date) field is not recognised..

SELECT planeamento.iditem, idmodelo, item_planeamento.idproduto, item_planeamento.idpele, cor, ordemfabrico, qtd, CONVERT(char(20), dt, 105) FROM produto INNER JOIN item_planeamento ON produto.idproduto = item_planeamento.idproduto INNER JOIN planeamento ON item_planeamento.iditem = planeamento.iditem WHERE planeamento.idfabrica = 51 AND alterado = 'FALSE' AND estado = NULL GROUP BY planeamento.iditem, produto.idModelo, item_planeamento.idProduto, item_planeamento.idPele, item_planeamento.cor, item_planeamento.ordemFabrico, item_planeamento.qtd, planeamento.dt

before, id just had "id" where "CONVERT(char(20), dt, 105)" is, but the date was printed in a unwanted format..

can you help me?

thanks a lot!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-26 : 10:49:10

This happens because dt is not a datetime datatype.
Try

CONVERT(char(20), CAST(dt as datetime), 105)

E 12°55'05.25"
N 56°04'39.16"

Porsche.V
Starting Member

4 Posts

Posted - 2008-05-26 : 10:53:33

but dt in the table is datetime.. i tried though, but the result was the same.. what i find strange is the php always gives me the same error: Notice: Undefined index: dt
no matter if i use CONVERT(char(20), dt, 105) or CONVERT(char(20), CAST(dt as datetime), 105) or CONVERT(char(20), planeamento.dt, 105)

maybe it's just php messing arround? ou is it sql sintax?

thanks in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-26 : 10:54:31

Maybecause you don't have a column name for the converted date column?

SELECT DISTINCT	planeamento.iditem,
		produto.idmodelo,
		item_planeamento.idproduto,
		item_planeamento.idpele,
		item_planeamento.cor,
		item_planeamento.ordemfabrico,
		item_planeamento.qtd,
		CONVERT(CHAR(20), planeamento.dt, 105) AS dt
FROM		produto
INNER JOIN	item_planeamento ON produto.idproduto = item_planeamento.idproduto
INNER JOIN	planeamento ON item_planeamento.iditem = planeamento.iditem
WHERE		planeamento.idfabrica = 51
		AND {produto | planeamento | planeamento }.alterado = 'FALSE'
		AND {produto | planeamento | planeamento }.estado IS NULL

E 12°55'05.25"
N 56°04'39.16"

Porsche.V
Starting Member

4 Posts

Posted - 2008-05-26 : 10:57:11

well, it can be php but sql must be wrong, because when quering it in mssql server i get as result all column but the date column (dt) is "(no column name)".

but if i use the same query but instead of CONVERT(char(20), dt, 105) i just put dt, it prints fine..

Porsche.V
Starting Member

4 Posts

Posted - 2008-05-26 : 11:00:05

that's it man!! CONVERT(CHAR(20), planeamento.dt, 105) AS dt !!

Thanks a lot! you're the man!

(mssql server by the way)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources