Missing the zero (0)

[Marko]

Hello all,

I hope someone can help me; if got a value in a cell (040 1234567),
when I run a query
in the Analyser I got as respons only 40 1234567, so missing the zero
(0) not the whole number is displayed. When I run a query on a cell
with value 1234567 I received the number 1234567 and that's oke. The
Data Type of the Column is Char.

Thanks,

Marko

[www.ciquery.com]

Try it with VARCHAR , which accepts numbers and string

--

Jack
___________________________________
Post IT Jobs for FREE - www.ciquery.com
Over 14,000 registered IT focused individuals
"Marko" <no****@planet.nlwrote in message
news:11**********************@16g2000cwy.googlegro ups.com...

Hello all,

I hope someone can help me; if got a value in a cell (040 1234567),
when I run a query
in the Analyser I got as respons only 40 1234567, so missing the zero
(0) not the whole number is displayed. When I run a query on a cell
with value 1234567 I received the number 1234567 and that's oke. The
Data Type of the Column is Char.

Thanks,

Marko

[Erland Sommarskog]

Marko (no****@planet.nl) writes:

I hope someone can help me; if got a value in a cell (040 1234567),
when I run a query
in the Analyser I got as respons only 40 1234567, so missing the zero
(0) not the whole number is displayed. When I run a query on a cell
with value 1234567 I received the number 1234567 and that's oke. The
Data Type of the Column is Char.

Cell? Do you mean column value in a row? Or are you running a query
against Excel?

What exact query do you run?

All I can say is that the leading zero should normally not be dropped
for a character value, which this obviously is, there is a space in it.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Marko]

Erland Sommarskog schreef:

Marko (no****@planet.nl) writes:

I hope someone can help me; if got a value in a cell (040 1234567),
when I run a query
in the Analyser I got as respons only 40 1234567, so missing the zero
(0) not the whole number is displayed. When I run a query on a cell
with value 1234567 I received the number 1234567 and that's oke. The
Data Type of the Column is Char.

Cell? Do you mean column value in a row? Or are you running a query
against Excel?

What exact query do you run?

All I can say is that the leading zero should normally not be dropped
for a character value, which this obviously is, there is a space in it.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Hello Erland,

It's a column value in a row, the query I run:

SELECT @Registernum=(
SELECT regnr FROM dbo.planningview
WHERE convert(CHAR(11),date ,106) = convert(CHAR(11),GETDATE(),106)
AND
convert(CHAR(8),Starttime ,108) <= convert(CHAR(8),GETDATE(),108)
AND
convert(CHAR(8),Stoptime ,108) >= convert(CHAR(8),GETDATE(),108) AND

groupName = 'Application' AND shortname = 'Helpdesk' AND deleted =0
)

if (@Registernum is null) Select @Registernum=0
select @Registernum
GO
What I get is 401234567 instaed of 0401234567, so I am missing the 0
(zero).

[Erland Sommarskog]

Marko (no****@planet.nl) writes:

It's a column value in a row, the query I run:

SELECT @Registernum=(
SELECT regnr FROM dbo.planningview
WHERE convert(CHAR(11),date ,106) =
convert(CHAR(11),GETDATE(),106)
AND
convert(CHAR(8),Starttime ,108) <=
convert(CHAR(8),GETDATE(),108)
AND
convert(CHAR(8),Stoptime ,108) >=
convert(CHAR(8),GETDATE(),108) AND

groupName = 'Application' AND shortname = 'Helpdesk' AND
deleted =0
)

if (@Registernum is null) Select @Registernum=0
select @Registernum
GO
What I get is 401234567 instaed of 0401234567, so I am missing the 0
(zero).

You failed to include the declaration of @Registernum. I would guess
you have declared it as integer or decimal.

Also, write conditions like

WHERE convert(CHAR(11),date ,106) = convert(CHAR(11),GETDATE(),106)

as

WHERE date >= convert(char(8), getdate(), 112)
AND date < convert(char(8), dateadd(DAY, getdate() + 1), 112)

This can be essential for performance. If the date column is indexed,
the index is no of use if you put the column into an expression.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for
SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Marko]

Erland Sommarskog schreef:

Marko (no****@planet.nl) writes:

It's a column value in a row, the query I run:

SELECT @Registernum=(
SELECT regnr FROM dbo.planningview
WHERE convert(CHAR(11),date ,106) =
convert(CHAR(11),GETDATE(),106)
AND
convert(CHAR(8),Starttime ,108) <=
convert(CHAR(8),GETDATE(),108)
AND
convert(CHAR(8),Stoptime ,108) >=
convert(CHAR(8),GETDATE(),108) AND

groupName = 'Application' AND shortname = 'Helpdesk' AND
deleted =0
)

if (@Registernum is null) Select @Registernum=0
select @Registernum
GO
What I get is 401234567 instaed of 0401234567, so I am missing the 0
(zero).

You failed to include the declaration of @Registernum. I would guess
you have declared it as integer or decimal.

Also, write conditions like

WHERE convert(CHAR(11),date ,106) = convert(CHAR(11),GETDATE(),106)

as

WHERE date >= convert(char(8), getdate(), 112)
AND date < convert(char(8), dateadd(DAY, getdate() + 1), 112)

This can be essential for performance. If the date column is indexed,
the index is no of use if you put the column into an expression.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Hello Erland,

I put the DECLARE above the Query:

DECLARE @Registernum integer

but no results, I got still the value without the zero (0).
The Data Type of the Column is CHAR, is this maybe wrong?

[Erland Sommarskog]

Marko (no****@planet.nl) writes:

I put the DECLARE above the Query:

DECLARE @Registernum integer

but no results, I got still the value without the zero (0).
The Data Type of the Column is CHAR, is this maybe wrong?

Your result set consists of a single integer variable. QA presents integer
values without leading zeroes. How could it now that the integer value
comes from a char column?

If you want to see a leading zero, you should declare the variable as
char or varchar of suitable length.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx