I have a date comparison situation in which I will have a column with a date and will have another value containing a GETDATE() minus two weeks. I want to be able to compare both dates and get the MIN date between the two. I'm not sure how to use the MIN(Date) in this scenario since the comparison won't be between two different columns, but between one column and a random date generated by the GETDATE() minus two weeks.
I want to know if there is a way to compare dates in the sql statement with dates that I input into a database and todays date. the datatype that I'm using is smalldatetime.The statement I used is:Select Date from Table where Date > 'Today.now'I get an errorCould this be done or is there another approach?
I am wondering how I would create a SELECT that will select the most recent date from one of two tables. For example, table1 has a field called LastUpdate and table 2 has a field called LastUpdate. I need to grab only the most recent date. I tried this using an inner join...but that didn't work because it only picks the lastupdate form one table only. talbe1 and table2 are tied by table2.table1id. Can anyone help?
Hi All, I have a database field (datestamp) which returns the date the records were inserted into the database. The datestamp was created with the now(); function in .net and is in the following format: 5/23/2006 2:27:45 AM I basically want to return all records that were inputted more than 28 days ago. I have had alook though some other posts and below is the closest query that i could find but unfortunately it does not work for me. SELECT id, datestampFROM table WHERE datestamp > DateAdd(d, 28, GetDate()) Thanks in advance, Jake
How do i compare 2 dates without comparing their Time portions. I have a Datetime column in my table which for example has values :-
6/6/2001 7:23:42 PM 6/6/2001 8:01:08 PM and so on....
I have a Stored Procedure in which i am trying to fetch values from this table. The SP has 2 DateTime parameters @DateFrom and @Dateto. This SP is called from a Web page. On the Web Page the user just enters mm/dd/yyyy values for the 2 Date parameters. Therefore the Time portions defaults to 12:00 AM. So when the user passes 6/6/2001 as Date From and 6/6/2001 as Date To values, he doesn't get back the 2 records mentioned above since the Time portion makes the 2 records fall out of the date range.
Is there a way to just do a comparison on the Date portions i.e. give me all the records for 6/6/2001 irrespective of the time. I don't wan't to do individual comparisons for Day then Month and then Year.
select * from independent_investment_certificate_temp
where independent_investment_certificate_temp.ticker_key = 31
and convert(varchar(40), cast(independent_investment_certificate_temp.maturity_date AS datetime),107) > convert(varchar(40), cast('2006-12-10 10:29:50.733' AS datetime),107)
independent_investment_certificate_temp.maturity_date value is '2005-01-21 00:00:00.000' the query above returns a value even though '2005-01-21 00:00:00.000' is clearly < '2006-12-10 10:29:50.733' but why can anyone plz explain.. how can i compare dates stripping tyhem of the time so that all dates on the same day will be equivalent regardless of the time
Hi, i got this query below, which I'd like to use to compare two sets of date...
select count(*) as rec, convert(char(6),dateEntered,13) as dateEntered from EmailTracker where chtype = '1' AND convert(char(12),dateEntered,13) >= '"& stdate &"' AND convert(char(12),dateEntered,13) <= '"& endate &"' group by convert(char(6),dateEntered,13)
the format of the stdate and dateEntered (a datetime datatype) looks like - 25 Aug 2005
right now, the query simply uses the first 2 letters of the date to fetch the record. That would not do the job as i want the thing to compare the whole lot! not just the 1st two letters...
I will try to explain my problem as best as possible without going into too much detail.
Basically I have two tables, AgentSchedule AgentPunchData
These tables both contain agent names.
The schedule table has a column called "SHEND" which is a DATETIME datatype which identifies when an agent shift should end.
The entries all have a date attached which means nothing, for example "12/30/1899 9:00:00 PM"
The PunchData table has a column called "LOGOUT" which is also a DATETIME, the entries will have the real date and look something like this: "3/28/2008 9:23:00 PM"
I need to strip the date out of these functions so I can compare the times. I need to query the databases and return any instances where the LOGOUT time is > the shift end time (SHEND).
So far nothing I've tried has been successful. I'm completely stumped, I've tried so many different things and nothing compares the two times correctly. SQL DATETIME is the biggest pain in the ass, I wish we had SQL 2008 so I could just use TIME.
I have some code that is trying to first find the first date of service that a person visited. Using the min function is not working. Is there another function? Right now I am still getting multiple rows for each person instead of just the first visit.
Then I want to be able to compare the first date of service to the date of birth and determine if the patient first visited before they were 2 years old. I can't figure out how to do this.
select distinct b.person_id,b.date_of_birth,min(a.enc_timestamp) from patient_encounter a join person b on a.person_id = b.person_id where b.date_of_birth >= '20060301' and b.date_of_birth <= '20060531' group by b.person_id,b.date_of_birth,a.enc_timestamp
i would like to create an SQL query, part of which involves comparing dates.
i have something like
Select ID, CONVERT(varchar(10), StartDate, 101), Name from Table WHERE StartDate .....
how can i compare the StartDate to something i have in the form of a string? for example, i have Date = 12/12/1998. and i would like to select where the StartDate in the DB = '12/12/1998'.
Basically, Im trying to return some data from an Access library database into a Java app using an SQL statement. Heres the code:
PHP Code:
Statement st = conn.createStatement(); String sql = "SELECT b.BorrowerNo, b.FirstName, b.LastName, COUNT(*) " + "FROM Borrowers b, Loans l WHERE l.BorrowerNo = b.BorrowerNo " + "GROUP BY b.BorrowerNo, b.FirstName, b.LastName ORDER BY COUNT(*) DESC";
ResultSet rs = st.executeQuery(sql);
This works fine, returns the BorrowerNo, FirstName, LastName, and COUNT is how many books they have loaned.
What I need it to do though, is just return loans that are late. I have a column in the Loans table called DateDueBack, so I think I need to compare this date to the current date in the WHERE statement.
I tried add this to my WHERE after l.BorrowerNo = b.BorrowerNo: AND l.DateDueBack > 16/11/2005
But this didn't work, it returned all the loans, regardless of whether they were before or after the date in the query.
Can anyone please help with this statement? I've never tried to compare dates before.
Is there a way to compare two dates fields that are different in length? For example, I have table1.datefield with a date of '040808' and table2.datefield with a date of '04082008'. Just comparing the two fields is not coming up equal. Has anyone had this situation before?
I have a table that holds pay rate changes with a field for the rate start date and a field for the rate end date. When an employee gets given a new pay rate, the existing rate is given an end date and a new row is added with the rate start date being the day following the end date of the old pay rate.
I need to identify the staff who have had a rate change within the past month, therefore an end date on one row that is within one month of the current month, and a start date on another row that is one day after an end date on a separate row and within one month of the current month.
guys - is this a decent query to pull all columns (dateCreate) that have a timestamp less than five minutes? i know its simple, but i've never done a date compare with minutes or hours in sql server thanks rik:o
select top 10 * from ptpuritm where datediff(MINUTE,dateCreate,getdate()) <=5
select top 10 * from ptpuritm where datediff(MINUTE,dateCreate,current_timestamp) <=5
I'm trying to write a function to return all notes with date. Sample data for 1 record=187189 as follows: iincidentid,iWorkNoteId,iSeqnum, dtEntryDate, workNoteAll 1871893440 1 2006-04-24 note1 1871893545 1 2006-06-22 note2 1871893547 1 2006-06-22 note3 1871893653 1 2006-08-10 note4 1871893653 2 2006-08-10 note5
funtion will return = 2006_08-10 note4 note5 for iincidentid=187189 ----------------------------------------------------- CREATE FUNCTION dbo.getIncidentNotesRev(@iIncidentID int) RETURNS varchar(8000) AS BEGIN declare @incidentId int declare @worknoteid int declare @worknotesaveid int declare @seqnum int declare @dtEntryDate smalldatetime declare @worknoteall varchar(8000) declare@allnotes varchar(8000) declare @currentWEDate smalldatetime declare @beginWEDate smalldatetime
select @allnotes='' select @currentWEDate=currentweekEndDate from csCurrentweekEndDate --get the current week end date select @beginWEDate = DATEADD(d, - 28, @currentWEDate)--get the last 4 weeks
declare CursorIncident CURSOR LOCAL FOR SELECT iIncidentId, iWorkNoteID, iSeqNum, dtEntryDate,worknoteall FROM dbo.rpt_weekly_prospect_status_vw where iIncidentId=@iIncidentID order by iWorkNoteId
OPEN CursorIncident FETCH NEXT FROM CursorIncident INTO @incidentId,@worknoteid,@seqnum,@dtEntryDate,@work noteall
--store 1st record of cursor select @worknotesaveid =@worknoteid WHILE (@@FETCH_STATUS=0) BEGIN if @dtEntryDate >=@beginWEDate AND @dtEntryDate <= @currentWEDate Begin if @worknotesaveid <> @worknoteid Begin Select @allnotes = @allnotes + @dtEntryDate + @worknoteall End else BEgin select @allnotes = @allnotes + @worknoteall End
select @worknotesaveid = @worknoteid --save next worknoteId End else Begin select @allnotes='' End FETCH NEXT FROM CursorIncident INTO @incidentId,@worknoteid,@seqnum,@dtEntryDate,@work noteall END --WHILE (@@FETCH_STATUS=0)
CLOSE CursorIncident DEALLOCATE CursorIncident
return @allnotes END
---------- Function not working right. I appreciate any help. Thanks in advance.
Below is my store procedure. I am trying to capture all orders between a startdate and a enddate. When I use the Where #1(see below- I have 2 where clauses) it allows me to go from one year to another. For example, startdate of 2/18/2003 - enddate of 2/19/2004 will return all orders in my table except for 2/19/2004. Probably because of the time portion. When I use the Where #2, it doesn't go back a full year. The startdate is reacting like 2/18/2004 and not 2003. The records I get are only for dates 2/18/2004-2/19/2004.
How do I create a where clause which will go back a year or more and include the enddate orders? Any help will be very much appreciated.
select o.orderid,(convert(varchar(12),o.orderdate,101)) as Date ,o.orderamt,o.shipamt,o.njtaxamt,o.totalamt ,c.bfirstname+' '+c.blastname as BName,c.baddress1,c.baddress2 ,c.bcity,c.bstate,c.bzip ,c.sfirstname+' '+c.slastname as SName,c.saddress1,c.saddress2 ,c.scity,c.sstate,c.szip from orders o inner join customers c on o.orderid=c.orderid 1.)where o.orderdate between @startdate and @enddate 2.)where convert(varchar(12),o.orderdate,101) between convert(varchar(12),@startdate,101) and convert(varchar(12),@enddate,101) order by o.orderid
To the experts in the field: There is probably a very simple solution that is avoiding my grasp.
I have a For Loop which I want to execute as long as a variable called BeforeRunDt = CurrentDate. Both are DateTime data types and I am using the following expression:
@BeforeRunDt==@CurrenDate
I get an error stating "Cannot convert expression value to propeerty type"
I understand that the result of the expression should be a boolean value but am just struggling on how to create it.
I need to compare records between two tables. There is no ID in the tables to do a simple join between them. So, what I'm looking for is: get the first record from table1 and read all record from table2 and give me back the most similar record. The String Distance is a predefined function.
Select a.table1 ,b.table2 from table1 a, table2 b where StringDistance (''a.table1,'b.table2') >90
I have this 40,000,000 rows table... I am trying to clean this 'Contacts' table since I know there are a lot of duplicates.
At first, I wanted to get a count of how many there are.
I need to compare records where these fields are matched:
MATCHED: (email, firstname) but not MATCH: (lastname, phone, mobile). MATCHED: (email, firstname, mobile) But not MATCH: (lastname, phone) MATCHED: (email, firstname, lastname) But not MATCH: (phone, mobile)
I have a scenario to compare previous records based on each ID columns. For each ID, there would be few records, I have a column called "compare", We have to compare all Compare 1 records with Compare 0 Records. If Dt is lesser or equal to comparing DT, then show 0. Else 1
We always only one Compare 0 records in my table, so all compare 1 columns will compare with only one row per ID
My tables look like
Declare @tab1 table (ID Varchar(3), Dt Date, Compare Int) Insert Into @tab1 values ('101','2015-07-01',0) Insert Into @tab1 values ('101','2015-07-02',1) Insert Into @tab1 values ('101','2015-07-03',1) Insert Into @tab1 values ('101','2015-07-01',1) Insert Into @tab1 values ('101','2015-06-30',1)
Insert Into @tab1 values ('102','2015-07-01',0) Insert Into @tab1 values ('102','2015-07-02',1) Insert Into @tab1 values ('102','2015-07-01',1)
select * from @tab1
1.) In the above scenario for ID = '101', we have 5 records, first record has Compare value 0, which mean all other 4 records need to compare with this record only
2.) If Compare 1 record's Dt is less or equal to Compare 0's DT, then show 0 in next column
3.) If Compare 1 record's Dt is greater than Compare 0's DT, then show 1 in next column
I need to check Table1 by Table2 only on NOT NULL cells and if all of them in the row match do not return that row as the result. In this case it will be:
SELECT a.Server, a.Databases, a.Users, a.Names FROM Table1 EXCEPT SELECT ISNULL(b.Server,c.Server), ISNULL(b.Databases,c.Databases), ISNULL(b.Users,c.Users), ISNULL(b.Names,c.Names) FROM Table2 AS a, Table1 AS c
But for many rows (like 100 000) it takes ages to get results, any better way to work on this?
I have a problem where I have 2 compare 2 records from the same table. This part looks easy but the problem is for a User there can be multiple records and I have 2 compare each record with its previous instance based on the timestamp. Not only I have to compare I have to perform some analysis. Below is the Table script and sample output.
Givens: All SQL Server 2008 or 2012 tools at your disposal.
Production database contains the following tables (simplified for example: constraints ignored, etc.) associated with a racing video game’s server.
-- A player of our game
-- Table greater than 10 million rows
CREATE TABLE [dbo].[User] ( [UserId] [bigint] NOT NULL ,[country] [int] NULL -- User’s home country ,[name] [nvarchar](15) NULL -- User’s displayable name (‘John’, ‘Bill’) ,[subscriptionTier] [int] NULL ) -- 0 == free, 1 == paid, for instance
Assume that rows get written into the event tables at a rate of 1,000 a minute,are never updated once written and currently are only read on a replica/reporting server.
Question Background: Write up a single query that would return the following: List of users and whose “TotalMoneyEarned” value ever grew (between logon events) at a rate of more than 1,000 per minute (we’d consider these suspicious and flag them for later investigation).
For instance, if the sample data were:
-- example of [Events.UserLogon] data -- not the query output we want
Event 1 is okay because there’s nothing to compare it against
Event 2 is okay because the TotalMoneyEarned only grew 500 in a minute
Event 3 should be flagged, as the value grew 1500 in a minute
Event 4 is okay, as it grew 7,000 in 8 minutes (< 1000 per minute)
Query Output (your query should return data in a format like this):
User Flagged Logon Time Rate Since Last Logon (money/minute) John 2010-10-16 00:21:56 1500 Dave 2010-10-16 00:30:50 3200 Bill 2010-10-16 00:35:23 1000
It is likely that you will need to create sample data for both the User and [Events.Logon] tables. We are looking for a single query that returns data like what is represented in Query Output.
DECLARE @Teams AS TABLE(Team VARCHAR(3)) INSERT INTO @Teams SELECT 'IND' UNION SELECT 'SA' UNION SELECT 'AUS' select Team from @Teams where Team > 'AUS'
[code]....
co-relation between comparison operators in WHERE Clause and the respective output.
I am trying to pull the records which are being affected i.e, comparing the values and if not same then populating the record.
I am using the below condition in where clause however i am getting runt time error as "Conversion failure when converting date and/or time from character string"
Condition in Where clause:
cast(isNull(tab1.Col1,'') as datetime) <> cast(isNull(tab2.col1,'') as datetime)
Got a query taking too much time because of lack of cross columns MAX/MIN functions. Consider a similar example where a View is required to reflect distribution of Water among different towns each having four different levels of distribution reservoir tanks of different sizes:In this case the basic table has columns like:
Now suppose I need a query to distribute QuantityPurchased in the Four additional Columns computed on the basis depending on the sizes declared in the last four fields,in the same order of preference.For example: I have to use IIFs to check: whether the quantity purchased is less than Tank_A if yes then Qty Purchased otherwise Tank_A_Size itself for Tank_A_Filled
then again IIF but this time to check:
Whether the quantity purchased less Tank_A_Filled (Which again needs to be calculated as above) is less than Tank_B if yes then Tank_A_Filled (Which again needs to be calculated as above) otherwise Tank_B_Size itself for Tank_B_Filled
I have a Column CCExp Varchar(7) that stores data like below
NULL '01/15' '02/15' '03/15' '04/15' '05/15'
[Code] ...
I am trying to get all expired cards which means, I am trying to get all cards which are behind sept 2015..like when I query I would like to get 8/15 and so on expired cards I am able to get the year , but with the below query I am getting all months less then 9 but i am missing 10,11,12 months cards values also I am having Conversion failed when converting the varchar value '#N' to data type int.
SELECT CustomerBillingInfoID , CCExp FROM CustomerBillingInfo WHERE LEFT( CCExp, 2) < MONTH(GETDATE()) AND RIGHT( CCExp, 2) <= RIGHT(YEAR(GETDATE()), 2) and CCExp NOT LIKE '#%' and CCExp not like 'NULL'
I need to write a query like get all the rows by comparing rmd with system todaydate and these must be with in startdate and enddate
id startdate enddate rmd 1 10-oct-15 16-oct-15 11-oct-15 2 11-oct-15 14-oct-15 11-oct-15 3 09-oct-15 11-oct-15 10-oct-15 4 07-oct-15 08-ot-15 07-oct-15 5 if sysdate(today) is 11-oct-15 then i need to get all the rows of 1, 2,3