Passing Date Variable In British Format

Jan 1, 2014

I want to pass a couple of date variables in British date format to a function

Here's the function

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

[code]....

View 7 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: Passing Table Name And Date Value As Variable

Jun 10, 2015

I need building the dynamic sql . When I create below script and executed the procedure passing the table name and date values its giving me error saying "Incorrect syntax near '>'".

Create PROCEDURE sampleprocedure
@tablename AS VARCHAR(4000),
@date as date
AS
BEGIN
declare @table varchar(1000)

[Code] ....

View 9 Replies View Related

Create Dts Variable For Date In Character Format Yyyymmdd

Nov 23, 1999

I'm trying to create a variable for use in a DTS job for yesterday's date in the string format YYYYMMDD for use in pulling yesterday's material transactions from my ERP system. I've got the Query statement created and working fine, but I have to manually go into the DTS job and insert the proper date manually. I'm looking into an Active-X script inserted into the workflow for the job to create a global variable, but I can't seem to come up with anything that works.
Any help or direction would be appreciated.
Thanx in advance!!
Glenn

View 1 Replies View Related

How To Convert Long Date Format To Short Date Format In Store Procedure.

Feb 1, 2008

E.g, i have a store procedure. The start date is long date (4/15/2007 3:00pm). i want to select the start date with a particular date (short date format 4/15/2006). Thanks in advance.

View 1 Replies View Related

Passing A SSIS Global Variable To A Declared Variable In A Query In SQL Task

Mar 6, 2008

I have a SQL Task that updates running totals on a record inserted using a Data Flow Task. The package runs without error, but the actual row does not calculate the running totals. I suspect that the inserted record is not committed until the package completes and the SQL Task is seeing the previous record as the current. Here is the code in the SQL Task:

DECLARE @DV INT;
SET @DV = (SELECT MAX(DateValue) FROM tblTG);
DECLARE @PV INT;
SET @PV = @DV - 1;

I've not been successful in passing a SSIS global variable to a declared parameter, but is it possible to do this:

DECLARE @DV INT;
SET @DV = ?;
DECLARE @PV INT;
SET @PV = @DV - 1;


I have almost 50 references to these parameters in the query so a substitution would be helpful.

Dan

View 4 Replies View Related

SQL's America Date Format Conflict With Australian Date Format

Nov 14, 2006



Hi

I am trying get my VB6 application to insert a record into a table (SQL Express) which has a datetime column but it would not process if the data format is differ to *American Date format*.

The date() function in VB returns 15/11/2006 which is in Australian Date format (DD/MM/YYYY) according to my setting in "Reginal and Lanuage Option-> Locale 0> English (Australia)" setting.

I get the following error:

Msg 242, Level 16, State 3, Server KITSQLEXPRESS, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.

My computer's locale is set to English (Australia) and I expect the datetime format would follow what is set in system locale


I've read an article somewhere on the net about how SQL 2005 eliminate the confusion of date conversion when read/write datetime records into a table...but it seems to me that it is still as in-flexible as MS Access


Is there a setting in the database that takes care of it?

Thanks

View 6 Replies View Related

British Airways

Feb 27, 2004

WHERE IS EVERYBODY?

View 10 Replies View Related

Converting SQL Date Format To Oracle Date Format

May 28, 2008

Hi,

I have a column date in my database which I should send it to Oracle database. The Date format in Oracle is number. I don’t know how should I convert the date to that format?
Example :
SQL FormatOracle Format
02/16/05 105046

Thanks.

View 6 Replies View Related

Passing Variable Value Through Environment Variable

Feb 15, 2008



Hi All!

I have a parent package that contains two children... The second child depends on the succes of the first child.

THe first child generates a variable value and stores it in an Environment variable ( Visibility - All ) ...After the first succeeds, the second will start executing and will pick up the variable value from environment variable( through package configuration setting )...

Unfortunately, this doesn't work...As the second child picks the stale value of the environment variables...Essentially it assigns variable value not after the first child is finished, but right at the beginning of parent execution...

I tried to execute coth children as Out Of Proc as well as In Proc...The same

Would anybody have an idea how to resolve this problem?

Thanks in advance for any help!

Vladimir

View 5 Replies View Related

Conversion Of Date From Legacy Systems With 7 And 6 Digit Format To DD/MM/YYYY Format

Nov 19, 2014

We are migrating data from old DB2 systems to sql server 2012, the DATE FORMAT in those systems is in decimal format with 7 digits. CYYMMDD format.

I need to convert this into DD/MM/YYYY format.

View 9 Replies View Related

Date In String Format Has To Be Changed Datetime Format

Jun 15, 2005

I have date coming to one page as a string in the following format"May 4 2005 12:00AM"
I need to query one of my tables using this date in combination of other nondate values. How can I convert this date into valid sql server datetime format before I query a database tables
Please help
 

View 3 Replies View Related

Passing Value To A Variable

Aug 9, 2007



Hi,
This is pretty simple but I am doing somethin really stupid somewhere, I am trying to pass a value to a variable declared in a package using some SQL code, I declared a variable "var1" and assigned the scope to the package , then I dropped a execute SQL task within the package and wrote a simple sql code that would get the maximum id from a table, "select max(id) from table" , now I want to assign the variable this max value.so in the parameter mapping tab I select the variable give it a parameter name of 0 and size of -1 along with direction of "output". everythin looks simple but when I try to use this variable value in my next execute sql task it behaves weirdly, I am trying to update anothet table using this variable value, so I add another task and put this code in there " update table2 set id = ? " , and on the parameter mapping tab I set the direction as input, however when I look at the column it updated th column with a weird number , xomethin like "230072408" which doesnt mean anything. I know that tha max value of ID is 10.
Please help.

Thanks

View 13 Replies View Related

Passing Variable To SqlCommand

May 18, 2007

 Can't seem to pass a variable to the sql statement. I'd appreciate any help. I'm trying to pass pColName to  CommandText = "ALTER TABLE tb_roomInfo ADD @rColName  varchar(50);";Doesn't seem to work though. CODE:  [WebMethod]    public string addCol(string pColName)    {                    SqlConnection cnn = new SqlConnection(connString);        try        {                        cnn.Open();            SqlCommand cmd = new SqlCommand();            cmd.Connection = cnn;                       cmd.CommandText = "ALTER TABLE tb_roomInfo ADD @rColName  varchar(50);";            SqlParameter rColName = new SqlParameter("@rColName", pColName);            cmd.Parameters.Add(rColName);            int i = cmd.ExecuteNonQuery();            cnn.Close();            return "Insert Successful";        }        catch        {                        return "Insert Unsuccessful";        }    }

View 3 Replies View Related

SQL WHERE Clause Passing In Variable

Jun 14, 2007

Hi All, Would somebody be able to help me from pulling my hair out!??I have a form with a radiobuttonlist. I would like to change my select statement depending on what radiobutton value is selected.E.g.SELECT * FROM table WHERE <<variable from radiobuttonlist>> LIKE 'Y'So,if radiobutton value selected = 1, it will select * from column A  in the dbif radiobutton value select = 2, it will select from column B in the dband so on...  Am i attempting to do the impossible?Thanks All, 

View 4 Replies View Related

Passing Datetime Variable To A SP

Jan 18, 2008

Hello,
I have a SP that recevies a date value for a users date of birth called "dob".
However when passing it into the class which contains the Stored procedure it gives an error.
 Below is my code
please advice
Thanks
Ehi
 
  1
2 command.Parameters.Add(new SqlParameter("@usernames", SqlDbType.Int, 0, "RegionID"));
3 command.Parameters.Add(new SqlParameter("@username", SqlDbType.VarChar, 20, "username"));
4 command.Parameters.Add(new SqlParameter("@First_Name", SqlDbType.VarChar, 50, "First_Name"));
5 command.Parameters.Add(new SqlParameter("@Last_Name", SqlDbType.VarChar, 50, "Last_Name"));
6 command.Parameters.Add(new SqlParameter("@dob", SqlDbType.Date, 50, "dob"));
7
8 command.Parameters[0].Value = 4;
9 command.Parameters[1].Value = "username";
10 command.Parameters[2].Value = "First_Name";
11 command.Parameters[3].Value = "Last_Name";
12 command.Parameters[4].Value = DateTime.Parse(dob.Text);
 

HERE IS THE ERROR MESSAGE 
 Compiler Error Message: CS0103: The name 'dob' does not exist in the current context

Source Error:



Line 40: command.Parameters[2].Value = "First_Name";
Line 41: command.Parameters[3].Value = "Last_Name";
Line 42: command.Parameters[4].Value = DateTime.Parse(dob.Text);
Line 43:
Line 44: int i = command.ExecuteNonQuery();


Source File: c:inetpubwwwrootcellulant1App_Codesignup_data-entry.cs Line: 42  

View 7 Replies View Related

Passing Value To A Variable From Database

Apr 25, 2008

I am trying to get data from a database through a select statment and I want to pass the return query to a c# string variable. Any idea how can I do this?
 

View 2 Replies View Related

Passing Variable To Sql Statement

Apr 28, 2008

could anyone please help me to resolve this issue?
here's my sql query which retrieve last 3 month data
t.execute(SELECT * tbl1 where nmonth >= datepart(mm,DATEADD(month, -3, getdate())) or nmonth <=datepart(mm,getdate()) and empno='"+emppip+"'")
now instead of passing 3 in this query(datepart(mm,DATEADD(month, -3, getdate())) )
i need to pass a variable to retrieve data based on user requirements.
i tried this way,
dim mno as n
mno=4
t.execute(SELECT * tbl1 where nmonth >= datepart(mm,DATEADD(month, -'"+mno+"', getdate())) or nmonth <=datepart(mm,getdate()) and empno='"+emppip+"'")
its not working.
can i achieve this using stored procedure? or can i directly pass a variable to sql synatax?
thanks for any help   

View 8 Replies View Related

Passing A String Variable To Sql

May 11, 2008

hi there,
i am trying to pass a string which contains a string,
here is the code which is wrong :
{string sqlcommand = "select pnia.pnia_number, pnia.user_name, pnia.date_pnia, pnia.user_pnia, problem.problem, gormim.gorem_name, status.status_name from pnia,gormim,problem,status where (pnia.status='@p1' and status.status='@p1' and pnia.problem=problem.problem_num and pnia.gorem=gormim.gorem)";
OleDbCommand cmd = new OleDbCommand(sqlcommand,con);OleDbParameter p1 = new OleDbParameter("@p1",this.DropDownList4.SelectedItem.Value.ToString());
cmd.Parameters.Add(p1);
}
the problem is that the sql compailer doesnt take the parameter (@p1) as a string
if someone could help me with that it would be great !  tnx

View 2 Replies View Related

Passing A Query Into A Variable

Aug 9, 2007

What is the easiest way to pass a value into a variable...
TSQL...

declare @@test int

--select count(*) from authors
select count(*) into @@test from authors

View 1 Replies View Related

Syntax For Passing Variable-usp

May 20, 2007

hi guys what is the syntax for using the passing variable part into the name of a table in a store procedure. in particular: (assume already declared the variable periodseq.


select *
into Temp_Usage_@periodseq
from Master_usage
where Master_usage.PERIODSEQ = @periodseq


in particular the Temp_Usage_@periodseq line of code, how do i "add" the periodseq (which is a number) to the end of the name of Temp_Usage, i.e: Temp_Usage_112
is the syntax an & like Temp_Usage_&@periodseq?
Cheers
Champinco

View 1 Replies View Related

Passing A Variable To A SQL Statement

Mar 10, 2008

I've been coding a few years and SSIS makes me feel more stupid than any program I've ever used.
I've read BOL and bought a book. Can't say either one has really helped.

I'm still a complete idiot after one week of working with it. I apologize for asking so many stupid questions.

What I'm trying to do now is parameterize a SQL statement.

I have a variable that's a string. I have a DataFlowComponent as a data source. I find references all over the Internet and this forum to something called an "ExecuteSQLTask" but I sure can't figure out what that is. In my toolbox I have data flow sources for DataReader, Excel, Flat Files, Old DB, Raw file, and XML but no Execute Sql Task.

Anyway SELECT * FROM TABLE WHERE COLUMN='Value' in the SQL Command property is simple enough. Now I want 'Value' to be a variable. You know, like in T/SQL DECLARE @Foo VarChar(25). Creating the variable is easy as pie.

I have found at least 10 different examples of specifying variables on the web, all of which claim to be SSIS examples, Is it User::Variable? @[User::Variable]? @Variable?

I want to read rows from a table, with a a variable value in the WHERE clause, and pass them to the fuzzy lookup task.

Is my approach fundamentally flawed in some way?





View 25 Replies View Related

Passing A Variable To SQL QUERY

Oct 24, 2007



Hi
I am extracting data from Oracle via SSIS. There are three smilar schemas from where the data has to be extracted.
Say My query is " Select * from abc.dept" where abc is the schema name. I want to pass this schema name through a variable and it should loop as there are total 3 schemas. There is a table which provided list of schemas.
Can somebody please guide me how to do this. There are multiple references of this table is SSIS package.

View 11 Replies View Related

Passing Variable On To Next Record

Jan 28, 2008

I am running a query in with my daily import job that lets me know if a duplicate record was imported into the system. If so I want to setup my email table to mail me the following information. My code works fine except the value of the variable @ID is only giving me the first record in the set. How do I pass it on to the next record? I get the right number of rows inserted into the email table but the @ID variable is the same for all 3.

Drop Table #SER1

Select id#,

Colx=Count(*)

Into #SER1

From Business

Group By id#

Having Count(id#) > 1


Declare @ID Varchar (4)


Select @ID = ID# from #SER1


INSERT INTO Email

( [To],

[Subject],

[message],

[Table],

TableUKey,

TableSource

)

SELECT

'Firstname.Lastname@Company.com',

'Duplicate ID# Imported',

'ID# ' + @ID + ' has been imported multiple times ' + ' on ' + cast( getdate() AS varchar(30)) + '. You need to remove the record and rerun the import.',

'Daily Import',

@ID, 'Daily Import'

FROM #SER1

View 1 Replies View Related

Passing Parameter Value To Variable

Jun 1, 2007

Hello,

I can't figure out how to pass values from report parameters to a variable in custom code.
I have a Report Parameter called Parm1 of type string. It contains the following:

Label: Value:
Labell1 Value1
Label2 Value2

In custom code I have declared a variable thus:
Public Dim ParmValue As string = Parameters!Parm1.Value

What I want is this: When a user select a report, they will have to choose a parameter from a dropdown-list (created automatically when creating a paramete). The chosen parameter will be passed to the variable ParmValue which I use as one of the parametes in a text-box like this:
=code.InstanceName.MethodName(ParmValue)

InstanceName is the instance of a class from a dll I reference.
It works fine if I hard-code the paramter like this:
Public Dim ParmValue As string = "SomeString" instead of:
Public Dim ParmValue As string = Parameters!Parm1.Value

The error I get is this:
[rsCompilerErrorInCode] There is an error on line 0 of custom code: [BC30469] Reference to a non-shared member requires an object reference.

I don't know if I'm at all on the right track here.
Can someone tell me what I'm doing wrong?

Thanks
/Peter

View 1 Replies View Related

Passing Batch Variable

Feb 8, 2008

Hi:

I'm not an expert with using batch files, or using them with SSIS, but I am trying to use two batch files in my SSIS package. I want the first one to map the first available drive on the server and pass that drive letter as an output variable to the package, and then to pass this variable to the last batch file and unmap.

I already have execute program tasks set up with a hardcoded drive letter, but that is not going to work as I don't know what drive letters will be mapped on this machine.

I did find sample code for using variables in a batch file that maps drives:




Code Snippet
@ECHO OFF

rem Map \serverfolder to the next available drive letter and save
the driveletter as DRIVELETTER
FOR /F "tokens=2" %%A IN ('net use * \serverfolder) DO IF NOT %
%A.==command. SET DRIVELETTER=%%A

rem Unmap DRIVELETTER
net use %DRIVELETTER% /del

rem Clear your env variables
set DRIVELETTER=





Questions:
1. Is this the type of code I want to use with the SSIS package?
2. How do I use the batch file with SSIS? I see the Execute Process Tasks has a "StandardInputVariable" and "StandardOutputVariable". Will these work?

Thanks,
Kayda

View 1 Replies View Related

Is There A Way To Loop Over Report Parameters, And Format Them Before Passing Them Onto The Query

Nov 13, 2007

Good morning all,


I have a report which is getting its parameters from an ASP.net page. My ASP developer wants to send in simple values, such as the list 1,2,3,4 for a parameter. However my report needs that list to look like [CD RSRC].[RSRC].&[1], [CD RSRC].[RSRC].&[2], [CD RSRC].[RSRC].&[3], [CD RSRC].[RSRC].&[4].


Is there any way, on the report services side, to capture an incoming report parameter, parse it, loop over the parsed values and format them?


I don't think there is, but I wanted to check before I go back to the developer and tell him he has to send in tuple lists.


Thanks,
Kathryn

View 1 Replies View Related

Passing A Linked Server As A Variable?

May 25, 2006

Anyone got any Ideas on how to set tax...TXZip as a variable in the following script segment?



Select
RecordID = Identity(int,1,1),
Zip_ZipCode,
Zip_SignatureCode
Into #Temp
from tempstatezip
where State = @State

Delete From tax...TXZIP ------------------------------

While (Select Count(*) from #temp)>0
Begin

Set @rowVal = (Select top 1 RecordID from #Temp)


Id like to set this up in a store procedure so I can pass the state variable to it.....

Having problems setting a linked server as a variable thou.....
Example of what I want.....
Delete from @StateVar
But as a linked server I always get...

declare @StateVar Varchar(11)
Set @StateVar = 'Tax...TXZip'
Select * from @StateVar

Msg 137, Level 15, State 2, Line 3
Must declare the variable '@StateVar'.

Any Ideas?

View 2 Replies View Related

Passing A Column/field Name As A Variable?

Aug 24, 2005

Is it possible to use a table's fieldname as an SQL variable?

i.e. can the below be made to somehow work:


Code:

SELECT Firstname, Surname, myVariable
FROM ContactDetails
WHERE myVariable = [user input];



- or simply -


Code:

SELECT Firstname, Surname, [user input]
FROM ContactDetails;



---
The "user input" being any other chosen column/fieldname from the ContactDetails table (e.g. Street, City, Postcode, etc.).

i'm using Access and ASP - in case that makes a difference.

any help would be greatly appreciated.

View 1 Replies View Related

Passing A Variable To Aggregate Function

Aug 29, 2013

I have cursor that loops through a table (the table only contains columnnames of several tables) the cursor has a variable declared @columnname. when i run the following it works fine

select @columnname,0,0,0,0
from temp_prt

it gives me my expected output

mtr_5120,0,0,0,0
mtr_3247,0,0,0,0
mtr_5160,0,0,0,0
etc........

now i want to get the min of each column name like so

select @columnname,min(mtr_5120),0,0,0
from temp_prt ------> this works for min(mtr_5120)
mtr_5120,34.5,0,0,0

now I want to generalize so I try to pass in the variable name and I do the following

select @columnname,min(@columnname),0,0,0
from temp_prt
(the columname (@columnname) exists in the table temp_prt)

but now i get an error
Msg 8114, Level 16, State 5, Line 29

Error converting data type varchar to decimal.how can i pass the colunmame into the min and max functions or is that at all ppossible. I also tried the following:

select @columnname,'min(' + @columnname + ')',0,0,0
from temp_prt

but i get the same error
Msg 8114, Level 16, State 5, Line 29
Error converting data type varchar to decimal.

View 2 Replies View Related

Passing Table Object Into A Variable

Jul 5, 2007

Good Day guys, sorry i'm just new in SQL Server.

My problem is like this:

I want to pass Table Object to a declared variable and make View.

Usually we make like this:

"Select * from Table1"

I want like this:

"Select * from @Table1"

I want to pass Table1 to a variable "@Table1" which the variable
reads as Table.

Please help me guys, i really appriciated everything.

Thanks,

Ero-Sennin26

View 1 Replies View Related

Passing DatePart Variable To DateAdd()

May 14, 2008

When I call DateAdd(), I would like to pass a variable for the first parameter.

I wrote a little script that illustrates the problem, as well as the desired outcome I'd like to achive.

-- 1. Declare a "@DatePart" variable

DECLARE @DatePart VarChar(5)


-- 2. Assign a value to the "@DatePart" variable.

--

-- In this case, DatePart = 'month',

-- But we may choose day, week, or year.

--

-- SELECT @DatePart = 'day'

-- SELECT @DatePart = 'week'

SELECT @DatePart = 'month'

-- SELECT @DatePart = 'year'


-- 3. Attempt to add 1 month to today's date.

--

-- Depending on the value of "@DatePart",

-- add 1 day, week, month, or year to today's date

--

-- 3.1 This throws "Invalid parameter 1 specified for dateadd."

select DateAdd(@DatePart, 1, GetDate())


-- 3.2 This addes one month to today's date (as I hoped 3.1 would do)

select DateAdd(month, 1, GetDate())

View 5 Replies View Related

Loop By Passing Variable Into Query

Nov 2, 2007



Hi all,

I'm have created a data flow that uses an OLEDB source with a SQL Query. In the WHERE statement of this query is a condition for the storecode. I want to figure out how to create a loop that will cycle through a list of storecodes using a variable which is passed to the dataflow in turn to the OLEDB source's query and runs through the process for each store.

The reason i'm using a loop is because there are about 15 million records that are merge joined with 15 million others which is causing a huge performance problem. I'm hoping that by looping the process with one store at a time it should be faster. Any ideas would be greatly appreciated.

View 3 Replies View Related

Passing Parameter To SQL Task Variable

Sep 25, 2006

I am trying to exectue SQL task as below by passing a parameter

If I try....

@v1 datetime

set @v1 = convert(datetime, ? ,103)

it fails with below error

" failed with the following error: "Syntax error or access violation". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

however the below code works well

delete from t1 where last_update = convert(datetime, ? ,103)

What could be the problem?

View 4 Replies View Related







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