How To Find The Maximum Possible Value Of A Given Datatype
Jun 27, 2007
Does anyone know of a built-in function to return the maximum possible
value of a given datatype? I have to return the biggest value for a
smalldatetime or datetime in a view if the field is null, but can't
find such a function. The closest I've come is:
select datalength(cast(getdate() as smalldatetime))
....but that only return the number of bytes, not the value itself,
which is '6-6-2079 11:59'
I know I could create my own lookup table and function, but I was
hoping that Transact-SQL would have a built-in solution
--John Hunter
View 3 Replies
ADVERTISEMENT
Feb 19, 2012
My db contains two table Employee_detail and Student_Detail.Employee_detail has Emp_id and Student_Detail has stud_id.Now my problem is that i want to get one maximum no from these two table.
View 11 Replies
View Related
Oct 12, 2007
I have a table with member information. Each member has multiple effective dates. I would like to query to find the record for each member that has the maximum effective date. A sample table is below:
MEMBERID EFECTIVEDATE
------------ ------------------
699361401 20070101
699361401 20070501
732612701 20070101
732612701 20070501
575424301 20070101
575424301 20070501
192939801 20070101
192939801 20070501
458645001 20070101
458645001 20070501
View 4 Replies
View Related
Jun 13, 2013
How to select the orderID, order date and the maximum-price unit of each order in the below tables in 2 ways:
1-using correlated sub-query
2-not using correlated sub-query
I only selected orderID, order date and the maximum price of each order but not the unit name.This is my query:
Code:
SELECT dbo.[Order Details].OrderID, Max(dbo.[Order Details].UnitPrice)as MaxUnitprice, dbo.Orders.OrderDate
FROM dbo.[Order Details] INNER JOIN
dbo.Products ON dbo.[Order Details].ProductID = dbo.Products.ProductID INNER JOIN
dbo.Orders ON dbo.[Order Details].OrderID = dbo.Orders.OrderID
GROUP BY dbo.[Order Details].OrderID, dbo.Orders.OrderDate
View 6 Replies
View Related
Jan 21, 2007
hi ,How can I find sql Datatype (like NVARCHAR , DESIMAL & .. ) and size of columns and also is it Identity or not in a Table of SQl server?
View 4 Replies
View Related
Jul 22, 2006
Hi,
How do we find the "column name" and "data type" of all the columns in a table. Assuming that I know the Table name or Table Object ID. I am using Microsoft SQL Server 2000.
Thanks
-Sudhakar
View 3 Replies
View Related
May 30, 2007
Dear experts,
how can i find the ntext datatype columns in a database?
please guide me
View 4 Replies
View Related
Sep 12, 2015
I have some code I build 2 weeks ago which I’ve been running daily but it’s suddenly stopped working with the following error.
“The table "tbl_Intraday_Tmp" has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes. INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit” When I google this there seems to be a related to tables with vast numbers of columns.
My table tbl_Intraday_tmp is relatively small. It has 7 columns. 1 of varchar(5), 3 of decimal(9,3) and 2 of decimal(18,0). The bit I’m puzzled with is it was working and stopped.
I don’t recall changing anything but I wouldn’t rule that out. I ‘ve inspected the source files and I don’t believe they have changed either.
DECLARE
@FileName varchar(50),
@Path varchar(50),
@SqlCmd varchar(1000)
= '',
@ASXCode varchar(5),
@Offset decimal(18,0),
[code]....
View 5 Replies
View Related
Apr 20, 2012
I am using MS SQL server 2008, and i have a table with 350 columns and when i m trying to create one more column its giving error with below message -
Warning: The table XXX has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes.
INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit.
how can i resolve this?
View 14 Replies
View Related
Oct 10, 2005
SQL Server 2000 8.00.760 (SP3)I've been working on a test system and the following UDF worked fine.It runs in the "current" database, and references another database onthe same server called 127-SuperQuote.CREATE FUNCTION fnGetFormattedAddress(@WorkID int)RETURNS varchar(130)ASBEGINDECLARE@Address1 As varchar(50)@ReturnAddress As varchar(130)SELECT@Address1 = [127-SuperQuote].dbo.tblCompany.Address1FROM[Work] INNER JOIN[127-SuperQuote].dbo.tblCompany ON [Work].ClientID =[127-SuperQuote].dbo.tblCompany.CompanyIDWHERE[Work].WorkID = @WorkIDIF @Address1 IS NOT NULLSET @ReturnAddress = @ReturnAddress + @Address1 + CHAR(13)+ CHAR(10)RETURN @ReturnAddressENDSo now the system has gone live and it turns out that the live"SuperQuote" database is on a different server.I've linked the server and changed the function as below, but I get anerror both in QA and when checking Syntax in the UDF builder:The number name 'Zen.SuperQuote.dbo.tblCompany' contains more than themaximum number of prefixes. The maximum is 3.CREATE FUNCTION fnGetFormattedAddress(@WorkID int)RETURNS varchar(130)ASBEGINDECLARE@Address1 As varchar(50)@ReturnAddress As varchar(130)SELECT@Address1 = Zen.SuperQuote.dbo.tblCompany.Address1FROM[Work] INNER JOINZen.SuperQuote.dbo.tblCompany ON [Work].ClientID =Zen.SuperQuote.dbo.tblCompany.CompanyIDWHERE[Work].WorkID = @WorkIDIF @Address1 IS NOT NULLSET @ReturnAddress = @ReturnAddress + @Address1 + CHAR(13)+ CHAR(10)RETURN @ReturnAddressENDHow can I get round this? By the way, I've rather simplified thefunction to ease readability. Also, I haven't posted any DDL because Idon't think that's the problem!ThanksEdward
View 2 Replies
View Related
Jan 28, 2015
Need to know if the varchar datatype field will ingore leading zeros when compared with numeric datatype ?
create table #temp
(
code varchar(4) null,
id int not null
)
insert into #temp
[Code] .....
View 4 Replies
View Related
Apr 24, 2008
Good afternoon,
I have an issue with an ssis variable datatype.
The scenario is as follows:
I have a stored procedure:
PROCEDURE [dbo].[sp_newTransaction]
@sourceSystem varchar(50),
@txOut NUMERIC(18,0) OUTPUT
AS
insert into scn_transaction (sourceSystemName) values(@sourceSystem);
SELECT @txOut = @@identity
Whose purpose is to perform an insert into a table and return me the identity value of the inserted record, which I'll then use throughout the rest of my package. The identity column in the inserted table is numeric(18,0).
I execute the stored proc with the following sql with an OLE DB connection manager:
exec sp_newTransaction ?, ?
The first parameter is a string variable from earlier in the package, and the second is the output parameter. I have the following parameter mappings to the execute sql task:
User:ystxId output numeric 1 -1
User:ourceSys input varchar 0 -1
The proc is correctly called, and the row insesrted, however I get a type conversion error when SSIS attempts to map the return parameter to my package variable... I've tried all sorts of combonations, and can't seem to get it to execute.
At one point I wasn't returning a numeric, but rather an int from the stored proc, and all was well until I went to use the variable in a derived column later in the package, and the type was converted quite incorrectly (a 1 was 77799789080 or some such), indicating a type conversion error likely related to the encoding of the number.
I'd like to keep the datatypes as numeric and make ssis use those - any pointers are greatly appreciated as to what type my package variable should be to allow proper assignment of a sql server numeric type to it.
Thanks much,
B
View 6 Replies
View Related
Sep 17, 2003
Database is SQL Server 2000
I have a field in a table that stores date of birth. The field's datatype is char(6) and looks like this: 091703 (mmddyy). I want to convert this value to a datetime datatype.
What is the syntax to convert char(6) to datetime?
Thank you in advance.
View 1 Replies
View Related
Mar 14, 2008
Hi,
I imported a table from Accees to SQL 7 with data in it.
I need to modify one of the datatype columns to "datetime" from nvarchar.
I tried to convert it manually, in SQL Server Enterprise Manager tool, but it gave me an error.
I also tried, creating another column "DATE2-datatype:datetime" and updating the column with the old one.
UPDATE users SET DATE2 = DATE.. But it also faild,..
How can I modify the column?
Thank you.
View 10 Replies
View Related
Dec 15, 2005
HI,I have a table with IDENTITY column with the datatype as INTEGER. Nowthis table record count is almost reaching its limt. that is totalrecord count is almost near to 2^31-1. It will reach the limit with inanother one or two months.In order to avoid the arithmentic overflow error 8115, we would likechange the datatype from INT to BIGINT. we hope this will solve ourproblem.How do I approch this datatype conversion?. Since the data count ishuge, that leads to a long down time of database.we need better approach or solution for this problem?. kindly give mea better solution that will reduce the total downtime of the productiondatabase.?.Regards
View 1 Replies
View Related
Feb 25, 2008
Hi guys..
i have so doubts in my mind and that i want to discuss with you guys... Can i use more then 5/6 fields in a table with datatype of Text as u know Text can store maximu data... ? acutally i am trying to store a very long strings values into the all fields. it's just popup into my mind that might be table structer would not able to store that my amount of data when u use more then 5/6 text datatypes...
and another thing... is which one is better to use as data type "Text" or "varchar(max)"... ?
if any article to read more about these thing,, can you refere to me...
Thanks and looking forward.-MALIK
View 5 Replies
View Related
Jul 26, 2005
I tried all the INFORMATION_SCHEMA on SQL 2000 andI see that the system tables hold pretty much everything I aminterested in: Objects names (columns, functions, stored procedures, ...)stored procedure statements in syscomments table.My questions are:If you script your whole database everything you end up havingin the text sql scripts, are those also located in the system tables?That means i could simply read those system tables to get any informationI would normally look in the sql script files?Can i quickly generate a SQL statement of all the indexes on my database?I read many places that Microsoftsays not to modify anything in those tables and not query them since theirstructure might change in future SQL versions.Is it safe to use and rely the system tables?I basically want to do at least fetching of information i want from thesystem tables rather than the SQL script files.I also want to know if it's pretty safe for me to make changes in thesetables.Can i rename an object name for example an Index name, a Stored Procedurename?Can i add a new column in the syscolumns table for a user table?Thank you
View 4 Replies
View Related
Jun 12, 2008
I have 3 database say
db1
db2
db3
I need to setup a script to read all the table names in the database above and then query the database to find the list of Stored Procedure using each table.(SQL Server)
View 5 Replies
View Related
Jul 6, 2006
hi, im trying to get the last row of a column by using this code
cmd.CommandText = "SELECT max(orderID) as orderID from orders where memberNo = '" & Session.Item("memberNum") & "'"but it return me 1 instead which is the smallest value in my orderID column n located in the first rowthen i try to use this code but they say got syntax errorcmd.CommandText = "SELECT max(orderID) as orderID from orders where memberNo = '" & Session.Item("memberNum") & "'" ' order by orderID asc " ' and orderID = '" & Session("ordersID") & "'"
can anyone tell me the right way to write the syntax??
thx in advance
View 2 Replies
View Related
Aug 7, 2002
Does anyone know the specific website that documents the maximum RAM SQL 7.0 Standard and Enterprise edition can handle within Windows 2000 Server and Windows 2000 Advanced server? All help appreciated!!
darius
View 2 Replies
View Related
May 29, 2001
Can anyone tell me the maximum number of (if there is a maximum) for the following :-
1. SQL Server Users per Database
2. Roles per Database
3. Connections per Server
4. Logins per Server
Thanks.
View 1 Replies
View Related
Nov 29, 2000
Hi Everybody,
Our development team is doing some stuff in ASP. They are interested only in char and varchar
data types. They clearly told us that they are not going with text and image data types.
Is it possible to make a row size behind 8060 bytes in a table without text and image data types?.
tks in advance,
Sri.
View 1 Replies
View Related
Feb 9, 1999
Hi,
I have a table with 188376 rows and the data size = 3012 KB, index size = 5884 KB .
LE threshold max is set to 2000 and LE threshol percent to 20%
I have an index on that table and observed that it is not getting used.
I would like to know whether sql optimizer uses the index based on the cost
of the query plan or does the table scan once the LE thresholdlimit is reached
overriding the optimized plan.
thanks,
MMS
View 1 Replies
View Related
Jun 3, 2004
I'm using replication between two server, but I don't know the maximum record will be move from Database 1 on Server1 to Database2 on Server2 in per minute.please help me, thanks!
View 2 Replies
View Related
Oct 23, 2013
how to filter on a maximum value ?
My query is this:
SELECT
ProdspecUID
,itemcode
,model
,revno
FROM prodspec
The revision numbers (revno) are unique per itemcode.I would like to get only the rows with the highest revision per itemcode, but I also need the prodspecUID with is the index key and therefore unique.I have tried MAX() and TOP 1's in sub queries, but up until now without any result.
View 3 Replies
View Related
Nov 8, 2005
Hello
Without technical information and DB design, I wish to know the maximum entries in MS SQL in million. I guest for access are a limit of 2 million and a 2GB DB.
How much for MS SQL in max. entries and how much in GB?
A lot of thx for your feedback.
Dominique Javet
View 4 Replies
View Related
Dec 18, 2006
hi guys,
I have 4 columns and 3 rows. Columns are Name, Age, Gender and Weight. I have values entered for each column. I need to pick the highest value of weight if Name, Age, Gender are same and put that into new table. how can i do that?
View 6 Replies
View Related
Mar 14, 2007
Ex Table
-----------------
ColName====> Field Name
------------
xyz
abc
xyz
abc
abc
sdf
abc
....
....
---------
In table column string. i need maximum ColName.. In above table maximum ColName is abc... also top 3 maximum ColName i need...
Result
------
abc
How to i'll get any query........
Top 3 Maximum Result
--------------------
abc
xyz
sdf
----------------------------
this also.......
View 10 Replies
View Related
Jun 13, 2007
Hi...
I have written a stored procedure with number of inserts as the input variables which will input those many rows into my execution table... When i try to input more than 31 rows it gives me a error shown below.... I have a trigger attached to the execution table where i am inserting the rows... I am unable to sort out the error..
If anyone knows whats the problem Please reply.... Thank you....
Msg 217, Level 16, State 1, Procedure runtime_insert_elevator_table, Line 105
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
anup
View 6 Replies
View Related
Nov 5, 2007
Dear all,
I have a input table like this:
FieldA FieldB FieldC
------------------------------------------------
ItemA 2007-10-01 10
itemA 2007-10-10 20
ItemA 2007-10-20 30
ItemB 2007-10-05 5
ItemB 2007-10-26 9
Result :
FieldA FieldB FieldC
------------------------------------------------
ItemA 2007-10-20 30
ItemB 2007-10-26 9
How can I get such result? i.e. I want to get the record with the latest date from each item group?
thanks in advance!
View 4 Replies
View Related
Mar 18, 2008
I want to select the latest photos that were posted on my site from my photos table:id name usercode createdate1 holiday 5 1/1/20082 holiday2 5 1/1/20083 my car 5 1/1/20084 new home 7 1/1/20085 starry night 8 1/1/20086 me again 6 10/10/2007But in case one user has posted like 400 photos I dont want to show 400 photos of the same user as that would mess up latest photos. So I want to show the latest photos but always with a maximum of 3 for the same user...So in the above data example pictures with id 1,2,3,4,5 would be shownHow can I achieve this with SQL (and as always: without temp tables :))Thanks!
View 7 Replies
View Related
Apr 4, 2008
hi...i need a stored procedure query to get the maximum occurred value ie(more than one time occurred value).... this is my table structure.... table Name: PHOTO_GALLERY_VISITSpgv_id
pgv_main_category_id pgv_sub_category_id pgv_path
pgv_visitor_id
pgv_visited_date 6 54
72
photo/Writer/Content/Mobiles/LG/camer4.jpg 26
4/3/2008 6:41:02 PM 7 54
73
photo/Writer/Content/Mobiles/Samsung/mobile1.jpg 26
4/3/2008 6:41:43 PM 8 64
85
photo/Writer/Content/Movie/Bachan/bhachan1.jpg 25
4/4/2008 3:37:17 PM 9 63
82
photo/Writer/Content/sports/Cricket/cricket1.jpg 34
4/4/2008 3:37:29 PM 10
54 73
photo/Writer/Content/Mobiles/Samsung/mobile1.jpg 34
4/4/2008 3:37:29 PM 11
54 73
photo/Writer/Content/Mobiles/Samsung/mobile1.jpg 26
4/4/2008 3:37:29 PM 12
54 73
photo/Writer/Content/Mobiles/Samsung/mobile1.jpg 54
4/4/2008 3:37:29 PM 13
54 72
photo/Writer/Content/Mobiles/LG/camer4.jpg 59
4/4/2008 3:37:17 PM The above is my sample table...... In that filter the most occurrence based on the column "pgv_sub_category_id"in the above table the 73 is the maximum occurred id after that the second maximum ie..72 is the 2nd maximum occurred....hence i want the output as only the id & path which occurred more than one time..... pgv_sub_category_id pgv_path 73 photo/Writer/Content/Mobiles/Samsung/mobile1.jpg 72 photo/Writer/Content/Mobiles/LG/camer4.jpg plz anybody send me the query....its urgent.....thanks in advance.....regardsjanu
View 4 Replies
View Related
Dec 6, 2004
Hallo Cracks,
what I try is a little bit heavy, maybe, but I only miss the minimum/maximum fuction - or I didn´t found it; not here in the Forum and also not in the onlinehelp of the SQL Server.
What I try to do:
I have 2 columns in my table; a start- and an end-date. For this period of time between end and start i have to calculate the days for the years. Here my thoughts (for the current year):
Is the startdate <= 31.12.2004 and the enddate >= 1.1.2004 i have to calculate die datediff between max(1.1.2004/startdate) and min(31.12.2004/enddate)
like this sqlstatement:
SELECT CASE WHEN dbo.Phases.phasenstart <= CAST(CAST(YEAR(GETDATE()) AS
varchar) + '-31-12' AS smalldatetime) AND dbo.Phases.phasenabschlussist >=
CAST(CAST(YEAR(GETDATE()) AS varchar) + '-01-01' AS smalldatetime)
THEN 365 ELSE 0 END AS Expr2,
FROM dbo.Phases
WHERE (phasenstart IS NOT NULL) AND (phasenabschlussist IS NOT NULL)
instead of 365 there must be the above calculation. Is start=3.1.2003 and end=30.1.2004 I expect as result only the 30 days in 2004.
thanks in advance and kind regards :-)
Cappu
View 2 Replies
View Related
