DATETIME conversion

Question

Post reply

DATETIME conversion

kevaburg

SSCoach

Points: 18131
More actions
August 7, 2014 at 7:55 am

#310553

Hi Folks,
I am trying to create a custom filename that has the current date and time appended to the original filename. What I have created up until now is:
select 'plan_werte_'+cast( REPLACE( CONVERT(VARCHAR(26),getdate(),112),':','-') as varchar(50))+'.csv'
This produces the filename:
plan_werte_20140807.csv
I need to have the time tacked onto the end as well and I am having problems doing that. In essense the filename should be:
plan_werte_YYYYMMDDHHMI.csv
Can anyone please help?
Regards,
Kev

Viewing 6 posts - 1 through 5 (of 5 total)

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

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

You will want to use format 121 to get the time in the conversion.

Something like this

select 'plan_werte_'+

REPLACE(

left(

CONVERT(VARCHAR(26),getdate(),121)

,16)

,':','')

,'-','')

,' ','')

+'.csv'

Cadavre SSC-Forever Points: 41690 More actions · Answer 2

Or append 114 to your current query: -

SELECT 'plan_werte_' + CAST(REPLACE(CONVERT(VARCHAR(26), GETDATE(), 112) + CONVERT(CHAR(5), GETDATE(), 114), ':', '') AS VARCHAR(50)) + '.csv';

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

kevaburg SSCoach Points: 18131 More actions · Answer 3

Cadavre (8/7/2014)
Or append 114 to your current query: -
SELECT 'plan_werte_' + CAST(REPLACE(CONVERT(VARCHAR(26), GETDATE(), 112) + CONVERT(CHAR(5), GETDATE(), 114), ':', '') AS VARCHAR(50)) + '.csv';

Thanks for the answer but the problem is that there can be no special characters in the solution, in this case '-'. Although it is very close 🙂

kevaburg SSCoach Points: 18131 More actions · Answer 4

kevaburg (8/7/2014)
Hi Folks,
I am trying to create a custom filename that has the current date and time appended to the original filename. What I have created up until now is:
select 'plan_werte_'+cast( REPLACE( CONVERT(VARCHAR(26),getdate(),112),':','-') as varchar(50))+'.csv'
This produces the filename:
plan_werte_20140807.csv
I need to have the time tacked onto the end as well and I am having problems doing that. In essense the filename should be:
plan_werte_YYYYMMDDHHMI.csv
Can anyone please help?
Regards,
Kev

Thank you Anthony! That is the perfect solution! The problem, and the reason that I have to be so specific with the format, is that we use Oracle Service Bus in the organisation and every two hours the difference between two cubes must be transported from the SQL Server, into a DWH and then into the Bus. The Bus though expects the data in a specific format. The problem is solved though now and so many thanks for your help.

Regards,

Kev

Ant-Green SSC Guru Points: 113507 More actions · Answer 5

kevaburg (8/8/2014)
Cadavre (8/7/2014)
Or append 114 to your current query: -
SELECT 'plan_werte_' + CAST(REPLACE(CONVERT(VARCHAR(26), GETDATE(), 112) + CONVERT(CHAR(5), GETDATE(), 114), ':', '') AS VARCHAR(50)) + '.csv';
Thanks for the answer but the problem is that there can be no special characters in the solution, in this case '-'. Although it is very close 🙂

This would work, it would just be a case of putting in more replace statements to remove the characters you don't need.