May 15, 2003 at 2:42 pm
Hi All,
Im trying cast a varchar field (XXXdate) as datetime. Basically there are dates in the varchar field:
i.e.
Fri, 6 Dec 2002 15:08:59 EST
Fri, 06 Dec 2002 14:52:26 -0500
I would like to do something such as:
select cast(XXXdate as datetime) from XXXtable with (nolock)
but of course i get the error:
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.
Can someone give me a tip on how to do this? Thanks again!
May 15, 2003 at 3:13 pm
Try
CREATE FUNCTION MyStrip(@v varchar(50)) RETURNS Varchar(50) AS
BEGIN
Declare @vv Varchar(50)
Set @vv=Right(@v,DataLength(@v)-CharIndex(' ',@v))
RETURN Left(@vv,DataLength(@vv)-CharIndex(' ',Reverse(@vv)))
END
Select dbo.MyStrip('Fri, 06 Dec 2002 14:52:26 -0500')
May 15, 2003 at 3:16 pm
It can be done. I trimmed off the 'FRI' part and it converted. I used a substring.
Patrick
Quand on parle du loup, on en voit la queue
May 15, 2003 at 3:17 pm
ooh ok, but i need every single character so trimming off the FRI wouldnt work.
still have to try the other method above..keep ya posted..thx
May 15, 2003 at 3:56 pm
You do not need the 'Fri' characters to convert correctly to a date type. The Day, Month and Year is enough.
See Books On Line for the CONVERT FUNCTION to convert back from date type to Varchar.
May 16, 2003 at 8:48 am
Hi 5409045121009,
I tried the function you mentioned, and modified it a little so that it gave me the whole string and it worked.
CREATE FUNCTION MyStrip1(@v varchar(50)) RETURNS Varchar(50) AS
BEGIN
Declare @vv Varchar(50)
Set @vv=Right(@v,DataLength(@v))
RETURN Left(@vv,DataLength(@vv))
END
Problem though is that it is returning it as a varchar value. I need to return it as a datetime value. Is it true that you can only cast/convert to a datetime value type if the string you're converting from is in one of the accepted datetime formats (alphabetic, numeric, odbc, time, unseparated string - from BOL)?
May 17, 2003 at 10:48 pm
CREATE FUNCTION MyStrip(@v varchar(50)) RETURNS DateTime AS
BEGIN
Declare @vv Varchar(50)
Set @vv=Right(@v,DataLength(@v)-CharIndex(' ',@v))
RETURN Cast(Left(@vv,DataLength(@vv)-CharIndex(' ',Reverse(@vv))) as DateTime)
END
Select dbo.MyStrip('Fri, 06 Dec 2002 14:52:26 -0500')
Select dbo.MyStrip('Fri, 6 Dec 2002 15:08:59 EST')
May 19, 2003 at 5:29 am
Remember, in the datetime data type, dates and times are stored as eight bytes of information. The first four bytes are the number of days before or since January 1, 1900. The last four bytes are the number of milliseconds since midnight. So, you CAN'T have FRI or EST or -0500 or any other information in a DATETIME data type field.
-SQLBill
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply