Argh! varchar to datetime - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Argh! varchar to datetime

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2003-09-11 : 09:31:51

DECLARE @test DATETIME
SET @test = 'Sep 10 2003'

other valid strings include:

Sep 10, 2003
September 10 2003

but not

Sept 10, 2003

Seems unreasonable to me. Should I ask for a refund?

Sam

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-11 : 09:37:35

I think the only options are the 3 letter abbreviation or the full name.

You write a UDF that always replaces "Sept" with "Sep". That's actually the only 4-letter abbreviation I could think of ...

Jan
Mar
Apr
May
Jun
Jul
Aug
Sept
Oct
Nov
Dec

.... of course, for June and July, I guess you could say they have "4 letter abbreviations" as well but those are covered because they equal the full name, which is supported.

- Jeff

SamC
White Water Yakist

3467 Posts

Posted - 2003-09-11 : 11:26:38

The function will have to exist in ASP. In ADO, I've declared it as DATETIME and no error is generated there. The error occues happens in the stored proc (interesting isn't it?).

So, a little ASP front-end processing will haftasuffice.

Thanks professor.

Sam

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-11 : 12:16:03

quote:
Originally posted by SamC

The function will have to exist in ASP. In ADO, I've declared it as DATETIME and no error is generated there. The error occues happens in the stored proc (interesting isn't it?).

So, a little ASP front-end processing will haftasuffice.

Thanks professor.

Sam

Why?


DECLARE @test DATETIME
 SELECT @test = REPLACE('Sept 10, 2003','Sept','Sep')
 SELECT @test

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

SamC
White Water Yakist

3467 Posts

Posted - 2003-09-11 : 12:33:58

Brett did the Florida sun-tan hurt your head

Here's the beef:

ASP calls the proc with @Mydate time set to 'Sept 10 2003'

Once it's in the proc, with the parameter declared as DATETIME, I think any reference to the parameter value will cause the passed 'Sept 10' to pull an error. Eg:

set @MyDate = REPLACE(@MyDate,'Sept','Sep') -- Gonna break dance

SamC

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-11 : 12:44:10

quote:
Originally posted by SamC

Brett did the Florida sun-tan hurt your head

Here's the beef:

ASP calls the proc with @Mydate time set to 'Sept 10 2003'

Once it's in the proc, with the parameter declared as DATETIME, I think any reference to the parameter value will cause the passed 'Sept 10' to pull an error. Eg:

set @MyDate = REPLACE(@MyDate,'Sept','Sep') -- Gonna break dance

SamC

change the parameter to varchar? who knows...do the replace in asp?

anyway...3 weeks off and you get a little rusty...

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

SamC
White Water Yakist

3467 Posts

Posted - 2003-09-11 : 12:46:34

It's easier to catch it in ASP. I wonder if ASP IsDate thinks Sept is a valid date. Maybe I'll convert it to datetime and back to char in ASP just to be sure. Shaken, not stirred.

With luck I'll be paddling a WV river this weekend.

Sam

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-11 : 12:53:13

quote:
Originally posted by SamC
With luck I'll be paddling a WV river this weekend.

Cool...

ever see these sites?

http://www.narr.com/

http://www.riversresort.com/trips_kayak.cfm

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

SamC
White Water Yakist

3467 Posts

Posted - 2003-09-11 : 13:50:52

Here's a couple of sites I visit

For local people that are going on trips / whatever there's Paddle Praddle

and for river information there's American Whitewater organizing the Gauley Fest next weekend. (5000 paddlers)

Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources