Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
h.singh10
Starting Member
16 Posts |
 Posted - 2012-01-10 : 13:14:34
|
| Hi I am putting the below query in a OLEDB SOURCE through a variable (it is a select statement with a where clause from one date to another).I am getting through this expression. Can you have a check on it please.I dont know whats is wrong with this expression "select TestRecordtype, request_id from department where LOAD_TMSTP between (select max(END_TMSTP) LOG) and (TO_DATE("+RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , @[System::StartTime] ), 2) + "-"+RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , @[System::StartTime] ), 2) + "-" +RIGHT("0" + (DT_STR,4,1252)DATEPART( "yy" , @[System::StartTime] ), 2) + " " +RIGHT("0" + (DT_STR,4,1252)DATEPART( "hh" , @[System::StartTime] ), 2) + "." +RIGHT("0" + (DT_STR,4,1252)DATEPART( "mi" , @[System::StartTime] ), 2) + "." +RIGHT("0" + (DT_STR,4,1252)DATEPART( "ss" , @[System::StartTime] ), 2) +",'DD-MM-YY HH24.MI.SS')) "Thanks |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-01-10 : 13:21:12
|
| is OLEDB Source pointing to Oracle db?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
h.singh10
Starting Member
16 Posts |
Posted - 2012-01-10 : 14:57:47
|
| Yes it is extracting data from oracle database. I am struggling with this expression. Please give suggestions.Thanks |
|
|
|
h.singh10
Starting Member
16 Posts |
Posted - 2012-01-10 : 15:00:53
|
| and btw, this expression works fine when i try to insert the system time in oracle tabel by execute sql task like"insert into log (end_tmstp)values(TO_DATE("+RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , @[System::StartTime] ), 2) + "-"+RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , @[System::StartTime] ), 2) + "-" +RIGHT("0" + (DT_STR,4,1252)DATEPART( "yy" , @[System::StartTime] ), 2) + " " +RIGHT("0" + (DT_STR,4,1252)DATEPART( "hh" , @[System::StartTime] ), 2) + "." +RIGHT("0" + (DT_STR,4,1252)DATEPART( "mi" , @[System::StartTime] ), 2) + "." +RIGHT("0" + (DT_STR,4,1252)DATEPART( "ss" , @[System::StartTime] ), 2) +",'DD-MM-YY HH24.MI.SS')) "It runs perfectely when i do this. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-01-11 : 02:02:55
|
| you cant directly pass variables like that in OLEDB source command. for that you need to first create a string variable to hold the query containing variable and set EvaluateAsExpression true for it. then use above expression inside expression tab.Finally for OLEDB source choose command from variable and map to variable created above------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
OLEDB SOURCE QUERY THROUGH EXPRESSION