Much ado about nothing - 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)

Much ado about nothing

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2002-11-23 : 13:44:14

This is a question about a time format, not ADO, but it's almost about nothing.

I'm reading POP3 email using POP3ASP. The Time of the email is returned as a string in the format

Fri, 22 Nov 2002 14:06:55 -0500

Before I start hacking away the "Fri," in the front and the GMT correction -0500 in the back to make this a proper DATETIME value. I'd appreciate any comments from the field about a simple way of converting this format to DATETIME.

Sam

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-11-23 : 16:14:14

Take a look at the format() and dateadd() and cdate() functions in vb.

Edited by - ValterBorges on 11/23/2002 16:15:33

SamC
White Water Yakist

3467 Posts

Posted - 2002-11-23 : 17:28:18

I'd tried the usual ASP suspects like CDATE and FORMAT. All the functions didn't like the -0500 in the string. That's why I referred to "hacking" the head and tail off the string before conversion.

It's pretty easy to chop off trailing characters to make a valid conversion. I'd never seen this time format anywhere before, and thought there might be a off the shelf conversion somewhere.

Sam

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-11-24 : 11:28:06

Try this:

Dim strPop3AspDate
Dim dtmPop3Date

strPop3AspDate = "Fri, 22 Nov 2002 14:06:55 -0500"

strPop3AspDate = LEFT(strPop3AspDate, INSTR(strPop3AspDate,'-')-2)
strPop3AspDate = MID(strPop3AspDate, INSTR(strPop3AspDate,",")+1)
dtmPop3Date = CDATE(strPop3AspDate)

If you really insist on having something of the shelf I believe there are windows api calls that can to the conversion for you.
Search msdn for timezone or gmt time conversion.

Edited by - ValterBorges on 11/24/2002 11:30:23

SamC
White Water Yakist

3467 Posts

Posted - 2002-11-24 : 12:24:09

I've gone ahead and chopped off the 5 characters in front and back of the date string (like what you've suggested), but in my stored procedure:

declare mydate datetime
SET @date = LEFT(RIGHT(LTRIM(RTRIM(@date)),LEN(@date)-5),LEN(@date)-10)
set mydate = cast(@date as datetime)

Thanks for your help.

Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources