I am new to sql server query. I am trying to write a query for an application and I want user to enter number of over due days for payment.Below is my query and I am getting an error: An expression of non-boolean type specified in a context where a condition is expected, near 'Group'.
SELECT (P.FirstName+', '+P.LastName) As PA, Px.PRespDate,Px.DateFrom
WHERE Px.Hide = 0 AND Px.Total <> Px.Payments AND Px.PRespDate IS NOT NULL AND PE.Hide = 0 AND PE.BillReady = 1
AND DATEDIFF(DAY, Px.DateFrom, PRespDate) LIKE 'Enter # of days |%% '
Group By (P.FirstName+', '+P.LastName), Px.PRespDate,Px.DateFrom
I have a column which stores a set of dates. I want to tell how many days left of a date till it’s month end. It should be noted that month ends are taken from the date series, not a calendar month end.
user data saved in db select distinct UserID,Name from Userss Where IsActive=1 and order by UserID and i want to just calculate no of days in month based on year and month name supplied by user. one way i can do it. first i will create a temporary table and in loop add many columns to that table and later dump user data to specific column.
We have a stored procedure which currently accepts the following parameter: exec dbo.PurgeOldData <'yyyy-mm-dd'...We want to schedule this sp to run once a (first day of the month) going back to say today's date (or the date when this sp is called to run) minus 365 or purging 12 months data. Basically, the function should convert the today's date minus xxx number of days to yyyy-mm-dd so that the sp accept it as the correct parameter and purges the data successfully. So, if the sp is run on 2015-08-06, it will purge data going back to 2014-08-06 or earlier with the following parameter:
exec dbo.PurgeOldData <'2014-08-06'>
which means, all the data prior to the above date will be purged. Even the sp was run on 2015-08-06, the converted value of 2014-08-06' is entered as the sp parameter.
I have a windows forms app and I'm trying to add functionality that would allow the user to enter a number into a textbox control on a setup form and then that number would autoincrement by one each time a new record on another form is inserted into the DB .
I've searched high and low and can't seem to find much information on how I can put this together. I'm sure I'll need a "numbers" table in the DB that stores the numbers and is joined with the parent table where the records are being inserted into. After that, I'm pretty much lost on how to generate the numbers in the "Numbers" table.
If anyone could assist or point me in the right direction of a resource, I would really appreciate it!!
SQL express 2012. I am trying to case in the where part and having a syntax errors - This is what i am trying to do:
select all the days in week number x including last year if necessary... so if the year start not at the beginning of the week then look in last year as well ( for the same week number of this year and last week nu of last year)
declare @yyyy int = 2014,-- THE YEAR @mm int = 1,-- THE MONTH @week1No int = 1,-- THE WEEK NUMBER IN THE YEAR @week2No int = 37-- THE last WEEK NUMBER IN last YEAR select count(tblDay.start)-- tblDay.start IS smallDatetime
I am trying to case in the where part and having a syntax errors - this is what i am trying to do:
Select all the days in week number x including last year if necessary... so if the year start not at the beginning of the week then look in last year as well ( for the same week number of this year and last week nu of last year)
declare @yyyy int = 2014,-- THE YEAR @mm int = 1,-- THE MONTH @week1No int = 1,-- THE WEEK NUMBER IN THE YEAR @week2No int = 37-- THE last WEEK NUMBER IN last YEAR select count(tblDay.start)-- tblDay.start IS smallDatetime
I have created table called Login in sql server where i have column usercode, email and login_date (login_date is datetime type)So, i created web application using .net. whenever user logged in, i am allowing based userLoged table and i am inserting into login table.
I would like to offer you the following string functions Transact-SQL
GETWORDCOUNT() Counts the words in a string GETWORDNUM() Returns a specified word from a string AT() Returns the beginning numeric position of the first occurrence of a character expression within another character expression, counting from the leftmost character RAT() Returns the numeric position of the last (rightmost) occurrence of a character string within another character string OCCURS() Returns the number of times a character expression occurs within another character expression PADL() Returns a string from an expression, padded with spaces or characters to a specified length on the left side PADR() Returns a string from an expression, padded with spaces or characters to a specified length on the right side PADC() Returns a string from an expression, padded with spaces or characters to a specified length on the both sides PROPER() Returns from a character expression a string capitalized as appropriate for proper names RCHARINDEX() Is similar to a built-in function Transact-SQL charindex but the search of which is on the right ARABTOROMAN() Returns the character Roman number equivalent of a specified numeric expression ROMANTOARAB() Returns the number equivalent of a specified character Roman number expression ...
More than 2000 people have already downloaded my functions. I hope you will find it useful as well.
For more information about string UDFs Transact-SQL please visit the http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,54,33,27115
Please, download the file http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,2,27115
I am pleased to offer, free of charge, the following string functions Transact-SQL:
AT(): Returns the beginning numeric position of the nth occurrence of a character expression within another character expression, counting from the leftmost character. RAT(): Returns the numeric position of the last (rightmost) occurrence of a character string within another character string. OCCURS(): Returns the number of times a character expression occurs within another character expression (including overlaps). OCCURS2(): Returns the number of times a character expression occurs within another character expression (excluding overlaps). PADL(): Returns a string from an expression, padded with spaces or characters to a specified length on the left side. PADR(): Returns a string from an expression, padded with spaces or characters to a specified length on the right side. PADC(): Returns a string from an expression, padded with spaces or characters to a specified length on the both sides. CHRTRAN(): Replaces each character in a character expression that matches a character in a second character expression with the corresponding character in a third character expression. STRTRAN(): Searches a character expression for occurrences of a second character expression, and then replaces each occurrence with a third character expression. Unlike a built-in function Replace, STRTRAN has three additional parameters. STRFILTER(): Removes all characters from a string except those specified. GETWORDCOUNT(): Counts the words in a string. GETWORDNUM(): Returns a specified word from a string. GETALLWORDS(): Inserts the words from a string into the table. PROPER(): Returns from a character expression a string capitalized as appropriate for proper names. RCHARINDEX(): Similar to the Transact-SQL function Charindex, with a Right search. ARABTOROMAN(): Returns the character Roman numeral equivalent of a specified numeric expression (from 1 to 3999). ROMANTOARAB(): Returns the number equivalent of a specified character Roman numeral expression (from I to MMMCMXCIX).
AT, PADL, PADR, CHRTRAN, PROPER: Similar to the Oracle functions PL/SQL INSTR, LPAD, RPAD, TRANSLATE, INITCAP.
Plus, there are CHM files in English, French, Spanish, German and Russian.
More than 6000 people have already downloaded my functions. I hope you will find them useful as well.
For more information about string UDFs Transact-SQL please visit the http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,54,33,27115
Please, download the file http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,2,27115
I would like to offer you the following string functions Transact-SQL
GETWORDCOUNT() Counts the words in a string GETWORDNUM() Returns a specified word from a string AT() Returns the beginning numeric position of the first occurrence of a character expression within another character expression, counting from the leftmost character RAT() Returns the numeric position of the last (rightmost) occurrence of a character string within another character string CHRTRAN() Replaces each character in a character expression that matches a character in a second character expression with the corresponding character in a third character expression STRFILTER() Removes all characters from a string except those specified OCCURS() Returns the number of times a character expression occurs within another character expression (include overlaps) PADL() Returns a string from an expression, padded with spaces or characters to a specified length on the left side PADR() Returns a string from an expression, padded with spaces or characters to a specified length on the right side PADC() Returns a string from an expression, padded with spaces or characters to a specified length on the both sides PROPER() Returns from a character expression a string capitalized as appropriate for proper names RCHARINDEX() Is similar to a built-in function Transact-SQL charindex but the search of which is on the right ARABTOROMAN() Returns the character Roman number equivalent of a specified numeric expression (from 1 to 3999) ROMANTOARAB() Returns the number equivalent of a specified character Roman number expression (from I to MMMCMXCIX)
AT, PADL, PADR, CHRTRAN, PROPER are similar to functions Oracle PL/SQL INSTR, LPAD, RPAD, TRANSLATE, INITCAP
More than 2000 people have already downloaded my functions. I hope you will find it useful as well.
For more information about string UDFs Transact-SQL please visit the http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,54,33,27115 Please, download the file http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,2,27115
I would like to offer you the following string functions Transact-SQL
GETWORDCOUNT() Counts the words in a string GETWORDNUM() Returns a specified word from a string AT() Returns the beginning numeric position of the first occurrence of a character expression within another character expression, counting from the leftmost character RAT() Returns the numeric position of the last (rightmost) occurrence of a character string within another character string OCCURS() Returns the number of times a character expression occurs within another character expression PADL() Returns a string from an expression, padded with spaces or characters to a specified length on the left side PADR() Returns a string from an expression, padded with spaces or characters to a specified length on the right side PADC() Returns a string from an expression, padded with spaces or characters to a specified length on the both sides PROPER() Returns from a character expression a string capitalized as appropriate for proper names RCHARINDEX() Is similar to a built-in function Transact-SQL charindex but the search of which is on the right ARABTOROMAN() Returns the character Roman number equivalent of a specified numeric expression ROMANTOARAB() Returns the number equivalent of a specified character Roman number expression ...
For more information about string UDFs Transact-SQL please visit the http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,54,33,27115
Please, download the file http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,2,27115
Description GETWORDCOUNT() Counts the words in a string GETWORDNUM() Returns a specified word from a string AT() Returns the beginning numeric position of the first occurrence of a character expression within another character expression, counting from the leftmost character RAT() Returns the numeric position of the last (rightmost) occurrence of a character string within another character string OCCURS() Returns the number of times a character expression occurs within another character expression PADL() Returns a string from an expression, padded with spaces or characters to a specified length on the left side PADR() Returns a string from an expression, padded with spaces or characters to a specified length on the right side PADC() Returns a string from an expression, padded with spaces or characters to a specified length on the both sides PROPER() Returns from a character expression a string capitalized as appropriate for proper names RCHARINDEX() Is similar to a built-in function Transact-SQL charindex but the search of which is on the right
For more information about string UDFs Transact-SQL please visit the http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,54,33,27115
I have a table RequestUtility with 3 columns Vendor, name, system. I want to call this table from BizTalk so that I send multiple vendors and will get corresponding Vendor, name and system for that. As I need to send multiple vendors at the same time I have created a User-Defined Table type VendorNames with a single column Names. and have written a SP :
ALTER PROCEDURE [dbo].[GetName] -- Add the parameters for the stored procedure here @vendorN VendorNames ReadOnly AS -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Select statements for procedure here SELECT * from dbo.RequestUtility where Vendor IN (SELECT Names from @vendorN)
This is working fine when the value for vendor is present in the table. But if the value is not present it is not returning any thing. But, my requirement is that for example vendor is Dummy which is not present in the table so it should return
field ([idConteudo] [int] NULL) to ([idConteudo] [BigInt] NULL)
Or really should drop and create again?
CREATE TYPE [dbo].[tbProjeto] AS TABLE( [dsConteudo] [varchar](64) NOT NULL, [idConteudo] [int] NULL, [dtConteudo] [datetime] NULL DEFAULT (getdate()) ) GO
SELECT DATEDIFF(n , LAG(CAST(Date AS DATETIME) + CAST(Time AS DATETIME), 1) OVER ( ORDER BY Date, Time ), CAST(Date AS DATETIME) + CAST(Time AS DATETIME)) FROM [DataGapTest]
Gives the right output:
NULL 1 1 3548 0
However, when I put the statement in a function, I get only zeros as the output. It's as if the lag and current value are always the same (but they are not of course).
CREATE FUNCTION dbo.GetTimeInterval(@DATE date, @TIME time) RETURNS INT AS BEGIN DECLARE @timeInterval INT SELECT @timeInterval = DATEDIFF(n , LAG(CAST(@Date AS DATETIME) + CAST(@Time AS DATETIME), 1) OVER ( ORDER BY Date, Time ), CAST(@Date AS DATETIME) + CAST(@Time AS DATETIME)) FROM dbo.[DataGapTest] RETURN @timeInterval END
User-Defined string Functions MS SQL Server 2005 Transact-SQL SQLCLR (VB. Net, C#.Net, C++. Net)
Ladies and Gentlemen,
I am pleased to offer, free of charge, the following string functions MS SQL Server 2005 Transact-SQL SQLCLR (VB. Net, C#.Net, C++. Net):
AT(): Returns the beginning numeric position of the nth occurrence of a character expression within another character expression, counting from the leftmost character. RAT(): Returns the numeric position of the last (rightmost) occurrence of a character string within another character string. OCCURS(): Returns the number of times a character expression occurs within another character expression (including overlaps). OCCURS2(): Returns the number of times a character expression occurs within another character expression (excluding overlaps). PADL(): Returns a string from an expression, padded with spaces or characters to a specified length on the left side. PADR(): Returns a string from an expression, padded with spaces or characters to a specified length on the right side. PADC(): Returns a string from an expression, padded with spaces or characters to a specified length on the both sides. CHRTRAN(): Replaces each character in a character expression that matches a character in a second character expression with the corresponding character in a third character expression. STRTRAN(): Searches a character expression for occurrences of a second character expression, and then replaces each occurrence with a third character expression. Unlike a built-in function Replace, STRTRAN has three additional parameters. STRFILTER(): Removes all characters from a string except those specified. GETWORDCOUNT(): Counts the words in a string. GETWORDNUM(): Returns a specified word from a string. GETALLWORDS(): Inserts the words from a string into the table. PROPER(): Returns from a character expression a string capitalized as appropriate for proper names. RCHARINDEX(): Similar to the Transact-SQL function Charindex, with a Right search. ARABTOROMAN(): Returns the character Roman numeral equivalent of a specified numeric expression (from 1 to 3999). ROMANTOARAB(): Returns the number equivalent of a specified character Roman numeral expression (from I to MMMCMXCIX).
AT, PADL, PADR, CHRTRAN, PROPER: Similar to the Oracle functions PL/SQL INSTR, LPAD, RPAD, TRANSLATE, INITCAP.
Plus, there are CHM files in English, French, Spanish, German and Russian.
Plus, there are versions for MS SQL SERVER, SYBASE ASA, DB2, Oracle.
More than 8000 people have already downloaded my functions. I hope you will find them useful as well.
For more information about string UDFs MS SQL Server 2005 Transact-SQL SQLCLR (VB. Net, C#.Net, C++. Net) please visit the http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,54,33,29527
Please, download the file http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,2,29527
Our server has integrated security setup, upon startup of the server, we have a continuous flow of error msg: Login Failed: Reason: User '_' not defined as a valid user of trusted connection. The timing of these messages only around 30 seconds apart The only incident from Technet I can find is Q186314, but we don't have replication setup, anyone knows where I can look into ? I recycle the server but didn't help. I appreciate any help I can get .. Thanks.
I need to add days to a date field, my date field is as varchar(20041030 for example) and I need to add 4 days to it, my result should be 20041103, result field is also in varchar,how would I do that, can anyone pls help?
select a.RelocateID,a.DateEntered,a.CompanyID,b.FileClose dDate from test1 a inner join test b on (a.RelocateID = b.RelocateID) where CompanyID ='5710' and DateEntered >= '01/01/2004' and convert(varchar,FileClosedDate,101) < '31/12/2004'
Hi I was wondering if somebody could help me alter this query so I an calulate the difference in days in between DateEntered and FileClosedDate having the above criteria. I can't seem to be able to get the datediff function right in this particular example
Hiwhen inserting records into a table one of the fields is a date field. I am using the GETDATE() function to insert the date as the record is being inserted.when i retrieve an entire record from the table i want to be able to select this date, but also to get the number of days it has been since that record was inserted.eg: 3 daysif the record was inserted less than one day ago (<24 hrs ago) i would like it to return the number of hours. e.g. 22 hrsi dont want hours to be displayed if the days is >= 1.please can anyone guide me with this?thanks!
I have a table with 3 columns: Item# | Date | ItemAmount. Everyday there is a number of transactions entered. An Item# can only be entered once par day (if it has occurred that day).
What I want to do is to : retrieve the number of total days where an Item has been entered for more than 2 consecutive days (for the month).
Example: if item I022 has been entered Monday and wed, then ignore, but if it's been entered Mon, Tues then return 2, if Mon, Tues, Wed then return 3 because the days are consecutive.
I'm wanting to get the average number of days between orders in my orders tbl - so I've done a search and found the following sql coded that I have modified for my db tbl's and columns. But when I try and parse it - I get 'Incorrect syntax new the keyword Group' - what am I missing.
SELECT custId, AVG(invDate - priorDate) FROM(SELECT custId,invDate,LAG(invDate) OVER (PARTITION BY custId ORDER BY invDate)as priorDate FROM orders) Group BY custId
