Posted by Erland Sommarskog on 12/14/06 08:54

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

Books Online for
SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]