Changing Year In Field

Jan 15, 2007

Hi all:
I need to change the year to many records that, by mistake, were put with a 2007 date, but they should be still with 2006, can anyone help me ?

I use Sql Server 2000 and the field is of type datetime

Thanks:eek:

View 1 Replies


ADVERTISEMENT

Making The Year Field 2007 To A 07 Instead?

Aug 13, 2007

Hi

In my matrix cell I have a cell with the following Expression:

=Fields!monthname.Value & " " & Fields!Year.Value



In one of my datasets I have a SELECT CAST(FCYB.cyb_yearname AS int) AS 'Year' ... amongst other things.



Now, how do I make 'Year' to say 07 instead of 2007? What is the proper way to do it? In the expression or in the SQL code?



Thanks in advanced as always

KR

View 3 Replies View Related

Char(4) Or Integer For Year / Quarter Field

Sep 3, 2007

Hello,

what's best for year resp. quarter Field, char(4) resp. char(1), integer or other?

Both are part of a composite index.

Thanks
Silas

View 6 Replies View Related

Select Rows With Datetime Field By Year

Jan 21, 2008

Hi,

I have rows with a datetime column which spans many years. I am wondering is there a more simple way to select those rows where the datetime column is in a particular year. For example...
where datetime_column.year = '2007'

I know i can use the 'between' sql etc...but am wondering if theres something more straightforward im not aware of.

Thanks..

View 4 Replies View Related

T-SQL (SS2K8) :: Return Value In A Status Field Which Has Latest Year And Month

May 11, 2015

I have table in which month & year are stored, Like this

Month Year
10 2014
11 2014
12 2014
1 2015
2 2015
3 2015
4 2015

I wanted a query in which it should return the value in a status field which has latest year & month.

View 9 Replies View Related

Very Simple Question Regarding A DATETIME Field; Select Fields Matching Month/day/year

Jan 12, 2006

Hello All,I've got a DATETIME field, and it includes hour:minutes:second data.  I want to do selects where I can simply match on the month, day and year.  For instance, something like this:SELECT * FROM QuizAttempts WHERE DateTimeTaken = '1/12/2006'And have it match anything that was taken that day, regardless of *when* it was taken.  Any suggestions?Thanks!  -Josh

View 2 Replies View Related

Changing Value Of A Field

Jun 5, 2006

Bob writes "I want to change the value of a field but it is part of a
unique index. Can someone help me do this.
I need to update about 55000 rows.

thank you very much."

View 2 Replies View Related

Changing A Field Value In SRS

Jan 27, 2008

Hello,

I have a field on my SRS Report called directioncode.
This field gives me a value of 1 or 0.

How do I fix this code so
if the value of directioncode is 0, the text "Incoming" is displayed
if the value of directioncode is 1, the text "Outgoing" is displayed?

Current value in the expression area of my field....
=Fields!directioncode.Value

View 5 Replies View Related

Changing Field Names

Jan 27, 2008

This is a followup to my last post. If a field name is changed in the database, what is the easiest way to determine what stored procedures and triggers that reference that field are now broken?

View 3 Replies View Related

Changing Populated Field Types

Mar 30, 1999

How can I change a field type whilst it is populated?

I have tried :

insert new_table
select *
from old_table

but I get :

Disallowed implicit conversion from datatype 'text' to datatype 'varchar'
Table: 'davy.dbo.new_table', Column: 'de_area'
Use the CONVERT function to run this query.

My table formats are as follows :

TABLE dbo.new_table (
de_pk int NOT NULL ,
de_name char (25) NOT NULL ,
de_area char(255) NULL
)

TABLE dbo.new_table (
de_pk int NOT NULL ,
de_name char (25) NOT NULL ,
de_area text(16) NULL
)

View 1 Replies View Related

Changing/reordering Text In A Field

Jan 3, 2005

I have a table with approx 1200 names in the format of "John Doe" I need to edit the names to the format of "Doe, John"

Any easy way in SQL to do this or am I better off dumping to text file, making the changes in another app and then updating the table?

View 3 Replies View Related

Changing Field Name Of An Existing Table

Jan 17, 2006

Hi,
Is it possible to change any fieldname of an existing table?I mean to say
by TSQL statement.We know that we can alter the data type and width etc.
But I haven't got any info about filedname change.So if it is possible
Please help...
And Is there any TSQL command to alter multiple columns in a single statement?

Please help...
Thanks!!
Joydeep

View 7 Replies View Related

Changing The Size Of A Varchar Field

Jul 7, 2006

We have a small table of about 13 million rows that needs altered. A column in the table needs to be changed from a varchar(20) to a varchar(500). When we ran the alter table script, 3 hrs later and it wasn't done running. Any suggestions on what we can do to speed up the process?


Thanks ahead of time
DMW


Edit:
We are running SQL Server 2000 and the db at the time was running in simple mood

View 1 Replies View Related

Changing Field For Unique Key Index

Oct 18, 2007

SQL 2000 Enterprise Manager won’t let me drop a unique key index and move it to another field. I get an error regarding ‘drop index not allowed because of unique key constraint enforcement.

Is there anyway to change fields for unique keys in SQL Enterprise Manager?

Thanks, Al

View 4 Replies View Related

Changing Field Values In A Stored Procedure

May 4, 2003

Here's the deal:

I import a flat file from a legacy system, and then convert it into a single table. That works simply enough.

Then I have a SP that querys that table using a parameter for an accountID. My business tier calls that SP and returns the results to the calling tier (my web application). Easy enough...

Now for the question. The people who created the flat file (written in COBOL) decided to use "codes" to represent data. So, for instance, if I'm looking for the account plan, I can expect to see characters like ], or [, or +, etc... These characters have a special meaning, like:

] = Plan A
[ = Plan B
+ = Plan C, and so on.

Currently, the web application displays those characters, but I want it to display the actual plan name. Is there a way that when I execute the SP, the SP could pull the necessary records, and whenever it encounters a certain "plan" character, it could convert it into a "readable" name? Say that it sees that the plan_type field has a value of "]" for twenty records, so it converts those twenty records' plan_type value from "]" into "Plan A"? I'm not sure if I can do that, but I want to at least evaluate the option if I can.

I've evaluated other options, like using a CASE statement in my code, but I shot that down quickly...for obvious reasons. I don't wanna be changing my web application or business tier each time these guys update a plan name, or add a new one, delete an existing one, etc...

I've also thought about creating a dictionary table than contains the plan's code and its name, and then just INNER JOIN the first table with the dict table. This would keep my SP very simple (it's very straight-forward right now, and I like that). That way, if a plan name is ever changed, or a new one is added, I simply update the dict table using a simple query. However, if my SP is doing the conversion, I could just as easily update the SP.

Either of these methods would work for me, and I *do* know how to do the latter (dict table). However, there are quite a few other fields that I may have to do this for. I believe when I left for the day on Friday, my last count was 14 fields total that needed translation. That would mean 14 different dict tables! That could certainly affect my SP performance with all those INNER JOINS!

Therefore, I'm certainly interested in figuring out if it's possible to do the former method (SP), and then I shall decide which method is best for my situation.

Feel free to include your thoughts on which process you think is better as well. I'm really riding the fence with this one. However, if I can't find out how to change field values in my SP, then obviously I'll make a decision very quickly...

Thanks in advance.

View 3 Replies View Related

Changing Field Lenght Via Query Analyzer

Jul 17, 2004

Hi
My DB is on the web so but I want change Field Lenght of one of my Fileds on my DB

its 70 rights now I want change it to 120 its a nvarchar


how can I do it , I am using a ASP page


Thanks

View 1 Replies View Related

Changing Field Names Is SQL Server 2000

May 23, 2007

Why is it that when you change a field name is SQL Server it sometimes completely messes things up. I renamed a field in one of my tables from Emp# to EmpNumber. I had a view based on this table and naturally I knew I would have to change a view I had based on the table. I opened the view and changed the field from Emp# to EmpNumber but when I tried to open the view I got an error “Invalid column Name EMP#”. I have not been able to fix this. I have dropped and recreated the view, refreshed all the objects using enterprise manager, refreshed all the objects using Query Analyzer, shut down and restarted my computer, taken my database offline and put it back on line. The field name EMP# is not in any tables in my database and not referenced any views or procs . I am just starting on this database so I could verify this very easily because I only have a few views and procs.

Has anyone had this problem or more importantly does anyone know why this is happening or how to fix it?

View 9 Replies View Related

Changing Field Length Whilst Replicating

Jan 6, 2004

Hi

I have two databases that are merged using replication, and I want to change the length of one of the fields. Can anyone think of a way of doing it that doesn't require dropping the whole publication and rebuilding it? Thanks Ed

View 2 Replies View Related

Changing One Field Updates 3 Other Fields, Use A Join?

May 10, 2004

i currently have a table like this..


user_id username app_id app2_id app3_id app4_id
1 john 3 4 5 6
2 mike 4 5 6 6
3 manager 4 5 6 6
4 vicepres 5 6 6 6
5 ceo 6 6 6 6
6 board 6 6 6 6


the basic pattern is... a user has approvers, and those approvers have approvers as well... i have 4 columns of approvers.. and if my first approver is the manager, then my second approver will be the managers approver and my third approver will be the managers approver's approver and so on..

on my actual page, i have select fields for the app, app2, app3, app4 and i need it so that when i change the very first app, it'll automatically update app2, app3, and app4

any ideas how i would do this? im pretty new to sql but im thinking i would use some type of join?

thanks

View 7 Replies View Related

Dynamically Changing The Length Of A Varchar(n) Field

Dec 26, 2006

Hi Everyone,I have a question about dynamically changing the length of a varchar(n)field, in case the value I'm trying to insert is too big and will givea "truncated" error, but before the error is given! i.e. Is there somekind of a way to "test" the length of the field while Inserting thevalue into it, and to have it automatically increase its length to thelength of the value being inserted, in case the value is too big?I've been able to do this in a "primitive" way, simply by identifyingthe specific error number in case the value is being truncated, andthen increasing the length of the varchar(n) field by using the ALTERcommand, and then duplicating the insert statement, but is there astandard (shorter) way of doing this?Here is my code (I'm working in an ASP environment):<%var_txt = "abcdefghijklmnopqrstuvwxyz12345678789"sql = "Insert Into Table1 (text) Values ('" & var_txt & "')"On Error Resume Nextconn.Execute sqlIf err = -2147217833 ThenResponse.Write "Error Recognized Successfully!<br /><br />"sql = "ALTER TABLE Table1 ALTER COLUMN text VARCHAR(" &Len(var_txt) &") NOT NULL"On Error Resume Nextconn.Execute sqlIf err<>0 ThenResponse.Write "Error while trying to alter Column:<br/>" & err & "= " & err.description & "<br />"ElseResponse.Write "Column altered successfully to: " &Len(var_txt) &"<br />"sql = "Insert Into Table1 (text) Values ('" & var_txt &"')"On Error Resume Nextconn.Execute sqlIf err<>0 ThenResponse.Write "<br />Error number 2:<br />" &err.description &"<br />"ElseResponse.Write "Now it was added successfully!HaHa!<br />"End IfEnd IfElseResponse.Write "Success."End If%>Thanks in advance!

View 2 Replies View Related

SQL Security :: Virus Changing Values In Field

Sep 18, 2015

I've got this virus URL... in our production server. It is changing the values in a table column.

View 6 Replies View Related

Changing A Field's Value When User Updates Data

Sep 10, 2007

Hello,
I am working on a project that involves one part where a field's value needs to be changed when the user updates the record. Here is the situation in detail:
There is an InputData table where the user enters new records or changes existing records. There is a field called "calculated" in this table which has a default value of 'no'. A stored procedure runs math calculations on all the InputData records where the calculated field = 'no'. At the end of this stored procedure, it sets the calculated field = 'yes'. When new records are added by the user their "calculated" field value is 'no' by default so that the next time the stored procedure is executed, it only runs the math calculations on the new records. The problem is, if a user changes an existing record, the "calculated" field needs to be changed from 'yes' to 'no' so that the stored procedure recalculates the math for the modified record. How do I change the value from 'yes' to 'no' on records that the user modifies?
Thanks.

View 9 Replies View Related

Changing Field In A Stored Procedure To Match Name In A Form

May 15, 2008

If I have a column named "Login" in a SQL Table (I am sharing with another application) that I am using a stored procedure to acquire the information from, how can I trranspose its name to match code already written in a Web App to get the data.
 There is a web app already created that has the followig code to get the data from the database
Dim strSQL ast string = "UsersSelectCommand"
intLoginID = objDataReader("LoginID")
 
My stored procedure is the following:
 CREATE PROCEDURE UsersSelectCommand/* (  @parameter1 datatype = default value,  @parameter2 datatype OUTPUT )*/AS Select Lastname, FirstName, Login from Users Order by LastName
GO
 The stored procedure will return "Login" instead of "LoginID" that I am wanting. How can I modify the Stored Procedure to change the LoginID to Login.
 
 
 

View 2 Replies View Related

Integration Services :: Error While Changing Field Size

Sep 10, 2015

I am loading from SQL Server 2008 to Access 2010 using SSIS. One of the columns in the table I am loading into is a Number datatype and Fieldsize is long integer. The values are being truncated, so I want to change the Fieldsize to DOUBLE.  However, when I do that I receive the error below. What should I do? I would like not to change my Windows registry.

This error can be caused by one of the following:

The maximum number of columns allowed in a table or the maximum number of locks for a single file is exceeded.

The indexed property of a field is changed from Yes (Duplicates OK) to Yes (No Duplicates) when duplicate data exists in the table.

An expression is not specified in the Expression property of a calculated field. 

If the maximum number of locks per file was exceeded, you can increase the number by editing a registry entry. However, this is not a recommended option.

If you use Registry Editor incorrectly, you could cause serious problems that require you to reinstall the operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.

Make a backup of the registry. Find the MaxLocksPerFile registry value by using the Windows Registry Editor, and then increase the value. The MaxLocksPerFile value is saved as part of the following key:

HKEY_LOCAL_MACHINESOFTWAREMicrosoftOffice14.0Access Connectivity EngineEnginesACE

If the Indexed property of a field and duplicate data is located in the table, reset the Indexed property to the previous setting, or remove duplicate records from the table.

While I was loading to the same table a few days ago, I received a warning and the task took approx 9 hours. I am attaching the screen shot.

View 8 Replies View Related

Inserting Values Into SQL CE 3.5 Ntext Field Is Changing All Rows...

Apr 25, 2008



Has anyone seen this issue before? We are running a SQL CE 3.5 database on a windows desktop. A couple of our tables have ntext fields. When we do an insert the statement updates the value for all rows, not just the one that was added. I can easily repro this with some of the online samples too. Try the following:


SqlCeConnection conn = new SqlCeConnection(_sConn);

conn.Open();

SqlCeCommand cmd = conn.CreateCommand();

cmd.CommandText = "CREATE TABLE BlobTable(name nvarchar(128), blob ntext);";

cmd.ExecuteNonQuery();

cmd.CommandText = "INSERT INTO BlobTable(name, blob) VALUES (@name, @blob);";

SqlCeParameter paramName = cmd.Parameters.Add("name", SqlDbType.NVarChar, 128);

SqlCeParameter paramBlob = cmd.Parameters.Add("blob", SqlDbType.NText);

paramName.Value = "Name1";

paramBlob.Value = "Name1 Memo";

cmd.ExecuteNonQuery();


cmd.CommandText = "INSERT INTO BlobTable(name, blob) VALUES (@name, @blob);";

SqlCeParameter paramName = cmd.Parameters.Add("name", SqlDbType.NVarChar, 128);

SqlCeParameter paramBlob = cmd.Parameters.Add("blob", SqlDbType.NText);

paramName.Value = "Name2";

paramBlob.Value = "Name2 Memo";

cmd.ExecuteNonQuery();



After the second execution the blob column in both rows will have the value 'Name2 Memo'.

This is obviously a huge problem for us and would appreciate it if someone can explain what is happening. Seems like a bug but would like to be certain before I go the support route.


View 1 Replies View Related

Fiscal Year Totals - Calculating Sales By Month And Current Year

Sep 18, 2013

I have the following script that calculates Sales by month and current year.

We run a Fiscal year from April 1st thru March 31st.

So April 2012 sales are considered Fiscal Year 2013.

Is there a way I can alter this script to get Fiscal Year Totals?

select ClassificationId, YEAR(inv_dt) as Year, cus_no,
isnull(sum(case when month(inv_dt) = 4 then salesamt end),0) as 'Apr',
isnull(sum(case when month(inv_dt) = 5 then salesamt end),0) as 'May',
isnull(sum(case when month(inv_dt) = 6 then salesamt end),0) as 'Jun',
isnull(sum(case when month(inv_dt) = 7 then salesamt end),0) as 'Jul',

[Code] ....

Data returned looks like the following.

ClassificationID Year Cus_no Apr May June ....
100 2012 100 $23 $30 $400
100 2013 100 $40 $45 $600

What I would need is anything greater than or equal to April to show in the next years row.

View 2 Replies View Related

Reporting Services :: Calculate Sales Percentage Difference Between Selected Year And Previous Year In A Matrix

Mar 27, 2015

I'm trying to generate a report using matrix like this

                                                      Month
Product     PreviousYearSalesAmount    SelectedYearSalesAmount      %SalesDifference

I can populate year sales amount, but i cant calculate the percentage.

Note: Month and Year are passed as parameters.

View 5 Replies View Related

To Send The Date Format If The User Has Specified Only Month And Year, Or Only The Year

Aug 30, 2004

I have three web form controls, a ddl that contains the day, another ddl that contains the month and a textbox that contains the current year. To send the date chosen by the user to the database, I join the three web form control values so that the resultant string is ‘day/month/year’ thus:

CmdInsert.Parameters("@Date").Value = day.SelectedItem.Value + "/" + month.SelectedItem.Value + "/" + year.Text()

And the resultant string is: dd/mm/yyyy, for example 30/08/2004.
But the problem is if the user does not select any day or any day and month, then the resultant string is for example; 00/08/2004 or 00/00/2004, but the problem is the database does not accept this format as datetime. How can I do it?

I want the user has the possibility to chose as well only the month and year, and as well only the year. Is it possible to send to the database the datetime format with only the month and year, or only the year?

Thank you,
Cesar

View 4 Replies View Related

SQL Server 2012 :: How To Get This Year And Last Year Totals In Two Separate Columns

Jun 19, 2014

I have two queries that give me the total sales amount for the current year, and the last year.

SELECT SUM([Sales (LCY)])
FROM [$Cust_ Ledger Entry] cle
LEFT OUTER JOIN dw.dim.FiscalDate fd
ON fd.CalendarDate = cle.[Posting Date]
WHERE [Customer No_] = '10135'
AND fd.CalendarYear = '2013'

[Code] ....

I would like to learn how to be able to make this a single query and end up with two columns and their summed up totals. Like it shows on the attached image.

This is my query without the columns I need:

SELECT
c.CustomerNumber
,c.Name
,c.ChainName
,c.PaymentTermsCode
,cle.CreditLimit AS 'CreditLimit'
,SUM(cle.Amount) AS 'Amount'

[Code] ....

View 1 Replies View Related

Select Month/year When Range Spans Year

Feb 25, 2004

I'm using PHP with SQLServer2k to create a page containing monthly counts of episodes at a facility occurring between two user selected month/year combinations. For instance, the user could select 10/2003 and 2/2004 and facility X and get a line for each month showing the count of episodes occuring in that month.

The problem is that the episode date is stored in three integer fields (epiday, epimonth, epiyear) and I'm having a terrible time getting them into a format where I can use them in a between statement.

I've tried evaluating the parts of the episode date seperately like:


where
(epimonth>=10 and epiyear=2003)
or
(epimonth<=2 and epiyear=2004)


and that works, but what happens when someone wants to see from 10/2002 to 2/2004?

Any suggestions on the best way to do this?

View 5 Replies View Related

How To Find Year And Past Year Compare In Business

May 5, 2014

We have customers who are new to this year (2014) and there same customers in last year (2013). also there are customers we have not received business this year but only last year. so there are 4 conditions.

1) New customer (2014) - Customer(B)
2) Old Regular customer (2013 and 2014) - Customer(A)
3) Last Year (Lost) customer (2013) - Customer(C), no business received in year(2014)

For example we have a transaction table:

TransactionId, ReceivedDate, Customer
1, 2-Dec-2013, A
2, 3-Jan-2014, A
3, 2-Mar-2014, B
4, 25-Nov-2013, C

I want results like

Customer, Business (this year activity/last year activity)
A, 1/1
B, 1/0
C, 0/1

How can i show this for each year? I used to separate it month wise as below but it does not return applying year with each customer anyhow...

select t.customerId, YEAR(Receiveddate),
sum(case month(ReceivedDate) when 1 then 1 else 0 end )as Jan,
sum(case month(ReceivedDate) when 2 then 1 else 0 end )as Feb,
sum(case month(ReceivedDate) when 3 then 1 else 0 end )as Mar,
sum(case month(ReceivedDate) when 4 then 1 else 0 end )as Apr,

[Code] ....

View 2 Replies View Related

Compare Given Period In Current Year / Previous Year

Mar 15, 2006

HiI want to write a function that can return a sum for a given daterange. The same function should be able to return the sum for the sameperiod year before.Let me give an example:The Table LedgerTrans consist among other of the follwing fieldsAccountNum (Varchar)TransdateAmountMST (Real)The sample data could be1111, 01-01-2005, 100 USD1111, 18-01-2005, 125 USD1111, 15-03-2005, 50 USD1111,27-06-2005, 500 USD1111,02-01-2006, 250 USD1111,23-02-2006,12 USDIf the current day is 16. march 2006 I would like to have a functionwhich called twice could retrive the values.Previus period (for TransDate >= 01-01-2005 AND TransDate <=16-03-2005) = 275 USDCurrent period (for TransDate >= 01-01-2006 AND TransDate <=16-03-2006) = 262 USDThe function should be called with the AccountNum and current date(GetDate() ?) and f.ex. 0 or 1 for this year / previous year.How can I create a function that dynamically can do this ?I have tried f.ex. calling the function with@ThisYear as GetDate()SET @DateStart = datepart(d,0) + '-' + datepart(m,0) +'-'+datepart(y,@ThisYear)But the value for @dateStart is something like 12-07-1905 so thisdon't work.I Would appreciate any help on this.BR / Jan

View 3 Replies View Related

How To Do A Year-to-date SQL Query Where Year Commences In August?

Jul 20, 2005

Does anyone have an example of an SQL query which returns rows for theyear-to-date, but where the "year" commences on August 1st?e.g. select * from mytable where datefield > last august 1stTIA for any helpIsabel

View 2 Replies View Related







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