Build my query/ - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Build my query/

Author

Topic

lemondash
Posting Yak Master

159 Posts

Posted - 2008-06-27 : 04:44:34

Morning,

I have a query that builds a url path from certain data from the colunms in a database. Please see example below. Part of the url is \Year\Month\Day and i build it from the date field in the database. which works perfectly fine for me is there a way i could put that in a function instead put the query below in everytime i run the main query.

CASE WHEN formatId = 4 --they are many formats so i need to write this for every fomat
THEN 'start OF the url here '
+ CONVERT(VARCHAR(4), YEAR(date)) + '\'
+ CASE LEN(MONTH(date))
WHEN 1
THEN '0'
+ CONVERT(VARCHAR(2), MONTH(date))
+ '\'
+ CASE LEN(DAY(date))
WHEN 1
THEN '0'
+ CONVERT(VARCHAR(2), DAY(date))
WHEN 2
THEN CONVERT(VARCHAR(2), DAY(date))
END
WHEN 2
THEN CONVERT(VARCHAR(2), MONTH(date))
+ '\'
+ CASE LEN(DAY(date))
WHEN 1
THEN '0'
+ CONVERT(VARCHAR(2), DAY(date))
WHEN 2
THEN CONVERT(VARCHAR(2), DAY(date))
END
END + '\' + CONVERT(VARCHAR(50), lDocID)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-27 : 04:50:45

cant you just do:-

SELECT 'start OF the url here ' + convert(varchar(10),date,111)

lemondash
Posting Yak Master

159 Posts

Posted - 2008-06-27 : 05:02:56

So sorry its a Unc path not an Url so i need the date \2007\03\31\ not 2007/03/31.

so sorry other wise i would have used that option.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-06-27 : 05:07:50

So

SELECT 'start OF the url here ' + '\' + REPLACE(CONVERT(VARCHAR(10),[date],111), '/', '\') + '\'

Example ::

SELECT 'C:\myDir' + '\' + REPLACE(CONVERT(VARCHAR(10),getDate(),111), '/', '\') + '\'

Returns :: "C:\myDir\2008\06\27\"

-- sorry for the multiple edits but for some reason the last \ from the example was always dropping off -- had to put it in a

block.

-- DAMN IT. What is going on here... if you try and enter "C:\myDir\2008\06\27\" without the "" this website truncates the last "\"?
-------------
Charlie

lemondash
Posting Yak Master

159 Posts

Posted - 2008-06-27 : 05:57:46

Excellent. cheers to all of you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources