Using Filename as a variable & changing character date YYYYMMDD into a proper date

Question

Post reply

Using Filename as a variable & changing character date YYYYMMDD into a proper date

daveangel

SSC Enthusiast

Points: 116
More actions
May 6, 2005 at 3:55 am

#185880

Hi Folks,
I am currently running a process that bulk uploads lots of files with the filename being date stamped i.e. disc_residual_20050330.csv. There is no date field in the table so I would like to create a one using the date in the filename (in this case 30/03/2005). Can anyone help???
Also the date format that I am being sent is YYYYMMDD. Does anyone know how I can convert this text string into a proper date?
Your help would be greatly appreciated.
Regards
Gope

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Does this help?

DECLARE @dt VARCHAR(30)

SET @dt='disc_residual_20050330.csv'

SELECT

CAST(LEFT(RIGHT(@dt,12),8) AS DATETIME)

------------------------------------------------------

2005-03-30 00:00:00.000

(1 row(s) affected)

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

daveangel SSC Enthusiast Points: 116 More actions · Answer 2

Thanks for your quick response. The date part works fine, however I don't know how to write the code so that I can assign the filename as the variable @dt without manually coding it in.

(The SET @dt='disc_residual_20050330.csv' part)

Do you have any ideas?

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

One possible way would be to send the return from xp_cmdshell to a table and query the table for the filename to process:

CREATE TABLE cmdshell (line VARCHAR(260))

GO

INSERT INTO cmdshell EXEC master.dbo.xp_cmdshell 'dir --your stuff would go here'

DECLARE @dt DATETIME

SELECT

@dt= CAST(LEFT(RIGHT(line,12),8) AS DATETIME)

FROM

cmdshell

WHERE --here would be your criteria in case there is more than one file

DROP TABLE cmdshell

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]