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=@varWO 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

Jul 20 '05 #1

Shervin Shapourian

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" <Ka**********@attbi.com> wrote in message
news:75*************************@posting.google.co m...

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=@varWO 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

Jul 20 '05 #2

Kathy Burke

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!

Jul 20 '05 #3

Shervin Shapourian

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" <ka**********@attbi.com> wrote in message
news:3f*********************@news.frii.net...

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!

Jul 20 '05 #4