ASP.NET query substring

Question

I've got this field in my database year_start_1 and it is an integer field and an example of an ouput is 20100827 I'm trying to create a substring to create year, week, day and change the format to be 27/08/2010

Here's what i'm trying

Dim query as String = "Select * from openquery (devbook, 'SELECT cast(year_start_1 as varchar(8)) as year_start_1, DATENAME(DAY, substring(CAST(year_start_1 AS VARCHAR(8)),6,2) + DATENAME(MONTH, substring(CAST(year_start_1 AS VARCHAR(8)),4,2) + DATENAME(YEAR, substring(CAST(year_start_1 AS VARCHAR(8)),1,4))) FROM web_statements')"

It's just throwing up an error and I not sure why:

Server was unable to process request

I have tried using convert but it doesn't work.

Any ideas?

UPDATE

with Chris's suggestion

Dim query as String = "Select * from openquery (devbook, 'SELECT year_start_1, cast(year_start_1 as varchar(8)) as year_start_1, substring(CAST(year_start_1 AS VARCHAR(8)),7,2)+''/''+substring(CAST(year_start_1 AS VARCHAR(8)),5,2)+''/''+substring(CAST(year_start_1 AS VARCHAR(8)),1,4) FROM web_statements')"

Still getting the error

Thanks

UPDATE

Couldn't seem to get it to work within the query so had to do a work around in the ASP.Net code

'POINTS END DATE YEAR
Dim strPointsDateEndYear = Mid(drv.Row("year_end_1"), 3, 2)
Dim strPointsDateEndMonth = Mid(drv.Row("year_end_1"), 5,2)
Dim strPointsDateEndDay = Right(drv.Row("year_end_1"), 2)

Dim strPointsDateEnd As String = strPointsDateEndDay + "/" + strPointsDateEndMonth + "/" + strPointsDateEndYear

Thanks for the help though

Answer 1

Your first DATENAME doesn't seem to close all its brackets before the next DATENAME starts:

DATENAME(DAY, substring(CAST(year_start_1 AS VARCHAR(8)),6,2) + DATENAME[...]

Should I assume be:

DATENAME(DAY, substring(CAST(year_start_1 AS VARCHAR(8)),6,2)) + DATENAME[...]

Edit: Though having fixed that minor error (and converted it to debug) I'm getting errors about casting strings to dates. I'm not sure what the datename stuff is meant to do but how about this:

DECLARE @year_start_1 int
SET @year_start_1 = 20100827 

SELECT cast(@year_start_1 as varchar(8)) as year_start_1, 
substring(CAST(@year_start_1 AS VARCHAR(8)),7,2)+'/'+substring(CAST(@year_start_1 AS VARCHAR(8)),5,2)+'/'+substring(CAST(@year_start_1 AS VARCHAR(8)),1,4)

(converted to a non table based select for test/debug purposes)

So what you would want for your final line of sql would be (untested):

Select * from openquery (devbook, 'SELECT cast(year_start_1 as varchar(8)) as year_start_1, substring(CAST(year_start_1 AS VARCHAR(8)),7,2)+'/'+substring(CAST(year_start_1 AS VARCHAR(8)),5,2)+'/'+substring(CAST(year_start_1 AS VARCHAR(8)),1,4) FROM web_statements')

Second Edit for debug notes:

I thought it might also be worth suggesting how to debug this sort of problem. The error message suggested that the linked server you were sending the sub-statement to was unable to process the request. The first thing to try in this case would be to run the request directly on the server to see what happens. In this case in fact just parsing it on its own would have revealed the first errors I got.

Once you have your statement running form management studio or whatever directly on the server you can try converting it back into the "openquery" style statement and see if ti still works. Bascially break down your complicated scenario into lots of smaller bits to test each one individually.

Answer 2

You are using the datename function on strings, but it should be used on a datetime value. They are nested inside each other so you would end up with just the day, and besided it doesn't even do what you are looking for.

To get the format that you asked for, you can just use string operations to split it up in it's components and add slashes between them:

substring(CAST(year_start_1 AS VARCHAR(8)),7,2) + '/' +
substring(CAST(year_start_1 AS VARCHAR(8)),5,2) + '/' +
substring(CAST(year_start_1 AS VARCHAR(8)),1,4)

Answer 3

hi,

select  convert(varchar(10),convert(smalldatetime,'20100827'),103) will return 08/27/2010 

so, your solution is:

select  convert(varchar(10),convert(smalldatetime,convert(varchar,year_start_1)),103) will return 27/08/2010