Convert a string to a Datetime

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

Transact-SQL (2000)

Convert a string to a Datetime

Author

Topic

JimRoss
Starting Member

2 Posts

Posted - 2003-09-01 : 13:17:13

I have a table that I'm trying to convert from an Access database that contains a "key" field that is char(10) and formatted as yyyymmddNN, where NN is an incremented field for sequence within the date. Some examples of the key would be:
2002082201
2002082202
... etc. The first 8 positions are allegedly a valid date, but this can't be guaranteed.

I need to write an UPDATE for the table that will extract the "date" part and use it to update a date field (in the same table)

I have tried several variations on CONVERT and CAST, but I don't seem to be holding my tongue right or something. Books Online shows a "style" parameter that seems to include the format I have to deal with, but I can't seem to get it to work.

The update would look something like:
UPDATE ServiceRequest
SET ServiceRequest.DateReceived = xxx

xxx = [expression that converts ServiceRequest.RequestKey to a date, where RequestKey is char(10) and formatted as described above]

And, as I mentioned, there is no guarantee that the table won't include some bogus dates.

Thanks,

Jim Ross
MS/MVP ASP.NET [Visual C++/MFC emeritus]

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-09-01 : 13:33:00

set qq = cast(cast(qq as char(8)) as datetime)

JimRoss
Starting Member

2 Posts

Posted - 2003-09-01 : 13:43:37

Thanks Stoad, it worked perfectly. !!!!

Jim Ross
MS/MVP ASP.NET [Visual C++/MFC emeritus]

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-09-01 : 14:11:06

Glad to hear it, Jim.

For SQL gurus:
BTW, what works faster: left(qq,8) or cast(qq as char(8)) ???

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-02 : 11:26:14

You might want to add a WHERE Clause

WHERE ISDATE(CONVERT(datetime,LEFT(RequestKey,8))) = 1

So the statement doesn't blow up because of a bogus date....

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-09-02 : 12:15:37

Cheers, Brett!

WHERE ISDATE(LEFT(RequestKey,8)) = 1

instead of

WHERE ISDATE(CONVERT(datetime,LEFT(RequestKey,8))) = 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources