Friday, June 28, 2013

[T-SQL] Converting Integer date & time into datetime variable .. help!

[T-SQL] Converting Integer date & time into datetime variable .. help!

Converting Integer date & time into datetime variable .. help!

Posted: 27 Jun 2013 07:43 AM PDT

Hi!I have two fields that are integer type in this formatDATE: 1130627 = (exclude the first digit)(13-06-27)TIME: 51458 (24hr) = 5:14:58I am trying to get this into a single datetime field and I cant figure it out :( Can anyone help please.Thanks!R.

Please Help me in the query

Posted: 27 Jun 2013 09:23 PM PDT

Hi team,The below query takes 100 % CPU. When I see in execution plan, its going in index seek. Kindly help me to get rid off this issue.SELECT CASE WHEN substring(cli, 1, 2) = '44' THEN cli ELSE '44' + cli END 'cli', min(call_date) 'date' FROM DbName..table_name1(nolock) WHERE network_id = '1' and dialed_number not in(select msisdn from table_name2(nolock)) group by CASE WHEN substring(cli, 1, 2) = '44' THEN cli ELSE '44' + cli END Note : CLI & call_date comes in table_name1 tableThanks in advance

Help with comma separated string in UDF to return comma separated string

Posted: 27 Jun 2013 06:43 AM PDT

Hi all,I have a top level sproc that returns x number of records. Each record has a field called CategoryID that stores a comma separated list of ID's like this:12,15,33Now I need to create a UDF that accepts this string of ID's and returns the corresponding Category Names for each, like this:'Sports,Education,Science'These are the 3 category names for the ID's of 12, 15 and 33.Ideally, using the input param of the UDF like this would be perfect:select @list = @list + ', ' + convert(varchar(100), AOC.CategoryName)from AccountOpeningCategories AOCwhere AOC.AccountOpeningCategoryID in (@CategoryIDList) But that doesn't work. I can't use temp tables in a UDF nor can I split the ID string in to a table to use as part of my select. Also, I can't execute a sproc in a select from another sproc.Since I've learned many ways how NOT to do this, I was hoping someone could look at my sproc below (which works) and tell me how I can convert this to a UDF to call within a select of another sproc like this:select F.column1, F.column2, dbo.acct_f_ReturnCategoryList(F.CategoryIDList) as CategoryListfrom Foo as FThanks,Mark[code]alter PROCEDURE acct_f_ReturnCategoryList @IDList varchar(100)asDeclare @list varchar(8000), @sql varchar(8000), @UID uniqueidentifier, @idx smallint, @Delimiter varchar(5), @slice varchar(100) --------------------------------------------------------------------------------- Split the incoming comma separated list in to a temp table and match it with -- a guid in case multiple users are in the DB at the same time.-------------------------------------------------------------------------------set @UID = newid()select @idx = 1 set @Delimiter = ',' if len(@IDList)<1 or @IDList is null return while @idx!= 0 begin set @idx = charindex(@Delimiter,@IDList) if @idx!=0 set @slice = left(@IDList,@idx - 1) else set @slice = @IDList if(len(@slice)>0) insert into dbo.tempIDListTable(TempID, GUID) values(@slice, @UID) set @IDList = right(@IDList,len(@IDList) - @idx) if len(@IDList) = 0 break end-------------------------------------------------------------------------------set @list = ''select @list = @list + ', ' + convert(varchar(100), AOC.CategoryName)from AccountOpeningCategories AOC, dbo.tempIDListTable IDLT where AOC.AccountOpeningCategoryID = IDLT.TempIDand IDLT.[GUID] = @UID-- Clear this users entry.delete from tempIDListTable where GUID = @UID-- Return the list.select @list[/code]

Top 2 with Count

Posted: 27 Jun 2013 10:17 PM PDT

Hi All,I have written a query. [quote]declare @N intdeclare @N intSet @n=isnull(@N,2000)select top(@N) * from(select 10 'Top 2'UnionSelect 20UnionSelect 30unionselect 40unionselect 50)T1[/quote]this is working fine. if change 2000 to 2 it gives me the result only 2 results. Now my question is when I am not setting any value then it should show me all the records.[quote]declare @N int--Set @n=isnull(@N,2000)select top(@N) * from(select 10 'Top 2'UnionSelect 20UnionSelect 30unionselect 40unionselect 50)T1[/quote]when I am executing the above query it gives the error that TOP clause contains an invalid value as I have not set any value.I want to see all the records when I am not setting any value.Please help..........Thanks in advance

How to avoid this error when scripting multiple sp? Thanks.

Posted: 27 Jun 2013 07:42 AM PDT

I have multiple sp with @sql defined, when I script these sp, it throws error saying @spl already defined, well, true or false, in the final generated script, yes; but I want to re-generate all the sp in a new database, eventually there would be multiple sp generated and those @sql will fall into different sp and hence @sql is NOT already defined.How do I avoid this error?Thanks.

XML Query

Posted: 27 Jun 2013 06:59 PM PDT

Hi all,This is is quite simple, but i need you help pls!I need to get the value of one of the parameters (CustomProperty1) included in the Field of XML data type. Here the example of data in this field: DECLARE @XML XMLSET @XML ='<CP> <CustomProperty1>Someroperty1</CustomProperty1> <CustomProperty2>Someroperty2</CustomProperty2> <CustomProperty3>Someroperty3</CustomProperty3></CP>'Meaning, i want the select to return the value: Someroperty1Thanks!!

Function inside a View

Posted: 27 Jun 2013 03:40 PM PDT

Hi, We have a View which calls a function to get the data. Following is the code of the View:[code="sql"]CREATE View View_FacultyTimeTableAsSelect Distinct B.ClassId,B.ClassName,B.Section,A.Subject,A.Faculty,A.Wing,B.IntClass,'' As VirtualClass From SchoolDefinePeriod As A,SchoolClass As B Where A.ClassId=B.ClassIdUnionSelect Distinct B.ClassId,B.ClassName,B.Section,A.Subject,A.Faculty,A.Wing,B.IntClass,A.VirtualClass From SchoolDefinePeriod As A,SchoolClass As B,FN_SchoolVirtualClass()As C Where A.VirtualClass=C.VirtualClass And B.ClassId=C.ClassID[/code] Is it a bad practice to call a function inside a View??....How many times does this function get compiled??....Only Once(when the View is created) or every time the View is used in a Query?......Thanks in Advance for your help guys. :-):-)

Extract first numeric value from a string

Posted: 27 Jun 2013 05:57 AM PDT

Brothers,I need to parse the first numeric value from a string that usually contains several numerics.This function works OK when the first number has no decimals ( 1 - returns '500000'). In #2 (naturally!) the result is 0Can you spare some help? [code="sql"]alter FUNCTION dbo.fnExtractDigits (@inString VARCHAR(8000))RETURNS VARCHAR(8000) ASBEGIN DECLARE @X VARCHAR(100)Select @X=@inString Select @X= SubString(@X,PATINDEX('%[0-9]%',@X),Len(@X))Select @X= SubString(@X,0,PATINDEX('%[^0-9]%',@X))RETURN @XEND-- 1. select dbo.fnExtractDigits ('NYSTATIN SUSP 500000 UNIT = 5 ML (1 5 ML CUP)')-- 2. select dbo.fnExtractDigits ('NYSTATIN SUSP 0.75 UNIT = 5 ML (1 5 ML CUP)')[/code]

Converting varbinary to numeric type in tsql -- decimal in c#

Posted: 27 Jun 2013 01:39 AM PDT

We are currently dealing with a legacy application, where the decimal numbers from c# are stored in the database (sqlserver) as varbinary types. This (I think) was done to keep formatting with the number.The problem now is that we can not search/index on the number in the database. It has to be restored to a c# decimal in the application and then only does this make sense.How can I convert the varbinary to a decimal/numeric type in the sqlserver?I don't mind creating a new column/table to store the numeric value and formatting information derived from the varbinary.I know in c# you can create a decimal number by giving it an array of ints.Here is the description of how c# interprets and converts an int array to decimal type. [url=http://msdn.microsoft.com/en-us/library/aa326746(v=vs.71).aspx]http://msdn.microsoft.com/en-us/library/aa326746(v=vs.71).aspx[/url]The binary representation of a Decimal number consists of a 1-bit sign, a 96-bit integer number, and a scaling factor used to divide the integer number and specify what portion of it is a decimal fraction. The scaling factor is implicitly the number 10, raised to an exponent ranging from 0 to 28. bits is a four-element long array of 32-bit signed integers. bits [0], bits 1, and bits [2] contain the low, middle, and high 32 bits of the 96-bit integer number. bits [3] contains the scale factor and sign, and consists of following parts: Bits 0 to 15, the lower word, are unused and must be zero. Bits 16 to 23 must contain an exponent between 0 and 28, that indicates the power of 10 to divide the integer number. Bits 24 to 30 are unused and must be zero. Bit 31 contains the sign; 0 meaning positive, and 1 meaning negative. A numeric value might have several possible binary representations; all are equally valid and numerically equivalent. Note that the bit representation differentiates between negative and positive zero. These values are treated as being equal in all operations.Help is highly appreciated.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog