March 21, 2007 at 2:30 pm
I have a linked server and get data through a view with an Openquery. I have a field in the openquery that I am trying to reformat from 12206 which I am trying to make it 1/22/06. There will be some that are 120106 which needs to become 12/01/06. I have tried the CAST, CONVERT and SUBSTRING......
Anyone have ideas?
Jan
March 21, 2007 at 11:03 pm
I don't think you use local variables in the view...
MohammedU
Microsoft SQL Server MVP
March 22, 2007 at 10:14 am
You're going to have to do a lot more work in order to convert from that form of string.
One problem that I see immediately is that formatting is going to be an issue. Let's say that the string is 012206 or 12206. Either of these should go to 1/22/06, but you're going to have to make determinations based on the length of the string and/or the leading edge. But, let's assume that the the '0' is there:
DECLARE @STR VARCHAR(6)
SET @STR= '012206'
SELECT CAST(STUFF(STUFF(@str,5,0,'/'),3,0,'/') AS SMALLDATETIME)
In the view just use your column name in place of where I have the variable.
SELECT CAST(STUFF(STUFF(x.1,5,0,'/'),3,0,'/') AS SMALLDATETIME)
FROM SomeTable AS x
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply