Date Format

  • hi,

    Right now i am using

    (DT_STR, 4, 1252) DAY( GETDATE() )+""+(DT_STR, 4, 1252) MONTH( GETDATE() )+""+(DT_STR, 4, 1252) YEAR ( GETDATE() )

    which prints 2142008.

    How would i change the above code to display the date as 210408.

    thanks

  • select replace(convert(varchar(8), getdate(), 3), '/', '')

    Ryan Randall

    Solutions are easy. Understanding the problem, now, that's the hard part.

  • ^^ but this way i am not able to put it in expression it gives me an error when evaluating.

  • My first question is what are you using the date for in the package?

  • I'm assuming you are using this as a variable. If so, in your properties section, there is a EvaluateAsExpression option for which the default is false. Set it to TRUE and the code Ryan provided should work great.

    Post back if you have any questions

    Marvin Dillard
    Senior Consultant
    Claraview Inc

  • ok so what i am doing is :

    I have a execute sql task and in expressions i am trying to insert some values to the table so i am using sqlstatementsource.

    I have "Insert into table (filename) values ((DT_STR, 4, 1252) DAY( GETDATE() )+""+(DT_STR, 4, 1252) MONTH( GETDATE() )+""+(DT_STR, 4, 1252) YEAR ( GETDATE() ) + @[User::Filename])".

    ^^ this works fine... but I get error when i put "Insert into table (filename) values (replace(convert(varchar(8), getdate(), 3), '/', '') + + @[User::Filename])".

  • Try this. Create a new variable with this expresion

    "select replace(convert(varchar(8), getdate(), 3), '/', '') " + @[User::Filename])

    Then on the properties section of the newly created variable, set evaluateasexpression to TRUE. Then assign that new variable as a parameter on the execute SQL Statement task.

    Let me know if you have any questions or don't understand. I may be able to whip up a quick package and send it to you

    Marvin Dillard
    Senior Consultant
    Claraview Inc

  • Looks like you've done most of the work already, you just need to use the RIGHT function with your expression.

    Also, add month and day to 100, and take the rightmost 2 characters so that you don't loose your leading zero.

    RIGHT((DT_STR,4,1252)(100 + DAY(GETDATE())),2) + "" + RIGHT((DT_STR,4,1252)(100 + MONTH(GETDATE())),2) + "" + RIGHT((DT_STR,4,1252)YEAR(GETDATE()),2)

    HTH

    Kindest Regards,

    Frank Bazan

  • thanks that worked perfectly.

  • Hi Folks,

    Need a help, I have a business requirement to genrate a CSV file with following name INDUS_CUST_RM_UPLOAD_20120416_163238.CSV where 20120416 is date in yyyymmdd format and 163238 is time stamp in hhmmss format.

    Now the challange is not in generating this file which I am managing quite easily using a BCP command as mentioned below

    DECLARE @FileName varchar(100),

    @bcpCommand varchar(2000)

    SET @FileName ='D:\TalismaImport\Live\WMSRelatedExport\INDUS_CUST_RM_UPLOAD_'+REPLACE(REPLACE(REPLACE(CONVERT(varchar,GETDATE(), 20),'-',''),':',''),' ','_')+'.CSV'

    SET @bcpCommand = 'bcp "select CustID,ECN from tlstagingdb.dbo.TempGK_C1FlagRMreport" queryout "'

    SET @bcpCommand = @bcpCommand + @FileName + '" -T -c -t ","'

    EXEC master..xp_cmdshell @bcpCommand

    Now the challange comes in I have to FTP this file to a AIX box.

    I am open to using any technology be it ssis or simple windows ftp. Folder containing this file also contains backdataed files.

    I tried giving varaible in ssis file connection manager to ftp the file using ftp manager but was unsucessful.

    Gave a thought to this and a solution what i have in mind is to insert file name and location in a table while genrating the file (BCP) and then ssis should query that table pickup the file name and path and ftp it to the required location.

    Any ideas are welcomed here.

    Regards,

    Shrey Sheth

  • I know this is an old post. But with the month extraction, I first check in the formula if the month is greater than 9, else I stuff a "0" in front. That way you always get a string with six characters.

    ----------------------------------------------------

Viewing 11 posts - 1 through 10 (of 10 total)

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