July 26, 2011 at 9:28 am
Hi, I need to delete data older than 30 days. Column that I filter on is char and has date formatted like this, 20110420184518
I need to be able to convert this to date time. I would like to select the data first to make sure I have it correct before I run the delete. Here is what I am trying to run.
select whenreceived from dsitransactionlog where whenreceived <= (GETDATE() -30)
"conversion failed when converting date and/or time from character string."
column is char and has a format of year,month,day,hours,minutes,seconds.
July 26, 2011 at 9:33 am
SET DATEFORMAT YMD
Then try the delete
or
Where char column <= '20110626' -- you can easily rebuild that part out so that it's dynamic.
July 26, 2011 at 9:44 am
I've inherited situations like this as well.
this is common issue when you store datatime in a non-datetime field; sometimes you can hope implicit conversiosn will work, but the format that field is using doesn't match the criteria.
ideally, change the datatype of the column so you can resolve the issue permenantly.
declare @colname char(14)
SET @colname = '20110420184518'
select
isdate( @colname),
@colname,
isdate(
LEFT(@colname,8) + ' ' + SUBSTRING(@colname,9,2) + ':' + SUBSTRING(@colname,11,2) + ':' + SUBSTRING(@colname,13,2)
),
LEFT(@colname,8) + ' ' + SUBSTRING(@colname,9,2) + ':' + SUBSTRING(@colname,11,2) + ':' + SUBSTRING(@colname,13,2)
in my snippet above, you can see that with some serious manipulation, that assumes every field is always 14 characters, you can isnert the formatting to be able to convert them.
If you are stuck witht eh schema design,
I might consider adding a persisted calculated column to the table, or create a view that manipulates the pseudo date column into the right format so you can do proper date calulations against it.
ALTER TABLE Example ADD DateFixed
AS CASE
WHEN LEN(colname) = 14 AND isdate(LEFT(colname,8) + ' ' + SUBSTRING(colname,9,2) + ':' + SUBSTRING(colname,11,2) + ':' + SUBSTRING(colname,13,2) ) = 1
THEN LEFT(colname,8) + ' ' + SUBSTRING(colname,9,2) + ':' + SUBSTRING(colname,11,2) + ':' + SUBSTRING(colname,13,2)
ELSE NULL
END
Lowell
July 26, 2011 at 9:50 am
... and index that new column.
P.S. Nice detailed script Lowell!
July 26, 2011 at 9:54 am
When I have to deal with strings being converted to dates and times, I start from the left and work my way right, using Stuff.
Like:
DECLARE @X CHAR(14) = '20110420184518'
SELECT CONVERT(DATETIME, STUFF(STUFF(STUFF(STUFF(STUFF(@X, 13, 0, ':'), 11, 0, ':'), 9, 0, ' '), 7, 0, '-'), 5, 0, '-'));
Easier than working left-to-right, because then each nested Stuff function has a moving target because the string keeps growing. Not a big deal, but slightly easier. You need to add characters every two places, except the first one (4-character year), and in this you can easily see that it goes 13, 11, 9, 7, 5. Left-to-right, the numbers go 5, 8, 11, 14, 17, and don't match the locations in the original string. Not as easy to read.
The already-mentioned idea of fixing the column so it uses a datetime data type is the best solution long-term, but if you have to convert strings to datetime, Stuff makes it easy.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 26, 2011 at 10:03 am
All of the valid formats require some kind of separator between the various components except for 112 which doesn't include the time components. Your conversion is failing, because your string contains a time component, but no separators, so it's not a recognized format.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 27, 2011 at 5:35 pm
Thanks everyone, I used this in my where clause and it worked perfect.
where CAST(LEFT(whenreceived, 8) as datetime) + 30 < GETDATE()
July 27, 2011 at 5:47 pm
Just to be pedantic, if you have an index on that column, you would be better like this:
WHERE whenreceived < CONVERT(VARCHAR(8),DATEADD(DAY,-30,getdate()),112)
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);July 27, 2011 at 6:37 pm
Care to edit your post MM?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply