Post reply

Date Format

  • April 21, 2008 at 6:05 am

    #185628

    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

  • April 21, 2008 at 6:16 am

    #804901

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

    Ryan Randall

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

  • April 21, 2008 at 8:22 am

    #805000

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

  • April 21, 2008 at 8:36 am

    #805013

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

  • April 21, 2008 at 8:43 am

    #805016

    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

  • April 21, 2008 at 9:01 am

    #805035

    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])".

  • April 21, 2008 at 9:40 am

    #805057

    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

  • April 21, 2008 at 10:30 am

    #805090

    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

  • April 22, 2008 at 7:31 am

    #805517

    thanks that worked perfectly.

  • April 16, 2012 at 5:18 am

    #1473946

    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

  • April 17, 2012 at 11:34 pm

    #1475152

    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