I thought you were going to use the date in the filename - if not then I can't think of a way without using xp_cmdshell in which case you could...

I've often done this using the following code.

create table #data_extract_files

(

filename varchar(20),

depth tinyint,

fileorfolder tinyint

)

insert into #data_extract_files

exec master..xp_dirtree 'C:\data_extracts\',1,1

Hope this helps!

Simon 🙂

Another approach would be to create your FiscalDates table with every date in it:

CREATE TABLE FiscalDates

(

[Id] INT IDENTITY(1,1),

[Date] DATETIME PRIMARY KEY,

[WeekNumber] INT,

[PeriodNumber] INT,

[Quarter] INT,

[FiscalYear] INT

)

This would allow you to quickly...

Are you in a position to change the structure of your dates table?

What will happen when you go into a new fiscal year - will you replace the LAST_YEAR_WEEK_DATE with...

Based on your sample data, this would work, but it relies on #DEF.anotherid being unique and inserted into the table in numerical order - it's not a particularly nice piece...

One way I often do this:

CREATE TABLE #MyTable

(

MyDate DATETIME

)

INSERT INTO #MyTable

SELECT '2008-10-28 11:04:04.207' UNION ALL

SELECT '2009-03-30 13:26:10.433' UNION ALL

SELECT '2009-01-16 14:06:20.033'

SELECT MyDate,

DATEADD(dd, DATEDIFF(dd, 0, MyDate), 0) MyDate_DateOnly

FROM #MyTable

DROP TABLE #MyTable

Hope...

Just to clarify, when people recommend you read Books Online (often shortened to BOL), they usually refer to the documentation of...

Did you read the article Gail Shaw (GilaMonster) linked in reply to your other post - you'll get a much stronger response and far greater help if you follow its...

Thankyou Jeff, I was having real trouble trying to think of a way to work the question number off the number of preceding semi-colons in the string - hadn't...

Unless I'm missing something, couldn't you look for the call to the stored procedure in Profiler by capturing TSQL > SQL:BatchCompleted ? In my setup that's one of the...

Hi Jack,

I'm getting different result sets when I run your query and my query.

Just to clarify, for each question of a survey, I want to split the answers out into...

Is "accounts" a table or a view, and does it have any computed columns in it?

Just a small (but significant :-P) correction to the above post:

sp_configure 'max degree of parallelism', 1

This issue appears in certain conditions (I've only seen it happening with certain statements using...

If it has to be done server-side then I would go with the trigger approach as well but remember to handle updates to the column as well if necessary.

Your departments table will allow the following:

insert into departments values ('12')

I'm guessing you only want it to allow uppercase characters A-Z?