472,145 Members | 1,624 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,145 software developers and data experts.

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

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
3 3926
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
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
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

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by jaYPee | last post: by
10 posts views Thread by Jozef de Veuster | last post: by
6 posts views Thread by insirawali | last post: by
reply views Thread by Saiars | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.