CODEWITHSUNDEEP
sqldatetimesql-server-2005dynamicfilenames
Shane Toner
Shane Toner

Reputation: 1

Create dynamic date filename using a stored procedure and a datetime variable

I realise in most cases this is a trivial problem but it just doesn't seem to be working for me at the minute. I have a stored procedure that creates a Datetime variable then using datepart finction to create a string that is set as a filename. I have borrowed another piece of code which outputs in the format yyyymmdd but I need ddmmyyyy and a simple reshuffle doesn't seem to be working. Any help would be appreciated.

 @Date datetime,
 @FileName varchar(14) OUTPUT

 as


/* Year */
SET @FileName = convert(char(4),datepart(yy, @Date))
/* Months */
IF datepart(mm, @Date) <= 9
SET @FileName = @FileName + '0'+ convert(char(1), datepart(mm, @Date))
ELSE
SET @FileName = @FileName + convert(char(2),datepart(mm, @Date))
/* Day */
IF datepart(dd, @Date) < =9
SET @FileName = @FileName + '0'+ convert(char(1), datepart(dd, @Date))
ELSE
SET @FileName = @FileName + convert(char(2),datepart(dd, @Date))

This is the borrowed code and I tried to rearrange it as follows but only the yyyy part appears:

/* Day */
IF datepart(dd, @Date) < =9
SET @FileName = @FileName + '0'+ convert(char(1), datepart(dd, @Date))
ELSE
SET @FileName = @FileName + convert(char(2),datepart(dd, @Date))
/* Months */
IF datepart(mm, @Date) <= 9
SET @FileName = @FileName + '0'+ convert(char(1), datepart(mm, @Date))
ELSE
SET @FileName = @FileName + convert(char(2),datepart(mm, @Date))
/* Year */
SET @FileName = convert(char(4),datepart(yyyy, @Date))

Upon inspection, I have noticed some issues with my code so I changed the lack of a + in the /* year */ block and I also removed th IF statements however this has not sorted the issue:

/* Day */
SET @FileName = convert(char(2),datepart(dd, @Date))

/* Months */
SET @FileName = @FileName + convert(char(2),datepart(mm, @Date))

/* Year */
SET @FileName = @FileName + convert(char(4),datepart(yy, @Date))

Upvotes: 0

Views: 2477

Answers (3)

Zohar Peled
Zohar Peled

Reputation: 82524

If your input is a datetime and desired output is a string in ddmmyyyy format, try this:

@Date datetime,
@FileName varchar(14) OUTPUT

SET @FileName = REPLACE(CONVERT(char(10), @Date, 103),'/', '');

CONVERT function is a built in function in sql server that will return you (in this case) a string formatted as dd/mm/yyyy.

REPLACE function is a built in function that will remove the / chars from that string.

Upvotes: 0

Serpiton
Serpiton

Reputation: 3684

SQLServer 2012 or better as the function FORMAT that IMO is exactly what you need

@Date datetime,
@FileName varchar(14) OUTPUT

SET @FileName = FORMAT(@Date, 'ddMMyyyy')

SQLFiddle demo edited to be standalone

Upvotes: 0

Narti
Narti

Reputation: 181

Would something like this work for you? 

select replace(convert(varchar(10),@Date,103),'/','')

Upvotes: 1

Related Questions