April 21, 2008 at 6:05 am
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
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
^^ 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
My first question is what are you using the date for in the package?
April 21, 2008 at 8:43 am
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
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
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
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
thanks that worked perfectly.
April 16, 2012 at 5:18 am
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
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