September 5, 2002 at 10:05 am
Hi
Is it possible to have SQL Server recognize the following character field as a date using convert or cast (or anything else):
2001-03-25-02.27.55.365252
so that I can do date comparisons such as
select * from table where [char in above format] < getdate()
September 5, 2002 at 10:22 am
Try reformatting the string you have into a valid SQL datatime string like follows:
declare @d datetime
declare @s-2 char(100)
set @s-2 = '2001-03-25-02.27.55.365252'
set @d = substring(@s,1,10) + ' ' + substring(@s,12,2) + ':' +
substring(@s,15,2) + ':' + substring(@s,18,2) + '.' + substring(@s,21,3)
print @d
-------------------------
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 6, 2002 at 5:12 am
This is another option that takes into account the rounding on the .365252 however MS SQL datetime for some reason will move 2+ or 1- sec in comparison for some.
DECLARE @date VARCHAR(40)
SET @date = '2001-03-25-02.27.55.365252'
SELECT CAST(LEFT(@date, 10) + ' ' + REPLACE(SUBSTRING(@date, 12,8) + RIGHT(CAST(CAST(SUBSTRING(@date, 20, 6) AS NUMERIC(5,3)) AS CHAR(5)), 4), '.', ':') AS DATETIME)
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Edited by - antares686 on 09/06/2002 05:12:58 AM
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply