Post reply

SQL server date formate

  • October 13, 2008 at 12:36 am

    #71365

    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

  • October 13, 2008 at 12:50 am

    #883983

    Try the following way:

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

    🙂

  • October 13, 2008 at 1:00 am

    #883988

    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

  • October 13, 2008 at 1:06 am

    #883989

    Ok, use left (..., 15) only. Copy the below code and try it again.

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

  • October 13, 2008 at 2:16 am

    #884006

    kishore, thank u very much for valuable reply

  • October 13, 2008 at 3:00 am

    #884021

    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

  • October 13, 2008 at 3:37 am

    #884033

    Aslo asked here:

    http://www.sqlservercentral.com/Forums/FindPost584664.aspx

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    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

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

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