"Marko" <nortel@planet.nl> wrote in message
news:1166012891.128359.186250@16g2000cwy.googlegroups.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
>

> 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.

> Marko (nortel@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, esquel@sommarskog.se
>

> 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).

> 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?

> Marko (nortel@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, esquel@sommarskog.se
>
>