Comparing Dates.

May 23, 2006

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, datestamp
FROM table
WHERE datestamp > DateAdd(d, 28, GetDate())

Thanks in advance,

Jake

View 2 Replies

Comparing Dates With Today Dates

Jun 21, 2005

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?

View 1 Replies View Related

Comparing Dates

Jun 26, 2006

Hello,

I am trying to retrieve the data that are not more than 3 months. How do I do this? The closest thing that I can do is...

(dbo.CLASSIFIEDADS.PostDate > CONVERT(DATETIME, '2006-03-26 00:00:00', 102))
I want to be able to put 3 months in there somehow...
Thanks in Advance!

View 2 Replies View Related

Comparing Dates In SQL

Jan 28, 2006

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?

View 7 Replies View Related

Comparing Dates

Jul 10, 2001

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.

Thanks
Sanjay.

View 5 Replies View Related

Comparing Dates

Dec 10, 2004

i am using sql server ver 7

i am trying to do

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

View 1 Replies View Related

Comparing Two Dates - Need Help!

Aug 25, 2005

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...

Any help pls?

View 5 Replies View Related

Comparing Dates

Dec 16, 2004

Does anyone know of a quick and easy to compare 2 datetime values based only on the month and year.

For example,

FromDate = 11/30/2004
ToDate = 12/30/2004

I just need to compare the 11/2004 to 12/2004 using function like <=, =, >=, etc.

Any suggestions?

Thanks

View 5 Replies View Related

Comparing 12-Hr Dates

Apr 4, 2008

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.

View 6 Replies View Related

Comparing Dates In SQL

Apr 9, 2008

hi, i have a table with persons(babies) and their date of birth. I want to delete the persons above 6 months old can u plse tell me the query ?

delete
from babies
where ......

View 7 Replies View Related

Comparing Dates

Jun 3, 2008

Hello,

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

View 4 Replies View Related

SQL QUERY Help Please.. (Comparing Dates)

Dec 1, 1998

hi,

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'.

i'd appreciate all the help i can get.

thanks,
sb

View 1 Replies View Related

Comparing Dates (older Than)

Feb 21, 2007

Hi Guys,

I am really stuck on exactly how I can do this. I can sudo it in my mind but can't figure out how I would code it.

What i want to do is select all records from a table that the log_date is older than 7 days.

If I was comparing a date i would imagine i would do something like

log_date < NOW()-7

I have looked at:

SELECT player_name, player_last_logon_date,DATEDIFF(day, player_last_logon_date, GETDATE()) AS NumberOfDays from player

select player_name, player_last_logon_date, datepart(dd,player_last_logon_date) from player

but I dont think either is really right. any and all advice would be great!

Thanks

View 2 Replies View Related

Problem Comparing Dates

Nov 16, 2005

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.

View 2 Replies View Related

Comparing Dates Of Different Lengths

Apr 9, 2008

Hi All,

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?

Thanks.
Frank

View 1 Replies View Related

Transact SQL :: Comparing Two Dates

Aug 4, 2015

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.

View 6 Replies View Related

Comparing The Dates In Different Rows

May 10, 2008

Hi,

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.

Is someone able to help me out please?

Thanks

View 8 Replies View Related

Comparing Dates In MS SQL 2000 Query

Feb 8, 2008

Hi,
I have a table that temporarily stores information. Before I insert new data in the table, I want to delete the previous day's data.

This is the query I'm using
Code:

q="DELETE FROM SearchTemp WHERE theDate < "&Date()
set RS2 = DB.Execute(q, ra, adCmdText)

I should say I'm using ASP & VBScript.

Problem is, this isn't deleting the previous day's dates.

The dates are stored in this format: m/d/yyyy.

I've searched through old posts but none of the solutions offered have worked for me so far. Anyone have any suggestions?

Thanks!

View 4 Replies View Related

Comparing Dates(Minutes, Hours Etc)

Jan 30, 2006

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

View 3 Replies View Related

Function Using Comparing Dates Not Working Right

Sep 12, 2006

Hi,

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.

View 3 Replies View Related

Problem Comparing Dates In Store Procedure

Mar 17, 2004

Hi,

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.

Thanks,

poc1010

alter Procedure GetOrdersBetweenDates

@startdatesmalldatetime=null,
@enddatesmalldatetime=null

as

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

exec getordersbetweendates '2/18/2003','02/19/2004'

View 1 Replies View Related

Creating A Boolean EvalExpression Comparing Dates

Aug 21, 2007

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.

Thanks!

View 6 Replies View Related

Want To Use Parameters To Filter For Dates Between Two (parameter, User-input) Dates

Mar 2, 2006

SQL 2005 Dev

How can I do this with Parameters? I can get a single parameter to filter for a single date (or even a combo list of the dates in DB). But I want my parameters to interact so that they specify a range. Is this possible?

View 3 Replies View Related

T-SQL (SS2K8) :: Calculate Sum Of Dates Minus Repetitive Dates

Jul 18, 2014

Today I have got one scenario to calculate the (sum of days difference minus(-) the dates if the same date is appearing both in assgn_dtm and complet_dtm)/* Here goes the table schema and sample data */

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[temp_tbl]') AND type in (N'U'))
DROP TABLE [dbo].[temp_tbl]
GO
CREATE TABLE [dbo].[temp_tbl](
[tbl_id] [bigint] NULL,
[cs_id] [int] NOT NULL,
[USERID] [int] NOT NULL,

[code]....

View 9 Replies View Related

T-SQL (SS2K8) :: Insert Into Table Dates In Between Two Dates

Feb 28, 2015

I have a table that has hotel guests and their start stay date and end stay date, i would like to insert into a new table the original information + add all days in between.

CREATE TABLE hotel_guests
(
[guest_name] [varchar](25) NULL,
[start_date] [date] NULL,
[end_date] [date] NULL,
[comment] [varchar](255) NULL

[code]...

View 7 Replies View Related

Generate Dates Between Two Dates

Nov 8, 2006

Hi,

I have a table which records employees'time-off records. There are 6 columns in this TimeOff table. They are RequestID, EmpName, StartDate, EndDate, Type, NumofDays. I have another table which has all the dates from 01/01/1950 to 01/01/2056.

I am trying write a query which lists all the dates between the timeoff startdate and enddate, including the the start and end dates, but my query so far only lists the start and end date in a timeoff record:

SELECT D.[Date], Datename(dw,D.[Date]) AS Weekday
FROM Dates D LEFT JOIN TimeOff T ON D.[Date] = T.OffStartDate OR D.[Date] = T.OffEndDate
WHERE (OffType = 'Sick Day' AND EmpName = 'Cat White') AND (D.[Date] BETWEEN T.StartDate AND T.EndDate)

Any advice will be greatly appreciated!

View 1 Replies View Related

Getting Individual Dates Between 2 Dates

Mar 4, 2008

Has anyone ever written a function to retrieve all individual dates between two given dates? Using DATEDIFF I can get the number of days between two dates. However I need to iterate through the days to identify weekend and holiday dates. Has anyone ever written a function to do this?

So, if select datediff(d,'07/01/2007','07/15/2007') as NumOfDays returns 14, I'd need to iterate through the 14 days and get the weekends and holidays. Would I have to use a cursor to iterate through the days?

View 13 Replies View Related

Leap Year Dates Causing Error When You Pull Dates For Non Leap Year

Feb 28, 2008

I'm trying to generate this query, that displays Budget Current Year , Actual Current Year and Prior Year Revenue. When It comes to the Budget and Actual everything works fine, however when I try to add the query for the Prior Year I get an error, and I realized that the leap date is causing the error

Here is what I'm trying to generate

InnCodeID
Quarterly
Monthly
Days
Period
Year
BARmRev
AARmRev
PYRmRev

ADDIS
Q1
Jan
1
1
2008
NULL
NULL

ADDIS
Q1
Jan
1
1
2008
3462.14
5107.65

ADDIS
Q1
Jan
1
1
2008
NULL
NULL

ADDIS
Q1
Jan
1
1
2008
NULL
NULL

Here is the error that I'm getting:

Code Snippet

Msg 242, Level 16, State 3, Line 1

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

(4834 row(s) affected)

Here is my Transact-SQL Syntax (summarized because I Couldn't post it):

SELECT

(CASE WHEN (CASE WHEN dbo.Trans.Tr_Dept = '10' AND dbo.Trans.TR_Main = '5120' AND dbo.Trans.tR_sub = '01'

AND Year(dbo.Trans.TR_Date) = Year(dbo.CurrentDate.CurrDate) AND dbo.trans.Datatype = '1'

THEN dbo.trans.Tr_Amount ELSE NULL END) IS NOT NULL THEN

(SELECT Trans1.TR_Amount

FROM dbo.Trans Trans1

WHERE Trans1.TR_Dept = '10' AND TR_Main = '5120' AND TR_Sub = '01' AND trans1.TR_Date = CAST((CAST(Month(dbo.Trans.TR_Date)

AS varchar(2)) + '/' + CAST(Day(dbo.Trans.TR_Date) AS varchar(2)) + '/' + CAST(Year(dbo.CurrentDate.CurrDate) AS varchar(4))) AS datetime)

AND Trans1.TR_Entity = dbo.Trans.TR_Entity AND trans1.datatype = dbo.Trans.DataType) ELSE NULL END) * - 1 AS BARmRev,

--AA Script Here AS AARmRev,

(CASE WHEN (CASE WHEN dbo.Trans.Tr_Dept = '10' AND dbo.Trans.TR_Main = '5120' AND dbo.Trans.tR_sub = '01' AND Year(dbo.Trans.TR_Date)

= Year(dbo.CurrentDate.CurrDate) AND dbo.trans.Datatype = '1' THEN dbo.trans.Tr_Amount ELSE NULL END) IS NOT NULL THEN

(SELECT SUM(Trans1.TR_Amount)

FROM dbo.Trans Trans1

WHERE RIGHT(RTRIM(Trans1.TR_Dept), 2) = '10' AND Trans1.TR_Main = '5120' AND Trans1.TR_Sub NOT BETWEEN '04' AND '05' AND

trans1.TR_Date = CAST((CAST(Month(dbo.Trans.TR_Date) AS varchar(2)) + '/' + CAST(Day(dbo.Trans.TR_Date) AS varchar(2))

+ '/' + CAST(Year(dbo.CurrentDate.CurrDate)-1 AS varchar(4))) AS datetime) AND Trans1.TR_Entity = dbo.Trans.TR_Entity AND

trans1.datatype = '0') ELSE NULL END) * - 1 AS PYRmRev

FROM dbo.EntityDef INNER JOIN

dbo.Trans ON dbo.EntityDef.ED_Property_ID = dbo.Trans.TR_Entity INNER JOIN

dbo.CurrentDate INNER JOIN

dbo.DimTime ON YEAR(dbo.DimTime.TimeDate) = YEAR(dbo.CurrentDate.CurrDate) ON dbo.Trans.TR_Date = dbo.DimTime.TimeDate

WHERE (dbo.EntityDef.ED_Property_ID = 'ADDIS')

ORDER BY dbo.EntityDef.ED_Property_ID, dbo.DimTime.TimeDate

I appreciate all your help

Thanks

View 13 Replies View Related

Please Help With Comparing Date

Jun 13, 2008

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?

How do I do that?

please help

Thank you

View 8 Replies View Related

Comparing Money

Aug 29, 2004

Hi,

I have a field in database money. When I enter value for it the amount entered is for example 20.000. How can I compare this value with noraml vaules i.e. like 20 in my search engine. Will I need to convert it to varchar and then compare it or is there some other way. Also if I need to convert it to varchar, how can I do it?

Thanks in advance,
Uday

View 6 Replies View Related

Problem With Comparing!!!

Jan 29, 2005

hi ... please look at it :

<< The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator. >>

ok ... it was my error mess, but i just wrote this code :

***********************************************************
Dim conPubs As SqlConnection
Dim cmdSelect As SqlCommand
Dim strSelect As String
conPubs = New SqlConnection ("Server=localhost;uid=ggggg;pwd=uuuu;database=eeee")
strSelect = "Select username From userpass Where username = '" & usernametxt.Text & "' And password = '" & passtxt.Text & "'"
cmdSelect = New SqlCommand( strSelect, conPubs )
conPubs.Open()

if Not(cmdSelect.ExecuteScalar() IS Nothing) then 'findeduser.text
response.redirect("welcome.aspx")
conPubs.Close()
else
msg.text="ID or Pass is incorrect!!"
conPubs.Close()
end if
***********************************************************

what is wrong?
can you help me?

me : mhamini@gmail.com

View 2 Replies View Related

Comparing Two Schemas

Mar 1, 2001

Hi Guys,

I'm afraid the schemas of the Development and Production versions of a database are in fact diferent.

How may I compare two database schemas?

Thanks in advance !-)

View 1 Replies View Related

Comparing Tables..

May 3, 2001

Hi ,
I have three tables T1 , T2 AND T3. T3 is having fields as a combination of T1 and T2 fields.How can I compare T1 and T2 field values with T3 FIELD VALUES.

View 4 Replies View Related