Date Selection Giving Default '1/1/1900' For NULL Values

Feb 9, 2005

'XXX_DTE' is character type which wont take NULL.

SELECT CONVERT(Datetime,XXX_DTE) FROM XXXX

I get result as : 1/1/1900

Why is it so.....

What I expect is '0000-00-00 00:00:00.000'

View 1 Replies


ADVERTISEMENT

Why Date Columns Default To 1900 After Inserting Dates In SQL Server Everywhere.?

Sep 19, 2006

I have created a sample Database for the school project,

After executing the query below, the Date column is supposed to have the dates I have entered before,

However the dates shown are 1900.

Any idea why is this happening?

I appreciate your help.

Thank you.
Query:
 
Drop table AccountReceivable
GO
--BEGIN TRANSACTION
Create table AccountReceivable
(
 AccountRecID int identity (1,1) not null,
 PatientID int not null,
 PresentCharges int default 0 not null,
 PaymentMade money default 0 not null,
 PreviousBalance money default 0 not null,
 BalanceDue money default 0 not null,
 LastPaymentDate datetime not null,
 PresentDate datetime default GetDate() not null
)
GO
ALTER TABLE AccountReceivable ADD CONSTRAINT
PK_AccountRecID Primary Key (AccountRecID)
GO
 
ALTER TABLE AccountReceivable ADD CONSTRAINT
FK_PatientID_PatientID FOREIGN KEY (PatientID) REFERENCES PATIENT (PatientID)
GO
--COMMIT
--query to find delinquent accounts
--DATEDIFF (d, LastPaymentDate, PresentDate)
 
--Populate the Accounts Table
DELETE AccountReceivable
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate )
                    VALUES (913235,451.34,50,0,401.34,4/7/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (918035,109,109,0,0,3/6/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                            VALUES (914235,279,89,0,190,5/9/2005,5/9/2005)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                            VALUES (914235,0,90,190,100,5/9/2005,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (912224,67.90,67.90,0,0,2/2/2006,DEFAULT)
GO
 
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (900814,678.32,78.32,0,600,4/6/2006,4/6/2006)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (900814,0,500,600,100,4/6/2006,4/16/2006)
 
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (900814,0,100,100,0,4/16/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (913010,203,0,100,303,2/6/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (913010,0,80,303,223,8/3/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (913230,1030.89,1030.89,0,0,4/16/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (918035,78,60,0,18,7/1/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (941235,902,502,0,400,8/15/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (941235,0,200,400,200,8/15/2006,DEFAULT)
 
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (952235,134,24,0,110,4/18/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (952235,0,20,110,90,4/18/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (921635,257.87,57.87,0,200,5/27/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (921635,0,20,200,180,6/27/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (915235,1204,200,0,1004,3/15/2006,DEFAULT)
 
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (915235,0,100,1004,904,4/27/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (900035,578,178,0,400,7/10/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (900035,0,100,400,300,7/19/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (913241,157,0,0,157,5/12/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (913241,0,57,157,100,5/16/2006,DEFAULT)
 
 
GO
 
--sample query

select PatientID,PresentCharges,LastPAymentDate,PresentDate from AccountReceivable

GO

--result

PatientID PresentCharges LastPaymentDate PresentDate

----------- -------------- ----------------------- -----------------------

913235 451 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297

918035 109 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297

914235 279 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000

914235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297

912224 67 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297

900814 678 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000

900814 0 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000

900814 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

913010 203 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

913010 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

913230 1030 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

918035 78 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

941235 902 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

941235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

952235 134 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

952235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

921635 257 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

921635 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

915235 1204 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

915235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

900035 578 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343

900035 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343

913241 157 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343

913241 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343

(24 row(s) affected)

 

View 6 Replies View Related

Reporting Services :: Set Default Values For Selection List Navigator For Data Source View

Jul 14, 2015

How to set default values for selection list navigator without using parametrized queries for data source view.

View 2 Replies View Related

Default Date When Not Null?

Apr 23, 2008

I have a table that has a date field that can't be null.  When a new record is created, if a date is not passed in, I want the default to be the current date.  How can I set that?  I tried Now, Today, GetDate, etc.  Diane 

View 2 Replies View Related

Null Datetime Field Contains 1-Jan-1900

Feb 10, 1999

I have defined fields in a table as datetime fields and allow nulls. SQL 6.5 is placing 1-Jan-1900 in all of these fields causing me to have to filter out these dates when retrieving a recordset.
How do I get rid of this?


thanks!

View 1 Replies View Related

Change NULL Values To Default In SELECT Statement

Dec 22, 2006

I have a stored procedure with a SELECT statement, that retrieves 1 row.
SELECT name FROM tblNames WHERE nameID = "1"
I want all the NULL values in that row to be change in some default values.
How do I do this?
 
 

View 4 Replies View Related

Quick Question... How To Use Default Values (after Allowing NULL)

Oct 17, 2005

Hello,

I have a BIT column which accepts NULL values.

What would be a good method to allow an INSERT (or UPDATE) statement to insert NULL into this column but then automatically change the NULL to 0 (zero). In other words, test for NULLs after INSERT (or UPDATE) and change the value to 0 (zero).

Not exactly sure how to do this with a Trigger. Also, what is that [Formula] option used for (column properties in the Table Design view)... and would this apply with my problem?

Thanks,

View 2 Replies View Related

Reading Default Values Instead Of NULL From A Flat File

Oct 22, 2007

Hi,

I have the following problem: I'm connected to flat file source and trying to read data that is later inserted in an MS Access database. Everything wokrs fine instead of one thing - when I have null values in the flat file, I want those NULL values to be inserted in the MS Access db, instead of that what happens is that I actually get the default values for a column type from the flat file and later insert that defalut value. For example if I have a null value in an four-bite-signed-integer column of the flat file, I get 0 as value.

I thouth of solution using a "Derived Column" transformation which can transform the zeros into nulls, but decided to check with you guys if there is a smarter thing to do (for example to edit the flat file source to read the NULLs correctly).

Any advice is appreciated! Many thanks

Ventsy

View 8 Replies View Related

Data Flow Task - Have Null Values Take Table Default

Jun 6, 2006

Hi,


I have a data transform from a flat-file to a SQL server database.
Some of the flat-file fields have NULL values. The SQL table I'm
importing into does not allow NULL values in any field, but each field
has a Default value specified.


I need to have it so that if a null value comes across in a field using
the data transform, it takes the table default on import. I could of
sworn I had this working a few days ago, but I get errors now that
state I'm violating table constraints. Has anyone done this before?


Thanks
Jeff

View 5 Replies View Related

Updating Database Date Field Results In Date Value Of 01/01/1900

Jun 18, 2007

Brand new to this, so please bear with me.I'm using the following code fragment to update a datetime field on a SQL Server 2005 database table:cmd.CommandText = "Update Projects Set EntryDate = " & Convert.ToDateTime(txtEntryDate.Text)cmd.ExecuteNonQuery()The result of the update operation is the the database field contains the value "1900-01-01 00:00:00:000".  This probably means that I passed nulls to SQL; however, I see a valid date in the txtEntryDate field on my web form (i.e., "06/18/2007").  I also did a "Response.write" to display the txtEntryDate and it looks Okay.Can someone tell me what I'm doing wrong?Thanks!Using Visual Web Developer 2005 Express.

View 1 Replies View Related

1/1/1900 Date

Oct 2, 2001

I insert an empty field from a form to a SQL 7.0 table which has a collumn with datetime type and allowing nulls, but I always get a default date 01/01/1900. I don't want this date, how do I get rid of it? Thanks

View 1 Replies View Related

Birth Date 1900-01-01 00:00:00.000

Apr 12, 2005

Hi,all,

How to check the default value set up for datetime field? The reason I am asking is my database

field "birthday" is datetime(8), the default value I set to '' already. Everytime my asp program
have empty birthday or invalid birthdate for this field, the system automaticaly set it to

1900-01-01 00:00:00.000

Do you know what happened?

Thanks!
Betty

View 3 Replies View Related

T-SQL (SS2K8) :: Date Column With 1900-01-01 Value

Dec 23, 2014

I have a date column with 1900-01-01 value, I am trying to get the min(date) without 1900-01-01.My problem here is: there are some records with both 1900-01-01 and real date. How do I get the MIN of the real date without getting 1900-01-01. Below is my query sample.

CASE WHEN MIN(CAST(PROJECTEDSTARTDATE AS DATE)) = '1900-01-01' THEN NULL ELSE MIN(CAST(PROJECTEDSTARTDATE AS DATE)) END as 'ProjectStartDate'

View 3 Replies View Related

Comparing Date With NULL Values

May 8, 2001

Hi , I need to compare two date fields in two different tables.One of the field is varchar(8) and other is dateime.When there is a date in one field and NULL in other field , how do I compare these two vales?

View 4 Replies View Related

Handling Null Date Values

Jun 30, 2000

I would like some suggestions on how to keep from displaying the value "1/1/1900" on my asp pages when my recordset field returns a Sql date value that is null

Thanks for any tips
Jim

View 1 Replies View Related

Null Date Values And Order By

Jul 20, 2005

How do I order a query by a date field ASC, but have any NULL valuesshow up last? i.e.7/1/20037/5/20037/10/2003<NULL><NULL>Any help will greatly be appreciated

View 3 Replies View Related

Handle Null Date Values

Feb 19, 2008



Hi,
In my Excel file, The Application date column contains empty for some rows. In SSIS I am using one Data Conversion to that Application Date column to change it as Date[dt_Date]. This data conversion is giving error Conversion failed. In Sqlserver table, I declare ApplicationDate column datatype as DateTime.
I want to keep those empty date values as Null in Sqlserver.
I tried the IMEX=1 property still it is not working. How to solve this error?

Thanks in advance.

View 1 Replies View Related

Configure SqldataSource With Null Date Values

Mar 20, 2008

I am attempting to create search parameters for a gridview control and I am experiencing a small issue. When I get to a date parameter I am unable to display null values.
I setup a sqldatasource and created the parameters below to handle the selections for minimum date required and the maximum date required for the date columns in the database. The problem is I do not know how to display null dates.
Is there a way to incorporate something into the search page to show null dataes? 
   
Sql Where Clause  1 WHERE (LSS_Requests.TypeCode = @TypeCode) AND (LSS_Requests.PersonNo LIKE '%' + @PersonNo + '%') AND
2 (LSS_Requests.TicketNo LIKE '%' + @TicketNo + '%') AND (LSS_Requests.Name LIKE '%' + @Name + '%') AND
3 (LSS_Requests.RequestName LIKE '%' + @RequestName + '%') AND (LSS_Requests.RequiredDate >= @Fromrequireddate) AND
4 (LSS_Requests.RequiredDate <= @ToRequiredDate) AND (LSS_Requests.OriginationDate >= @SearchFromOriginationDate) AND
5 (LSS_Requests.OriginationDate <= @SearchToOriginationDate) AND (LSS_Requests.LastUpdated >= @SearchFromUpdatedDate) AND
6 (LSS_Requests.LastUpdated <= @SearchUpdatedToDate) AND (LSS_Users_1.userFullName LIKE @SearchDDLUsers) AND
7 (LSS_Users.userFullName LIKE @SearchddlCIAsignee) AND (LSS_Requests.TypeCode = 'CC') AND (LSS_lu_Status.stNm LIKE '%' + @StatusName + '%')
  

View 2 Replies View Related

SQL Server Automatically Changing Date 1900-01-01 To 2001-01-01

May 16, 2008

I have a SSIS data flow task that downloads data from an Oracle source which has some real dates and some date values of 0001-01-01 which I am trying to convert to '1900-01-01' using a decode(Date1,'0001-01-01','1900-01-01',Date1). The problem I am having is that when I run the package in Business Intelligence Development Studio on my local machine the date value is stored correctly in the SQL table in a datetime field as 1/1/1900 12:00:00 AM, however when I run the package from the server the field gets saved as 1/1/2001 12:00:00 AM.

I have tried a bunch of ways to work around this issue and the only solution I have found that works it to download into a temp table and then load into the live table. Does anyone have any idea of what might be causing this issue when I run the package from the server?

View 1 Replies View Related

Inserting NULL Values On Date Fields Trhough DAL

Jul 25, 2006

I am using a DAL and i want to insert a new row where one of the columns is DATE and it can be 'NULL'.
I am assigning SqlTypes.SqlDateTime.Null.
But when the date is saved in the database, i get the minvalue (1/01/1900) . Is there a way to put the NULL value in the database using DAL????how can i put an empty date in the database?
THANK YOU!!!

View 3 Replies View Related

Matrix Report, Date: 1900-01-01 Wrong!! Suppose To Be Empty, How?

Aug 10, 2007

Hi
I am making a report in Visual Studio. Im making a matrix report. I have made a UNION of 2 tables. One of them has a field called Date and the other one does not. But to make a UNION of these 2 tables so that the results are printed in a Matrix I have to have the same fields aliases at least. So what I did is that in the second table is:
SELECT AS Date
Right?

Now, in the report the first table gives me the dates in a format:
=Format(Fields!Estimated_Close_Date.Value, "yyyy-MM-dd")

But, the second table is it doesnt have a date in that field when I run the report it gives me:
1900-01-01

Something I dont want.

So, how do I make it understand that the second tables date field is suppose to be empty?

View 8 Replies View Related

Error Executing A Sql Statement Against A Table With Null Date Values

Apr 10, 2008

We are just upgrading a server from 2000 to 2005 and we are getting the message below when we execute a sql statement against a table with a date field with null values:


"Error converting data type DBTYPE_DBTIMESTAMP to datetime."

View 3 Replies View Related

Compressing Multiple Rows With Null Values To One Row With Out Null Values After A Pivot Transform

Jan 25, 2008

I have a pivot transform that pivots a batch type. After the pivot, each batch type has its own row with null values for the other batch types that were pivoted. I want to group two fields and max() the remaining batch types so that the multiple rows are displayed on one row. I tried using the aggregate transform, but since the batch type field is a string, the max() function fails in the package. Is there another transform or can I use the aggragate transform another way so that the max() will work on a string?

-- Ryan

View 7 Replies View Related

Query Help - Giving A Date Range Given The Start Date, Thanks!

Jul 23, 2005

Hi Group!I am struggling with a problem of giving a date range given the startdate.Here is my example, I would need to get all the accounts opened betweeneach month end and the first 5 days of the next month. For example, inthe table created below, I would need accounts opened between'5/31/2005' and '6/05/2005'. And my query is not working. Can anyonehelp me out? Thanks a lot!create table a(person_id int,account int,open_date smalldatetime)insert into a values(1,100001,'5/31/2005')insert into a values(1,200001,'5/31/2005')insert into a values(2,100002,'6/02/2005')insert into a values(3,100003,'6/02/2005')insert into a values(4,100004,'4/30/2004')insert into a values(4,200002,'4/30/2004')--my query--Select *[color=blue]>From a[/color]Where open_date between '5/31/2005' and ('5/31/2005'+5)

View 2 Replies View Related

Date Picker Bug - Drops The Default Value And Displays Default Value As Todays Date

Apr 3, 2008



Hi,
Does anyone have a workaround or know of a fix to this problem:
Default value set to 'date pick' from date currently within field by setting value equal to that field . ie if date is 01/01/2010 date picker opens in Jan 2010 - works ok.
However, once published to Sharepoint and run through browser the Date Picker ignores the default value and the date picker opens for today. ie April 2008.


Any words of wisdom gratefully recieved,

Howard Stiles

View 1 Replies View Related

Default Collation Selection

Sep 3, 2002

On installation of SQL2000 if one selects the 'typical' installation one gets a default collation of SQL_Latin1_General_CP1_CI_AS, which among other things sets the default code page to 1252 and accent sensitive. If one selects the 'custom' installation one can choose a default collation, not one of the backward compatible selections; in fact one must select a default collation in order to override the AS setting. If one does select the default collation to override the AS setting one gets a default collation of Latin1_General_CI_AI.

Unfortunately SQL_Latin1_General_CP1_CI_AS and Latin1_General_CI_AI are not compatible collation sequences for string comparison operations.

One of our vendors really wants the SQL_... collation setting for their database; however, our other enterprise applications want accent-insensitive set. Other than overriding the collation sequence for all databases and tables, etc. is there a way to create a server default collation setting of SQL_Latin1_General_CP1_CI_AI???

Thanks,

AL

View 2 Replies View Related

How To Use Default Parameter Values With A Date Parameter From A Cube/Reducing Parameters

Oct 15, 2007



Hi,

I have parameters in my report. The user can choose the year, month and date (3 parameters).
Now I want to set default vaules for the parameters , so that the user sees the report for example for the current day without selecting the parameters. I tried to set the type of the parameters to DateTime and the default value for example for the year to "=Today().Year" . But when I execute the report an error occures . Something like : no validValue for this parameter.

My Attributes for the year month and date are from an Analyis Services Cube from a Server Time dimension .
Does somebody know how to make it possible to set default values for this parameters?



Other question :

Does somebody know how I can reduce the values for a parameter. For Example I have a parameter "year" from a server time dimension from a cube. The values which are available are "Year 2004", "Year 2005", "Year 2006", "Year 2007".
But I want that the user only can choose "Year 2006" or "Year 2007" ant not every Year or "All".
Or Other Example: The User should only choose a Date that is int the past or Today but not a Date in the future.


Thanks !

JF

View 7 Replies View Related

Reporting Services :: Top N Giving Wrong Values

Jun 5, 2015

I using TOP N filter in my report to fetch top 20 Projects as per expense done.

Expression- Project_name
Operator- TOP N
value- 20

However it is not fetching the correct values. It is giving me 20 records but those are not the TOP 20.

It is missing few values from between.

View 3 Replies View Related

Subscription Issue With Null Default Parameter - Key Cannot Be Null

May 3, 2007

I have a report that is run on a monthly basis with a default date of null. The stored procedure determines the month-end date that it should use should it be sent a null date.

The report works fine when I tell it to create a history entry; however, when I try to add a subscription it doesn't appear to like the null parameter value. Since I have told the report to have a default value of null it doesn't allow me to enter a value on the subscription page.

Now, I suppose I could remove the parameter altogether from the stored proc, but then the users would never be able to run the report for a previous time period. Can someone explain to me why default values aren't allowed to be used on subscriptions when they seem to work fine for ad hoc and scheduled reports? This is really quite frustrating as most of my reports require a date value and default to null so that the user doesn't have to enter them for the latest data.



An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help




Key cannot be null. Parameter name: key

View 1 Replies View Related

Change Not Null To Null, Default Value To Empty

Aug 3, 2005

hi,my structure table in database:Amount float(53) not null default 0when i try to run his script:alter table ABC alter column Amount float(53) nullit can only set the Amount to allow null, but can't set the defaultvalue to empty.anyone know how to set the field to allow null and default set toempty, no value.thanks

View 5 Replies View Related

Alter Table Datatype Giving Error Date To Bit

Nov 20, 2014

IF EXISTS (SELECT * FROM SYS.COLUMNS WHERE NAME =N'Case_Escalated__c' AND OBJECT_ID = OBJECT_ID(N'[Case]'))
begin
alter table [Case] alter column Case_Escalated__c bit null
print 'Case_Escalated__c altered sucessfully for [Case]'
end

Msg 1792, Level 16, State 1, Line 3
Alter table 'Case' failed because the added fixed column might cause existing data to go beyond the maximum allowable table row size of 8060 bytes.

View 4 Replies View Related

Stored Procedure Giving Error When Searching On Date Range

Aug 20, 2007

I've  a report whose columns are returned from a stored procedure. Now I want to display the report based on a date range. The date field is Received. It's in dbo.master. I added 2 parameters start date and end date. When I check the condition if dbo.master.Received>StartDate and dbo.master.Received < EndDate directly I'm getting error. Could someone tell me what mistake I'm doing? Thanks for your help!ALTER Procedure [dbo].[USP_Reports_NewTier1]
 
@ClientCode VARCHAR(7) = '',@UserID INT = 0
,@OrderID INT =0
,@StartDate datetime,@EndDate datetime
 
IF @ClientCode <> '' and dbo.master.Received > StartDate and dbo.master.Received<EndDateBEGIN SELECT --Root Select --ClientName @ClientName = (Select Name  FROM dbo.customer c     WHERE   c.Customer = @ClientCode)     ,@TotalDollarValue = (SELECT SUM(m.current1-m.paid1)     FROM dbo.master m     WHERE phase=1     AND m.Customer = @ClientCode    AND M.Status <> 'PIE')
 ,@AverageAge = ISNULL((select avg(age) from    (select datediff(day,Received,CASE WHEN clidlp>clidlc then clidlp else clidlc END)* -1 as age    from dbo. master M    WHERE phase=1 AND customer = @ClientCode AND M.Status <> 'PIE') x),0)END

View 3 Replies View Related

Transact SQL :: First Values In Multiple Columns Selection

May 19, 2015

I'm trying to run something like this:

Select ID, FIRST(forename), FIRST(surname) from table1
GROUP BY ID;

I know First doesn't work in TSQL, I used to use it in Access and now need to run something like that in TSQL. Simply getting unique ID with first forename and surname, cause there are some dupes in a table.

There are records like:

ID      forename     surname
--------------------------------
1    John    Kormack
1   James  Dope
2   Erin    Dupes
3   Will  Hugh
3    Walter Heisenberg

So I want to pull out:

1 John Kormack
2 Erin Dupes
3 Will Hugh

How can I run it in TSQL?

View 6 Replies View Related







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