I need to compare two consecutive rows (if BEGDA of second row is 1 day greater than ENDDA of first row then I need to pick First row BEGDA and 2nd row ENDDA)
I have following query which return me SP/Views and Functions script using:
select DEFINITION FROM .SYS.SQL_MODULESNow, the result looks like Create proc create procedure create proc create view create function
I need its result as:
Alter Procedure Alter Procedure Alter Procedure Alter View Alter Function
I used following
select replace(replace(replace(DEFINITION,'CREATE PROCEDURE','Alter Procedure'), 'create proc','Alter Procedure'),'create view','Alter View') FROM .SYS.SQL_MODULESto but it is checking fixed space like create<space>proc, how can i check if there are two or more spaces in between create view or create proc or create function, it should replace as i want?
as you can see, the records have a 30minutes time interval. i need to create a query to know if there are missing records in the table. so basically the result should be this:
I want to be able to compare today's date with the date that is in the database. Right now I have:
Select Field1, Field2 FROM table 1 Where Year(TS_Date)=Year('3/1/2006')and Month(TS_Date)=Month('3/1/2006')
Where I have to change the date every month. Is there a way to use GetDate or another type of code so it could automatically update every month. Any suggestions would be very greatful.
I have a need to create a table in a sql server database from C# code. The kicker is that the user must be able to specify the table and field names via the UI. I can do a bit of sanity checking but as long as they enter something reasonable I need to accept it. Normaly I always ADO parameters to sanitise any user parameters but they can't be applied to table and field names, only values. As far as I'm aware that leaves me needing to concatenate strings and that's something I usually avoid like the plague due to risk of SQL injection.
My actual question : Assuming string concatenation is my only way forward, how can I sanitise the values that would go into the table name and fieldname bits of a CREATE TABLE statement to ensure that injection can't occur? I've been pondering it and I think I just need to check for semi-colons. Without a semi-colon I don't think a user could inject an extra statement could they?
I have a string that contains series of parameters with separators.i need to split the parameters and its values as rows and columns.e.g string = "Param1 =3;param2=4,param4=testval;param6=11;..etc" here the paramerter can be anything and in any number not fixed parameters. Currently am using the below function and getting the parameters by each in select statement as mentioned below.
select [dbo].[rvlf_fn_GetParamValueWithIndex]('Param1=3;param2=4,param4=testval;param6=11;','param1=',';') as param1, [dbo].[rvlf_fn_GetParamValueWithIndex]('Param1=3;param2=4,param4=testval;param6=11;','param2=',';') as param2 CREATE FUNCTION [dbo].[rvlf_fn_GetParamValueWithIndex] ( @CustomProp varchar(max),
The database for our software stores dates and times in clarion format. I'm trying to write some custom reports in T-SQL and I need to convert these dates but it's giving me a lot of trouble. How do I query the dates and times and have the results shown as a "regular" date/time? Below is what I have so far which is really the very beginnings of this report. Basically on the results/output I need the ClarionDate and ClarionTime to be shown as typical date/time columns. I did some research on my own but I'm having a hard time grasping this. I believe they have to be pulled into a temp table and then converted?
SELECT A1.DATEOFACCESS ClarionDate, A1.TIMEOFACCESS ClarionTime, A2.NAME Event FROM dbo.History A1 JOIN dbo.SysEvents A2 ON A1.RSVD_Action = A2.RSVD_EVENTTYPE
HiI have a problem trying to compare a string value in a WHERE statement. Below is the sql statement. ALTER PROCEDURE dbo.StoredProcedure1(@oby char,@Area char,@Startrow INT,@Maxrow INT, @Minp INT,@Maxp INT,@Bed INT )
ASSELECT * FROM ( SELECT row_number() OVER (ORDER BY @oby DESC) AS rownum,Ref,Price,Area,Town,BedFROM [Houses] WHERE ([Price] >= @Minp) AND ([Price] <= @Maxp) AND ([Bed] >= @Bed) AND ([Area] = @Area)) AS AWHERE A.rownum BETWEEN (@Startrow) AND (@Startrow + @Maxrow) The problem is the Area variable if i enter it manually it works if i try and pass the variable in it doesn't work. If i change ([Area] = @Area) to ([Area] = 'The First Area') it is fine. The only problem i see is that the @Area has spaces, but i even tried passing 'The First Area' with the quotes and it still didnt work.Please help, its got to be something simple.Thanks In Advance
I am searching for a Powershell script which picks Windows Server names from SQL server table(eg: Instance.DB.tbServerList) & writes last reboot date to SQL server table(can be same or different table).
I have a query that accesses a series of events in a gridview and would like to only show those that are in the future. I am looking to compare the date/time column against today's date in my SQL query.Any ideas would be great.ThanksOrbital
So let's say I have a table Orders with columns: Order# and ReceiptDate. Order#'s may be duplicated (Could have same Order# with different ReceiptDate). I want to select Order#'s that go back 6 months from the last ReceiptDate for each Order#.
I can't just do something like: SELECT * FROM Orders WHERE ReceiptDate >= add_months(date,-6)
because there could be Order#'s whose last ReceiptDate was earlier than 6 months ago. I want to capture all of the instances of each Order# going back 6 months from each last ReceiptDate relative to each Order#.
I have this script that I'm trying to filter the results of the Job History to the day prior at 1800 hours.
It return dates prior to what I have in the WHERE clause.
What should the WHERE Clause look like?
USE msdb Go SELECT j.name JobName,h.step_name StepName, CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS dateTIME), 111) RunDate, STUFF(STUFF(RIGHT('000000' + CAST ( h.run_time AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') RunTime, h.run_duration StepDuration, case h.run_status when 0 then 'failed'
Hi, I have a table with 3 columns inside - record_id (int) - user_id (varchar) - login_date (date) it is a many-to-many relationship table that record login date of users
Now, I want if I want to COUNT the users who login before 31 May 2008, I would use SELECT COUNT(*) FROM table1 WHERE login_date < '2008-31-05'
That's works
But the problem is I want to split the result to - How many people visit the site only once? - How many people visit the site only twice? - How many people visit the site three time?
I would like to put a Clustered Index on a date column in a current heap, but one question/concern.This heap every month has thousands of rows deleted and even more added later. How much of an issue will this cause the Clustered Index as far as page splits? I was thinking Fill Factor of 70%.I would normally just test and still will on Dev box, but my Dev box is much smaller than production as far as power.
SELECT TOP 1000 *... case when Rownum<= datediff(day, salesdate, baseenddate) then DATEADD(mm, RowNum, salesdate) /*error at this point*/ else 0 end as subscriptionrowdate FROM Subsrow
Rownum is an integer type. DATEADD is the part when the error is but i dont know how to convert this to int
What I am trying to accomplish is to make a few extra columns with specified date ranges.
I have FY14 date range in the parameters at the top .. I would like to add a FY 15 column so the year would move up by 1.and also I need to add 2 more columns Prior year current month and This year, current month.
<code> DECLARE @Fy14_start datetime DECLARE @Fy14_end datetime SET @Fy14_start = '2013-07-01' SET @Fy14_end = '2014-06-30' SELECT x.ACCOUNT_NAME, X.STATUS_CODE, COUNT(X.PATIENT_CODE) AS FY14
I've used some info on here to generate random dates within a given range and also random times - independently they work fine, but I can't seem to join them into a single field of datetime. I'm not sure why. The following snippet works fine as two independent fields:
select CAST(CAST(ABS(CHECKSUM(NEWID()))%(780)+(33968) AS DATETIME) as DATE) as theDate, CAST(CAST(DATEADD(milliSECOND,ABS(CHECKSUM(NEWID()))%86400000 ,'00:00') AS TIME) as varchar(50)) as theTimeBut when I try to make it a single datetime field:
select CAST(cast(cast(CAST(ABS(CHECKSUM(NEWID()))%(780)+(33968) AS DATETIME) as date) as varchar(50)) + ' ' + cast(CAST(CAST(DATEADD(milliSECOND,ABS(CHECKSUM(NEWID()))%86400000 ,'00:00') AS TIME) as varchar(50)) as varchar(50)) as datetime)
Which returns with: Conversion failed when converting date and/or time from character string.
So what I am really looking for is a way to join those two values into a single datetime field... Or failing that that how to generate random dates within a range including random times...
I have two sets of dates to work with. One is an existing booking with a start and an end date. The other is a new booking with a start and an end date. I want to compare them and calculate how much overlap there is. If the overlap is over a certain amount (say 4 days), then I want to flag the user.
Is there any thing I can use in terms of a SQL query to assist in this comparison? I'm relatively new to SQL so I'm not entirely sure what functions and keywords are available to me to make this comparison.
Here's my statement below. What I'm trying to get is joining the name column in master.sys.databases with a sub query for the database name, file location and backup start date from the MSDB database. The reason for this, if a new database has never been backed up, It should be returning as a NULL value, which is my goal. However, I'm getting multiple results for the backups.
select CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,a.name,File_Location=b.physical_device_name,backup_start_date=max(backup_start_date) from master.sys.databases a left join(select c.database_name,backup_start_date=max(backup_start_date),b.physical_device_name from msdb.dbo.backupmediafamily b join msdb.dbo.backupset c on c.media_set_id=c.backup_set_id where c.type='D'
