Write A Date Back To A Table In An Expression

Mar 12, 2008

Help,

I have a package that maps the current getdate to a variable.

I then want to be able to write that variable back to a table using a sql task using the following expression:

"INSERT INTO CONTROL_BATCH_HEADER VALUES (
" + (DT_STR, 10, 1252) @[Control::intAdtBatchId] + ",
'" + @[Control::strSubjectArea] + "',
convert(datetime, '" + (DT_STR, 30, 1252) @[Control::dteRunDate] + "',131))"


The problem is the date is being written back in the wrong format. I've tried changing the mask to 121 but the month is being written as the day.

Help, how can I make this work regardless of the locals. In oracle I would just put a to_char(date,'dd-mm-yyyy') but not sure what to do here.

Thanks for your help

Stapsey

View 1 Replies


ADVERTISEMENT

Write To A Table In A Rolled Back Transaction.

Mar 4, 2008



Hi,

Is there a way to write to a log-table inside a transaction which is rolled back without rollback of this log-entry.

thanks in advance
Raimund

View 9 Replies View Related

Write DataSet Back To SQL

Sep 10, 2004

Hello everybody,
I have a dataset that i read from an xml file.
I need to import this database back in to sql..
Is there a easy way.. Or do i need to loop thorugh each record in the dataset to import it.

Thanks

Chris

View 2 Replies View Related

How To Write Expression For This...

Feb 22, 2007

=Fields!net_comissionable.Value-(Val(ReportItems!textbox14.value) * Val(ReportItems!staffed_hrs.Value))*14.43

this is in a text box that is calculating the referanced box's and I am looking for the expresion that would be if the result of this is <than 0 to return as a 0
if thenn statment or a case statment how does this work.

Thanks

View 1 Replies View Related

How To Write A Comparison Expression

Dec 8, 2006

Hi,i need a column in a SELECT statement which returns always "true" (As a boolean value, not as a string)so I first tried SELECT field1, field2, true AS field3but this is not working. If I use "0 AS field3" this returns fine the 0, but this is not a boolean but a int.I also tried to make something like     "0 = 0 AS field"but also errors. In that context I discoverd that I could not compare values in the select field - I can use +,-+,* ... for having something like "2 * field1 AS field3" but I'm not clever enough to figure out how I would have a field "field1 < 12 AS field3" (ok I would use CASE, but I need the boolean return value...)Any ideas?Thanks in advance, Klaushow could I write something

View 3 Replies View Related

Expression To Show Result 12 Months Back

Feb 18, 2008

I´m creating a report in SSRS 2005 where you can filter after month and year. The result is the total sale for that particular month. What I want to do, is adding an extra column to compare with the result in the same month last year. F.eks. say that I choose january 2008 I would like to see the results for january 2007.

I thought of writing an expression to do that, but as that isn't my stronges side I would appreciate some help

View 3 Replies View Related

Transact SQL :: Insert Records And Write Back Identity Value

Jun 19, 2015

I'm trying to insert records into "holding" table and write back identity column value (Entry_Key) to the original table. So my setup is I have two tables; tblEWPBulk and tbleFormsUploadEWP. Users will enter records into tblEWPBulk and use BatchID to group records, once batch entry has been completed (usually less than 30 records) user will click on UploadAll button and insert records (not all fields) into tbleFormsUploadEWP. One record in tblEWPBulk can be sent multiple times to the holding table but tblEWPBulk will need to have latest Entry_Key captured. Records are sent from holding table to DB2 z/VSE using SQL stored procedure and based on certain logic records are marked uploaded or certain error capture... that part works fine.

So for example I want to send 

BatchID, AccountNumber, Period, ReceiveDate, AccountType, ReturnType, NetProfitOrLoss, TaxCredit FROM tblEWPBulk to the holding table and write back Entry_Key (identity column) back to the record in tblEWPBulk (field called UploadEntryKey). As I said one record could be sent to the holding table multiple times until uploaded or deleted and UploadEntryKey always needs to be updated so that when results are processed response from the DB2 can be inserted into table and presented to the user.

No foreign key relationship exists since records in the holding table get sent to the archive table and table is truncated and entry_key starting value reset back to 2000... just some DB2 restrictions. 

View 5 Replies View Related

Analysis :: How To Block Writing In Total Cells When Use Excel And Write Back

Nov 16, 2015

I have a cube with a partition configures in write back.

Users in Excel need to see the totals of the line. Unfornately they have the bad idea to write in this cell sometimes and not in the leaf cells.

As there is some MDX code behind in the weight expression field, we got some weird values : one is negative and others ARe 10 times the initial value in the total. So it's very dangerous.

How can we block the writing in this totals cells ?

View 2 Replies View Related

Reporting Services :: Expression To Return Date Value Based On Most Recent Date Only

Oct 7, 2015

How do you Write an expression to Return the date value based on the most recent date only?

Eg.  Date 10-7-2015,  action - done, notes - all items fixed.
       Date 4-5-2016, action - not yet done, notes - buying parts

All these dates are returned based on a search parameter based on a project number.  I only want the most current date and associated fields displayed.

View 4 Replies View Related

Transact SQL :: How To Write A Query To Get Current Date Or End Of Month Date

Sep 29, 2015

how to write a query to get current date or end of month date if we pass year and month as input

Eg: if today date is 2015-09-29
if we pass year =2015 and month=09 then we have to get 2015-09-29
if we pass year =2015 and month=08 then we have to get 2015-08-31(for previous months we have to get EOMonth date & for current month we have to get current date).

View 3 Replies View Related

Date Need Back Off Four Year

Aug 10, 2004

I have a table in sql server, i need to import this table to another
database in same sql server using DTS, In the table, we have a field called
'qualDate', I need to import the record that the qualDate is in the date of
today and back off four years, for example, today is 8/10/2004, back off four
year should be 8/10/2000, so i need only the record that qualDate is between
8/10/2000 to 8/10/2004. And this date should be changed daily. Tomorrow, it
should change to qualDate is between 8/11/2000 and 8/11/2004. How can i do this? it should be done every day! How to do in where clause. Thanks.

View 4 Replies View Related

Passing A Date Name To A Database Back Up

Oct 10, 2000

I am trying to pass a datename in to a database backup. The date is to be retrieved from the 'monthname' column which is stored in the 'tblbill' table. I have included my ridiculous bit of code below (which funnily enough does not work!!) just so that you can see what I am trying to do. Can anyone help?

DECLARE @name varchar(10)
backup database Bill to Disk ='c:@name.dat' with init

@name =select distinct monthname from tblbill where monthname is not null

cheers,
Dennis

View 2 Replies View Related

Convert Week Number Back To First Date

Feb 28, 2008

hi guys,
How could I convert 'week 9 2008' back into the first day of that week ?

is that even possible ?

View 5 Replies View Related

Date Format In Expression

Jul 18, 2007

Hi all,

I would like to generate a file name according to the current date, like 20070716.TXT. In expression, the function Month(Getdate()) will return 7 instead of 07.

How can i do it.

TIA

Patrick

View 4 Replies View Related

Date Not Evaluating In An Expression

Dec 5, 2006



I have been looking all over for some info about other people having this problem, but haven't found anything.

I have a package that needs to download a dated file from an ftp site. I am using a couple script objects to set variables, and one of them is the filename based on the date. I use an expression to get the date:

@[User::varFileName] = (DT_WSTR,4) Year( GetDate()) + (DT_WSTR,2) Month( GetDate()) + Substring((DT_WSTR, 29) GETDATE(), 9, 2)

Everything works really well when I am debugging it locally. However once it is on the server or even once I come back to it in a day or two, I am still seeing the old date. I thought it might be because my variable needed to be set to evaluateexpression = true, however once I did this it hung me and prevented me from debugging and I had to end bus dev studio. Not sure if its because it is being evaluated in two places (as a global and then in a script) but when I took it out of my script it hung again. Its strange in order to get it to work when I am debugging it locally I have to go to each process and evaluate the expressions in there, then it seems to work. thanks!

View 4 Replies View Related

Expression Where Date Is 3 Months Ago?

Apr 17, 2008

What is the correct code for an expression where my "datetime" field is greater then or equal to 3 months ago?

View 1 Replies View Related

Date Check Expression

Feb 22, 2008

feel like a dafty asking this one but can anyone tell me the proper syntax for checking if a date being inserted is greater than today ?

I have tried [datetobechecked] >= getdate()
without success in the expression editor



thanks

View 4 Replies View Related

Calculate Number Of Working Days Back From A Date

May 6, 2008

Hi,
I need to calculate the number of working days from a date backwards. For example 2 working days before Thursday would be the Tuesday (as a basic example)

I use the following code and a Calendar table to calculate the working days from a date but can anyone help with reworking this query to do the reverse

declare @WorkingDate as datetime

SELECT @WorkingDate=dt
FROM tblCalendar AS c
WHERE (@WorkingDays =
(SELECT COUNT(*) AS Expr1
FROM tblCalendar AS c2
WHERE (dt >= @StartDate) AND (dt <= c.dt) AND (IsWeekday = 1) AND (IsHoliday = 0))) AND (IsWeekday = 1)
AND (IsHoliday = 0)

-- Return the result of the function
RETURN convert(varchar(12),@WorkingDate,106)

Hope someone can help

Thanks

View 3 Replies View Related

Data Mining :: How To Get A Single Date Back To Each Item

Oct 5, 2015

I'm trying to get a list of items by there last invoice date, but I keep getting errors. I have to join two table to get all the information needed to match a date with an item number.

select i.[Invoice-date], l.[Item-no]
from [Invoice] as i 
join [invoice-line] as l
on i.[Seq-no] = l.[Seq-no]
 (Select MAX(I2.[Invoice-date]) as LastDate, l2.[ITEM-No]
  from [invoice] AS I2
  JOIN [invoice-line] AS L2
  on i2.[Seq-no] = l2.[Seq-no]
  GROUP BY L2.[Item-no]) ITEMNUMBER
WHERE i.[invoice-date] = lastdate.[invoice-date]
and l.[item-no] = itemnumber.[item-no]

However, I keep getting this..

Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'Select'.
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near ')'.

View 2 Replies View Related

Can Someone Write A Query To Get Last Date Of Each Month

May 19, 2008



Hello,

Can any one write a query to give the last date of each month

thanks alot PROS

View 9 Replies View Related

Format Expression For Date Field

Dec 20, 2007

Hi All,

Can anyone advise me if I can enter an expression into the format property of a field, so the date is displayed as dd/mm/yyyy ?

It is currently mm/dd/yyyy hh:mm:ss

I have been trying with convert and FormatDateTime, but can't seem to get this to work in the Report layout editor. I would like to know how to do it here, rather than changing my query.

Thanks in advance
Humate

View 5 Replies View Related

How To Write Date Range On Case Statement

Oct 30, 2012

I have a case statement as follows which I am using in the where clause to get today's data

(Case when (A.ID is null or B.ID = '' or C.ID= ' ') Then convert(varchar,(Case when A.Time is null then B.Time else A.Time end),103)

else convert(varchar,(Case when A.Time is null then ''" else B.Time end),103) end)

=convert(varchar,getDATE(),103)

However I want to know whether I can use a date range on a case statement

I.e. I want to be able to use BETWEEN statement to get a date range

BETWEEN convert(varchar,getDATE(),103) AND convert(varchar,getDATE()-1,103)

How do I achieve this on a CASE WHEN statement?

View 3 Replies View Related

T-SQL (SS2K8) :: Get Date Format In DB2 As Expression In SSIS

Sep 15, 2015

I am working on a ssis package ,where I have date parameters as format below

"2015-09-01-00.00.00.000000"

How to get this date format in TSQl expression in SSIS package?

View 1 Replies View Related

Converting A Number Into A Date Type Using An Expression

Jul 2, 2007

I am loading data from an iseries into a sql server 2005 DB. Our dates are stored as a numeric value in a format of CYYMMDD where C = Century indicator 20'th is 0 and 21'st is 1, YY = Year, MM = Month and DD = Day!



Today would be 1070701. Now I want to use a derived column which which would be of type date using an expression to do the conversion.



Usually, we would add 19000000 to the number to give us 20070701 then I'd convert it to a string and then substring into a date format.



I'm just getting started with SQL 2005 so I don't know how to do this using an expression.



Any help would be greatly appreciated.



Thanks,

Gray

View 3 Replies View Related

Validating Date Via An Derived Filed/expression

Apr 8, 2008

How can I validate the date that is coming across in a 8 byte character field via an expression? It will either be a valid date or 0. If it is zero I want to make it a null.

View 1 Replies View Related

How To Check Null Or '' In Date Field In Expression And ...............

Aug 16, 2006

I have a column say 'ActivationDate' which is a (database timestamp [DT_DBTIMESTAMP]) which I want to replace with an expression in derived columns

The condition is if 'ActivationDate' field is null or '' then 'Null' else 'ActivationDate'

I am struggling to write this condition. Without condition i.e. at present it saves the value in this database '1753-01-01 00:00:00.000'.
In the preview the 'ActivationDate'field does not show any thing so I recon it is either null or ''





View 6 Replies View Related

Creating An Expression To Modify A Date Field

Sep 1, 2006

In my Derived Column Transformation Editor I have something like this:

DAY([Schedule]) + MONTH([Schedule]) + YEAR([Schedule])

where [Schedule] is a database timestamp field from a OLEDB Datasource.

I want to produce a string something like: "DD/MM/YYYY"

using the expression above, I get something really wierd like "1905-07-21 00:00:00"



Help much appreciated!



View 10 Replies View Related

Default Date In A Derived Column Expression

Nov 20, 2006

one of my SSIS packages use this expression to put todays date in if it is NULL:

(ISNULL(datejs)) ? GETDATE() : datejs

however, what I really want to do though is put a default date in like '2007-01-01' but I get syntax error because SSIS thinks it's a string, which it is I suppose.

Is it possible to do what I want it to?

Thanks

 

 

 

 

View 5 Replies View Related

T-SQL (SS2K8) :: Date Comparison In Two Table By Returning Nearest Date Of Table A In Table B

Jun 9, 2014

I am having a problem in creating query for this exciting scenario.

Table A

ID ItemQtyCreatedDatetime
W001 CB112014-06-03 20:30:48.000
W002 CB112014-06-04 01:30:48.000

Table B

IDItemQtyCreatedDatetime
A001 CB112014-06-03 19:05:48.000
A002 CB112014-06-03 20:05:48.000
A003 CB112014-06-03 21:05:48.000
A004 CB112014-06-04 01:05:48.000
A005 CB112014-06-04 02:05:48.000

I would like to return the nearest date of Table B in my table like for

ID W001 in table B should return ID A002 CreatedDatetime: 2014-06-03 20:05:48.000
ID W002 in table B should return ID A004 CreatedDatetime: 2014-06-04 01:05:48.000

View 3 Replies View Related

Select Criteria Based On Date - Cleaner Way To Write?

Sep 7, 2005

Code:

SELECT (JUL_CURR_CREDITS + JUL_CURR_DEBITS +
AUG_CURR_CREDITS + AUG_CURR_DEBITS +
SEP_CURR_CREDITS + SEP_CURR_DEBITS +
OCT_CURR_CREDITS + OCT_CURR_DEBITS +
NOV_CURR_CREDITS + NOV_CURR_DEBITS +
DEC_CURR_CREDITS + DEC_CURR_DEBITS +
JAN_CURR_CREDITS + JAN_CURR_DEBITS +
FEB_CURR_CREDITS + FEB_CURR_DEBITS +
MAR_CURR_CREDITS + MAR_CURR_DEBITS +
APR_CURR_CREDITS + APR_CURR_DEBITS +
MAY_CURR_CREDITS + MAY_CURR_DEBITS +
JUN_CURR_CREDITS + JUN_CURR_DEBITS) as CURR_AMT



I need to sum these amounts running from July to the month prior to whatever the current month is. So if it was August, it would only be

Code:

SELECT (JUL_CURR_CREDITS + JUL_CURR_DEBITS) as CURR_AMT



Is there a cleaner (shorter) way to iterate through the twelve months than either writing the query 12 times in an IF statement, or 12 CASE statements? This is only part of a query that joins several tables (not shown).

Any suggestions on the best way to write this would be valued.

View 2 Replies View Related

SQL Server 2008 :: Write Query For Date Logic?

May 25, 2015

I have a below query which have a date filter like "EST_PICK_DATE between '2015-02-01' and '2015-06-01'", where the logic is EST_PICK_DATE should be 3 months from the current month and 1st date of next month. Ex for current month MAY, EST_PICK_DATE shoulc be between '2015-02-01' and '2015-06-01'. I need to write below query dynamically. In below query i have hardcoded the value ("EST_PICK_DATE between '2015-02-01' and '2015-06-01'"), but it should take dynamically. How to achieve this?

I am using this query in SSIS package, So Shall i do in SQL level or we should implement this logic in package? If yes, How?

INSERT INTO STG_Open_Orders (Div_Code, net_price, gross_price) SELECT ord.DIV_CODE AS Div_Code, ord_l.NET_PRICE AS net_price, ord_l.gross_price AS gross_price, FROM ORD ord inner join ORD_L ord_l ONord.ORD_ID=ord_l.ORD_ID WHERE ord_l.EST_PICK_DATE BETWEEN '2015-02-01' AND'2015-06-01'

View 1 Replies View Related

Integration Services :: Expression To Parse Filename To A Date

Aug 7, 2015

I am very new to SSIS.  I need a package to iterate through files in folders and subfolders, evaluate the date of the file and delete them if they are over 90 days. I am using the following as a guide.  The reason I am not using a maintenance plan for this is that I did not see a way to use a built in maintenance plan task to copy files.  I already built a package that copies files to the destination.  Now I need to delete files in the destination that are over 90 days old.  

[URL] ...

I have a File System Task to delete files. I have a Script Task with a precedence constraint where I need to build an expression on the constraint between the Script Task and the File System Task for the delete.  

I have database backups in the format of SQLInstanceName_DatabaseName_BackupType_YYYYMMDD_XXXXXXX.bak.  

I created a variable to store the filename and I need an expression that will convert a file name like :

MySQLInstance_MyDB_DIFF_20150803_1700000.bak into 20150803, compare it to getdate -90 days.  

I created another variable for the Max File Age.  I built the below but when I click on Evaluate Expression I get an error stating, "The expression might contain an invalid token, an incomplete token, or an invalid element." 

DATEDIFF("dd",(DT_DATE)(SUBSTRING(RIGHT( @[User::strFileName], 20 ),1,8), GETDATE()) >  @[User::intFileMaxAge]

View 5 Replies View Related

Date Issue With Derived Column / Expression Language

Jan 10, 2006

Can someone confirm this for me?  The expression language in SSIS has the same limitations on date ranges as Sql Server?  That limitation is that valid date ranges are from Jan 1, 1753 to Dec 31, 9999.

When ever I try to do a date function (DATEPART, for example) in a Derived Column Transformation on a date less than 1/1/1753, I get an error.  I initially discovered this when bringing data over from Oracle to Sql Server.  Just as a test, I created a text file filled with various dates and tried to import it.  Whenever a date is less than 1/1/1753, it blows up. 

For example, this expression code - DATEPART("YEAR",Date) will yield this error - [Derived Column [24]] Error: The "component "Derived Column" (24)" failed because error code 0xC0049067 occurred, and the error row disposition on "output column "YEAR" (80)" specifies failure on error. An error occurred on the specified object of the specified component. 

As a workaround, I've been using a Script Component to do date checking, but this is obviously not ideal.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved