Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL server date formate Expand / Collapse
Author
Message
Posted Monday, October 13, 2008 12:36 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 4:41 AM
Points: 1,058, Visits: 3,010
select left(replace(replace(convert(varchar,getdate(),120),' ',' '),':',''),18)+'.txt'

The above script result is 2008-10-13 120155.txt

I want result like - 200810131201.txt

canu you tell how to eliminate the last two character.

Thanks
Post #584666
Posted Monday, October 13, 2008 12:50 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, March 13, 2014 5:49 AM
Points: 1,391, Visits: 608
Try the following way:

select replace (replace (left (replace (replace (convert (varchar, getdate(), 120), '', ''), ':', ''), 16), '-', ''), ' ', '') + '.txt'

:)



Post #584671
Posted Monday, October 13, 2008 1:00 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 4:41 AM
Points: 1,058, Visits: 3,010
hi kishore, thanx for reply

It was working fine, but i need to be remove the sec in date.

as per query result is 20081013122619.txt

I want display only 200810131226.txt

thanks

Post #584677
Posted Monday, October 13, 2008 1:06 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, March 13, 2014 5:49 AM
Points: 1,391, Visits: 608
Ok, use left (..., 15) only. Copy the below code and try it again.

select replace (replace (left (replace (replace (convert (varchar, getdate(), 120), '', ''), ':', ''), 15), '-', ''), ' ', '') + '.txt'



Post #584680
Posted Monday, October 13, 2008 2:16 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 4:41 AM
Points: 1,058, Visits: 3,010
kishore, thank u very much for valuable reply
Post #584702
Posted Monday, October 13, 2008 3:00 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 4:41 AM
Points: 1,058, Visits: 3,010
Hi kishore, one more clarification, pls tell me

alter proc dateformatproc as
Declare @FileName varchar(255)
select @FileName = 'c:\Northwind Backup Plan4_' +
replace (replace (left (replace (replace (convert (varchar, getdate(), 120), '', ''), ':', ''), 15), '-', ''), ' ', '') + '.txt'
--select @FileName

the above script i had create procedure, This procedure return values like c:\Northwind Backup Plan4_200810151415.txt,
so how to call that procedure at place @messagefile location below

declare @rc int
exec @rc = master.dbo.xp_smtp_sendmail
@FROM = N'WJMN0130136',
@FROM_NAME = N'JG-IDC-SQL Server',
@TO = N'ananda.murugesan@zmail.ril.com',
@replyto = N'',
@CC = N'',
@BCC = N'',
@priority = N'NORMAL',
@subject = N'Hello IDC SQL Server Mail @ jamnager',
@message = N'Goodbye have a nice day, this is for test mail from MSSQL',
@messagefile = N'',
@type = N'text/plain',
@attachment = N'',
@attachments = N'',
@codepage = 0,
@server = N'10.4.54.22'
select RC = @rc
go


Post #584720
Posted Monday, October 13, 2008 3:37 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:57 AM
Points: 43,045, Visits: 36,205
Aslo asked here:
http://www.sqlservercentral.com/Forums/FindPost584664.aspx



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #584738
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse