Using the AS clause in a SQL stored proc -- problem using datetime column

[KathyB]

I'm trying to concatenate fields in SQL stored proc for use in text
field in asp.net dropdownlist. I'm running into a problem when I try
to use a DateTime field, but can't find the answer (so far) on the
Internet. Was hoping someone here would know?

My sql stored proc:

SELECT AnomalyID, DateEntered + ', ' + Station + ', ' + Problem As
'SelectInfo'
FROM tblAnomaly
WHERE WorkOrder=@varW O AND Signoff=Null
ORDER BY DateEntered

but I get the error:
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value.

I'm not the brightest bulb on the block, so any clues greatly
appreciated!

Thanks, Kathy

[Shervin Shapourian]

Re: Using the AS clause in a SQL stored proc -- problem using datetime column

Kathy,

You need to convert the DateEntered field. Also pay attention that you can
not compare a field to NULL using "=", use "is" operator insted.
Try this:

SELECT AnomalyID,
convert(varchar , DateEntered, 100) + ', ' + Station + ', ' + Problem
As 'SelectInfo'
FROM tblAnomaly
WHERE WorkOrder = @varWO AND Signoff is null
ORDER BY DateEntered

Shervin

"KathyB" <KathyBurke40@a ttbi.com> wrote in message
news:75e8d381.0 310131305.70f69 a4@posting.goog le.com...[color=blue]
> I'm trying to concatenate fields in SQL stored proc for use in text
> field in asp.net dropdownlist. I'm running into a problem when I try
> to use a DateTime field, but can't find the answer (so far) on the
> Internet. Was hoping someone here would know?
>
> My sql stored proc:
>
> SELECT AnomalyID, DateEntered + ', ' + Station + ', ' + Problem As
> 'SelectInfo'
> FROM tblAnomaly
> WHERE WorkOrder=@varW O AND Signoff=Null
> ORDER BY DateEntered
>
> but I get the error:
> The conversion of a char data type to a datetime data type resulted in
> an out-of-range datetime value.
>
> I'm not the brightest bulb on the block, so any clues greatly
> appreciated!
>
> Thanks, Kathy[/color]

[Kathy Burke]

Re: Using the AS clause in a SQL stored proc -- problem using datetime column

Thanks, Shervin. And thanks for the quick reply!!!

Kathy

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Shervin Shapourian]

Re: Using the AS clause in a SQL stored proc -- problem using datetime column

Kathy,

I forgot to tell you about the third parameter of CONVERT. This is the style
used to convert datetime type to string. Check Books Online to get the
complete list of acceptable styles.

Shervin

"Kathy Burke" <kathyburke40@a ttbi.com> wrote in message
news:3f8b418d$0 $201$75868355@n ews.frii.net...[color=blue]
> Thanks, Shervin. And thanks for the quick reply!!!
>
> Kathy
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]