DATETIME(8)

Question

DATETIME(8)

mccork

SSC-Insane

Points: 22288
More actions
October 15, 2003 at 6:01 am

#82444

I've scripted all objects from an existing SQL 2000 database and, when executing on another server, one of the SPs fails with:
"CAST or CONVERT: invalid attributes specified for type 'datetime'"
The errant line is:
SELECT CONVERT( DATETIME (8), @ldate, 103)
Well, it's obviously easily fixed by taking out the "(8)", but I'm intrigued how such a syntax error could get into the database in the first place.
Has anybody ever come across something like this before?
Cheers,
- Mark

Cheers,
- Mark

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SQLBill SSC Guru Points: 51440 More actions · Answer 1

It's not an error in the database. The data is correct but it's being CONVERTed incorrectly (from DATETIME to DATETIME(8)).

The stored procedure may not have been parsed. I've written and saved scripts that when I got around to running them, they didn't work because of syntax errors. This may be the case in your situation.

-SQLBill

mccork SSC-Insane Points: 22288 More actions · Answer 2

SQLBill,

I can understand erroneous SPs being created ok due to Deferrred Name Resolution, but this is a syntax error that should prevent the SP being created.

quote:
I've written and saved scripts that when I got around to running them, they didn't work because of syntax errors

How do you do that? Obviously it can be done somehow, as evidenced by the fact that this particular bit of code exists in the database. But, for example, how would I get the following to actually get through the SQL syntax check and create the stored procedure?

 

CREATE PROCEDURE MMTest

AS

SELECT CONVERT( DATETIME (8), '16/10/2003', 103)

Cheers,

- Mark

Cheers,
- Mark

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 3

It probably got into your db as result of a restore or attach from an SQL7 db.

I've noticed the same with a "convert(int(5),floatcol)"- SQL7-statement.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

mccork SSC-Insane Points: 22288 More actions · Answer 4

Thanks Johan. I'll give it a go on a SQL 7.0 box.

Cheers,

- Mark

Cheers,
- Mark

SQLBill SSC Guru Points: 51440 More actions · Answer 5

You are right Mark. I was thinking that you were trying to CREATE the procedure on the other server. I can save a bad CREATE PROCEDURE script without it being parsed, but it will error when I try to run it.

I missed (tired, end of my day) that you were just EXECecuting an existing stored procedure.

-SQLBill