convert date from yyyyddmm to mmddyyyy

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

SQL Server 2000 Forums

SQL Server Development (2000)

convert date from yyyyddmm to mmddyyyy

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-05-03 : 10:48:03

Don writes "ok,

just so you all know, i am very new to sql, i have a database on MS Sql 2000 with the latest service pack running on 2000 server SP4.

what i need to do is import an excel file into my sql database via dts package (so i can automate it later), I can do this easily providing I do it manually every day.

My excel spreadsheet has the followng naming convention "filename date.xls" or "myxlfile 04252004.xls".

I have a stored procedure(included below) that will import my file from a network share, it finds the network folder but expects the date on the filename to be in the yyyyddmm (20042504) format, my delima here is I do not have access to change the output of my original document so i need to find a way to make sql flip flop the date to work or find another way to get my data imported.

=====code starts here=====
CREATE PROCEDURE [dbo].[importme] AS

Declare @xlfilename varchar(120)
Declare @statement varchar(255)
set @xlfilename = CONVERT(char(8),getdate(),109)
SET @statement = 'SELECT * INTO Daily_list FROM OpenDataSource(
''Microsoft.Jet.OLEDB.4.0'',''Data Source="\\path\to\filename' + @xlfilename + '.xls";Extended properties=Excel 9.0'')...Sheet1';
print @statement
exec(@statement)
GO
=====end code======

searching around the net i have found many ways to modify the date but no ways to accomplish what i need.

can anyone help me?

many thanks in advance,

Don"

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-05-03 : 11:17:12

...blah blah blah
DECLARE @Now as DATETIME
SET @Now = getdate()

... blah blah blah

set @xlfilename = RIGHT('00' + LTRIM(STR(MONTH(@NOW), 2)), 2) +
RIGHT('00' + LTRIM(STR(DAY(@NOW), 2)), 2) +
RIGHT('00' + LTRIM(STR(YEAR(@NOW), 4)), 4)

Duane.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources