How To Compare Date Periods ?

Oct 10, 2005

Hi,
I need to compare 2 periods (start date / end date) in order to find out if the first period overlaps or in included in the second period.

any idea ???

thanks

View 14 Replies


ADVERTISEMENT

Date Periods

Jun 20, 2007

Hi, this is my first post here, i'm working on a view wich I have to display the results by period.
1st period - Current month
2nd period - Last month
3rd period - Month before last
and so on.

How can I achieve this? of course I have a column wich has the date I want to use, I was thinking on semthing like:

case when DATEDIFF(M, TRXDATE, getdate()) <= 0 then ammount END as Period1
(TRXDATE and ammount are columns on my table)

Any ideas? would this work?

Thanks for your replies

View 2 Replies View Related

Number Of 'Periods' In A Date Range

Feb 10, 2006

Guys
Date brain killer - I have a startdate and enddate and need, for a given period length in months, to identify the number
of periods betwen the startdate and enddate
so for example

DECLARE @Periods TABLE
(
StartDate datetime,
Enddate datetime,
NumberOfMonthsInPeriod INT ,
TotPeriodsINT
)

-- Initial data
insert @Periods
(
StartDate ,
Enddate ,
NumberOfMonthsInPeriod ,
TotPeriods-- for this illustration, initialised to 0 but need to be UPDATEd as per detail below
)

select
'30-Sep-2005',
'10-Apr-2009',
1 -- 1 month period
0
union all
select
'30-Sep-2005',
'10-Apr-2009',
3 -- 3 month period
0
union all
select
'30-Sep-2005',
'10-Apr-2009',
6 -- 6 month period
0

The following rules regarding periods apply
Each 1 (NumberOfMonthsInPeriod) month period is as would be expected ie
01-Jan - 31 Jan
01-Feb - 28-Feb (ie 1st March - 1 day which would deal with leap years - dateadd(...)
and so on to December

Each 3 (NumberOfMonthsInPeriod) month period is one of the following 'bands' per year
01-Jan - 31-Mar
01-Apr - 30-Jun
01-Jul - 30-Sep
01-Oct - 31-Dec

Each 6 (NumberOfMonthsInPeriod) month period is one of the following
01-Jan - 30-Jun
01-Jul - 31-Dec

I need to derive TotPeriods as follows:

For the row where NumberOfMonthsInPeriod = 1, the first period ie the one the start date falls within is 01-Sep-2005 to 30-Sep-2005, second is 01-Oct-2005 - 31-Oct-2005 and so on until
last period ie the one the end datye falls within is 01-Apr-2009 - 30-Apr-2009, a TotPeriods value of 44

For the row where NumberOfMonthsInPeriod = 3, first period is 01-Jul-2005 to 30-Sep-2005, second is 01-Oct-2005 - 31-Dec-2005 and so on until
last period is 01-Apr-2009 - 30-Jun-2009, a TotPeriods value of 16

For the row where NumberOfMonthsInPeriod = 6, first period is 01-Jul-2005 to 31-Dec-2005, second is 01-Jan-2006 - 30-Jun-2006 and so on until
last period is 01-Jan-2009 - 30-Jun-2009, a TotPeriods value of 8

Hope this is clear and thanks in advance

View 3 Replies View Related

Comparing Two Date Periods For Overlapping

Nov 9, 2006

hi guys,

i have a booking table which has the following columns...

booking
-------------------------------------------
dCheckin (format 11/9/2006 12:00:00 AM)
dCheckout (format 11/11/2006 12:00:00 AM)

when a new booking is entered, we want to make sure that the period entered does not conflict with an existing record.

not sure how to go about building the query required. any help would be greatly appreciated.

mike

View 4 Replies View Related

How To Compute Biweekly Periods When Date Is Passed In As Parameter

Oct 7, 2014

How can I compute the biweekly periods when a date is passed in as a parameter, for example if the user enters 9/12/2014 I should get:

Friday /2nd half9/12/2014
Saturday9/13/2014
Sunday9/14/2014
Monday9/15/2014
Tuesday9/16/2014
Wednesday9/17/2014
Thursday9/18/2014
Friday/1st half 9/19/2014
Friday/2nd half9/19/2014
Saturday9/20/2014
Sunday9/21/2014
Monday9/22/2014
Tuesday9/23/2014
Wednesday 9/24/2014
Thursday9/25/2014
Friday/1st half 9/26/2014

View 2 Replies View Related

SQL Server 2012 :: CTE To Return All Periods Between First And Last Transaction Date

Jan 21, 2014

I'm trying to create a cte to return a list of lots and every period between the first and last transaction date for each lot. I've gotten this far:

SELECTIntLotKey
,DATEPART(YYYY, StartDate)StartYear
,DATEPART(MM, StartDate)StartPeriod
,DATEPART(YYYY, EndDate)EndYear
,DATEPART(MM, EndDate)EndPeriod

[Code] ....

This gives me the following results:

IntLotKeyStartYearStartPeriodEndYearEndPeriod
271532013120135
28468201312201312
2846920131201312
2847020131201312
28472201312201312
593022013120131
593032013120131

Now what I need is something that looks like this:

LotKeyYearPeriod
2715320131
2715320132
2715320133
2715320134
2715320135

[Code] .....

Some lots may not have any transactions for some of the periods between the start and end dates but I need to report every period between the start and end period for each lot. I have a period table that I thought I could use but haven't come up with a way to get the results I'm after.

View 9 Replies View Related

Transact SQL :: Date Value Between One Of The Calendar Periods In Multiple Rows?

Aug 20, 2015

Question: How to determine if a date value was between one of the date periods that appear in multiple rows?

Background: We have a table of "license valid" periods, wherein each license can have one or more rows.  (As you know, a driver's license can be started, expired, renewed, suspended, reinstated, revoked, etc.)  Instead of of having a license activity table--from which valid license periods could be extrapolated--we store just the periods for which a license was valid.

My task is to take a list of licenses and specific dates and determine if each license was valid as of that date, returning either true or false.  What is the best way to accomplish this?

DECLARE @ValidityInQuestion TABLE (
LicenseID int
, DateValidityInQuestion date);

DECLARE @LicenseValidPeriods TABLE (
LicenseID int
, BeginDate date
, EndDate date);

[Code] ...

 How then do I query both tables in order to get the same result that results from the following query?

SELECT
12345 AS LicenseID
, '2015-01-15' AS DateValidityInQuestion
, 1 AS LicenseActive
UNION
SELECT
67890
, '2015-02-04'
, 0;

I assume I need to join on the LicenseID columns, but my understanding stops there.

View 4 Replies View Related

Super Urgent Codes To Compare Datafield Date With Today's Date

Nov 15, 2007

Hi, I really need this help urgently.
I need to send an email when the dueDate(field name in database) is equal to today's date... I have come out with this code with the help of impathan(jimmy i did not use ur code cos i not very sure sry)... below is the code with no error... but it jus wun send email...
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load, Me.Load
con1.Open()
Dim cmd As New SqlCommand
cmd.CommandText = "select * from custTransaction where convert(datetime,dueDate,101) = convert(datetime,GetDate(),101)"
'Set the connect the command object should use
cmd.Connection = con1Dim da As New SqlDataAdapter(cmd)Dim ds As New DataSet
da.Fill(ds)
con1.Close()
If Not ds.Tables(0) Is Nothing ThenIf ds.Tables(0).Rows.Count > 0 Then
 Dim objEmail As MailMessage = New MailMessage
objEmail.From = New MailAddress("my@email.com.sg")objEmail.To.Add(New MailAddress("my@email.com.sg"))
objEmail.Subject = "Due Date Reaching"objEmail.Body = Session("dueName")
objEmail.Priority = MailPriority.Normal
Dim SmtpMail As New SmtpClient("servername")
SmtpMail.Send(objEmail)
End If
End If
End Sub
Note: I am veri sure that database has the data field dueDate with the value 11/16/2007 smalltimedate(mm/dd/yyyy)
Realli veri urgent Thanks so much for ur'll help

View 8 Replies View Related

How To Get Todays Date In Format YY/MM/DD And To Compare It To Another Date Passed Into The Sql

Dec 11, 2007

I need to do the following and am hoping someone can help me out.
I have C#(asp.net app) that will call a stored procedure. The C# will pass in a date to thestored procedure. The date is in the format YY/MM/DD. Once inside of the stored procedure, the datepassed into the stored proc needs to be compared to todays date. Todays date must be determined inthe SQL.
So basically here is my pseudo code for what I am trying to accomplish. Basically I just am afterthe comparison of the two values:
If @BeginDate < TodaysDate
The difficult part is how to obtain the value for "TodaysDate"
Taking into consideration that "TodaysDate" should probably be in the format of YY/MM/DD considering that is how the date it is to be compared with is being passed in.
Can someone please code this out for me in Microsoft SQL. I would be forever grateful.

View 1 Replies View Related

How Do I Compare Date In MS Sql

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 have login in the last 0-5 days (based on the input (31 MAY 2008))
- How many people have login in the last 6-10 days (based on the input (31 MAY 2008))
 
How do I do that?
 
Please help
 

View 5 Replies View Related

Compare Date Value In SQL SP

Jun 20, 2008

I have table:ArticleIDSubjectBodyDateThe Date field have data:12.02.200512.02.198912.02.156314.09.134530.12.345I need to show Articles wiich have date today. Example:If today is 12.02.2008 I should show only Date with value 12.02. The results:12.02.2005
12.02.1989
12.02.1563 I try something with DAY, Returns an integer representing the day datepart of the specified date but it's doesn't work.Help :)

View 5 Replies View Related

Date Compare

Feb 2, 2005

How do you compare date in sql, it is giving me errors,

I want to view all that logged onto my system Today
(*Yes I want to use dyn query for this *)

LastLogin is of type DateTime

SELECT @myStatement = ' SELECT LastLogin, surname
FROM #TEMP WHERE ((LastLogin ='+CONVERT(datetime, @LastLogin)+')

exec(@myStatement)

Thanks in advance

View 4 Replies View Related

SQL Compare Date

Nov 4, 2005

I want to know how can I write a sql to compare 2 dates which can run in SQL Query Analyzedeclare @date1 datetime, @date2 datetime
set @date1 = '2005-01-22 12:00:05'set @date2 = '2005-01-22 13:05:01'how can I compare whether @date1 = @date2 in terms of (YYYY-MM-DD) only?

View 3 Replies View Related

How To Compare 2 Date By Month?

Jul 30, 2007

I'm doing DTS, Here is one of my Sql Query in DTS
 
Select * From ZT_DailyRpt_Detail Where isNumeric(TenDayDate) = 1 And TenDayDate < Convert(varchar(10),DateAdd(Month,-CAST                              ((SELECT         keepmonth                                  FROM             zt_databackup a, zt_biller b                                  WHERE         a.companycode = b.companycode AND                                                             zt_DailyRpt_Detail.biller_code = b.billercode) AS int),GetDate()),112)
 
the sub query ã€?SELECT         keepmonth                                  FROM             zt_databackup a, zt_biller b                                  WHERE         a.companycode = b.companycode AND                                                             zt_DailyRpt_Detail.biller_code = b.billercode) AS int】will return a value 6 or 12
for the reason, every month have differnet days, ( some have 31 days , some are 30 days ) I don't want to check the day of date , only to compare month 
if sub query return "6" and I do DTS on 2007/07/29 , will select  date which TenDayDate< 2007/01 not  TenDayDate<2007/01/29 ( don't want to check the day of date)
 
does my query correct? if not can you correct it for me? thank you very much
 

View 2 Replies View Related

SQL Statement To Compare Between Date

Nov 30, 2007

Hi Guys,
I have a table "HOLIDAY" with column "HOLIDAYSTART" and "HOLIDAYEND" in date/time type.User can apply new holiday on a form with value "STARTDATE" and "ENDDATE", but how do I check if the new holiday dates already exist in the table??
I looked around and found you can use something similar to following to check for single date,select * from holiday where ('1-12-2007' >= HOLIDAYSTART) AND ('1-12-2007' <= HOLIDAYEND)
but how would I do it with my situation above with between 2 dates? any help? Thanks in advance.

View 3 Replies View Related

How Do You Compare Date Values

Aug 2, 2004

I'm stuck trying to compare date values from users selection against a database. I also need to add a condition statement on if the results return no match. How would I do that?

Also, I'm not even sure if my SELECT statement is right. The user will hit a date that will be in format m/d/yyyy and that will be compared to see if it exist in the database. The datebase column "date_created" is default with date/time( 8 ). But when I compare the to together, nothing returns back. The page loads successfully, but no results from the database.

Here is my code:
dim conpubs as sqlconnection
dim cmdSelectAuthors as sqlcommand
dim dtrAuthors As sqlreader

conpubs = New sqlconnection(configurationsettings.appSettings("STD"))
conpubs.open()
cmdSelectAuthors = New sqlcommand("Select * From HotNews WHERE date_created=" & y, conpubs)
dtrAuthors = cmdSelectAuthors.ExecuteReader()
Response.Write("<table><tr><td>")

While dtrAuthors.Read()
Response.Write(dtrAuthors("title") & "<br>")
End While

Response.Write("</td></tr></table>")

dtrAuthors.close()
conPubs.close()

View 1 Replies View Related

Compare Date Range

Aug 2, 2000

I want to write a query to retrieve all the items that are
order between 2 dates range, 07/30/2000 to 08/02/2000, any suggestions are
greatly appreciated,

View 4 Replies View Related

Compare Varchar Date

Jan 10, 2006

I have a preexisting database that has dates in the format mm/dd/yyyy but it is set up as varcar. How can I apply numeric operators to it to find how old someone is from todays date? I then need to take that value and populate another column on the same row.

View 1 Replies View Related

SQL- How To Compare Max Date Across Fields

Jan 4, 2006

Hello, I am wondering how to select the max date from a database view containing multiple date fields for a particular record. I am rusty at SQL and can't seem to get started. Here's an example of the data:

PRCL_IDPROJ_STRT_DTORD_DT ...
1242/3/20063/5/2006
5206/3/20068/2/2006
6412/31/2005
1872/14/20063/16/2006

I need to be able to compare the max date for each PRCL_ID record ("where prcl_id="). In the example above, prcl_id 124 has a max date of 3/5/2006, and prcl_id 520 has a max date of 8/2/2006, etc. The results of the SQL query should return the PRCL_ID and the max date.

Example query results:

PRCL_ID ORD_DT
124 3/5/2006

Please let me know if you can help or if I can provide more info.

Thanks,
Alexkav

View 6 Replies View Related

How Do You Compare Date Values

Aug 2, 2004

I'm stuck trying to compare date values from users selection against a database. I also need to add a condition statement on if the results return no match. How would I do that?

Also, I'm not even sure if my SELECT statement is right. The user will hit a date that will be in format m/d/yyyy and that will be compared to see if it exist in the database. The datebase column "date_created" is default with date/time( 8 ). But when I compare the to together, nothing returns back. The page loads successfully, but no results from the database.

Here is my code:
dim conpubs as sqlconnection
dim cmdSelectAuthors as sqlcommand
dim dtrAuthors As sqlreader

conpubs = New sqlconnection(configurationsettings.appSettings("STD"))
conpubs.open()
cmdSelectAuthors = New sqlcommand("Select * From HotNews WHERE date_created=" & y, conpubs)
dtrAuthors = cmdSelectAuthors.ExecuteReader()
Response.Write("<table><tr><td>")

While dtrAuthors.Read()
Response.Write(dtrAuthors("title") & "<br>")
End While

Response.Write("</td></tr></table>")

dtrAuthors.close()
conPubs.close()

View 1 Replies View Related

Compare With Current Date

Sep 25, 2013

From my table:

convert(varchar,Rec.expdate,101) as exp:

01/19/2038
08/12/2013
08/12/2013

I only want record is >= the current date but looks like my syntax is not working right because I got nothing return instead of 01/19/2038.

and convert(varchar,Rec.expdate,101) >=convert(varchar,getdate(),101)

View 3 Replies View Related

How To Compare The Date In SQL Server 2000?

May 16, 2004

Hi all,
I have encountered a problem that I can't compare the date in SQL Server 2000.
But I know that maybe I have used "datetime" as data type for that field.
My question is that, how can I get ONLY the "date" in stead of "datetime" from the database?
Your attention is appreciated!
Thank you.

Regards,
katszeto

View 8 Replies View Related

SQL Server 2008 :: How To Compare The Date Column

Mar 30, 2015

I need to compare the date column. The date column is 2012-01-22 in this format.

So I need to convert getdate() into this format yyyy-mm-dd.

What is the style number I have to use?

I used the link [URL] but I didn't see this format.

View 5 Replies View Related

How To Compare Current Date With Sql Db Datetime Data Type

Nov 2, 2003

Hi,

I am using one datetime data type ( name: date_added ) and getdate() as default value. I want to display only those records added today. How I can compare current date with date_added.

Thanks
Manoj

View 1 Replies View Related

SSIS - Conditional Split, Date Compare Problem!! Please Help

Nov 6, 2007

Right the answer is probably simple but the Internet and books and everything has been no joy to me whatsoever.

I want to split my data stream based on the date. So I want to use a conditional split object to do this.

I entered the following as my case date_created > (DT_DBTIMESTAMP)"01/10/2000"

When I move off the line it stays black so appears to be okay, yet when I run my package it says it is not a boolean result and fails. Can anyone please tell me what I am doing wrong.


Also I cannot filter in the source call due to the sheer amount of work being done on the data before the split.

Thanks in advance for any help

View 5 Replies View Related

Transact SQL :: Compare Two Table With Multiple Date Comparison

Oct 20, 2015

I need to take all records from table @A where ID = 1. Also i need to process the records with datewise from table @A. Here is the table structure

DECLARE @A TABLE (ID INT, ACCOUNT VARCHAR(10), EFFDT DATE)
INSERT INTO @A VALUES (1,'AAA','2015-10-01')
INSERT INTO @A VALUES (1,'BBB','2015-10-01')
INSERT INTO @A VALUES (1,'CCC','2015-10-01')
INSERT INTO @A VALUES (1,'AAA','2015-10-05')
INSERT INTO @A VALUES (1,'DDD','2015-10-01')
INSERT INTO @A VALUES (2,'AAA','2015-10-02')
INSERT INTO @A VALUES (2,'BBB','2015-10-02')
INSERT INTO @A VALUES (2,'CCC','2015-10-02')
INSERT INTO @A VALUES (2,'DDD','2015-10-02')

[code]...

how to achieve this in SQL query, i cannot use CTE or temp table as i need to use this code in another tool, it has to be single query, can use subquery or join would be better.

View 4 Replies View Related

Compare With Multiple Date Values Passed Into Stored Procedure?

Dec 14, 2012

I have a scenario where I need to compare a single DateTime field in a stored procedure against multiple values passed into the proc.So I was thinking I could pass in the DateTime values into the stored procedure with a User Defined Table type ... and then in the stored procedure I would need to run through that table of values and compare against the CreatedWhenUTC value.I could have the following queries for example:

WHERE CreatedWhenUTC <= dateValue1 OR CreatedWhenUTC <= dateValue2 OR CreatedWhenUTC <= dateValue 3

The <= is determined by another operator param passed in. So the query could also be:

WHERE CreatedWhenUTC > dateValue1 OR CreatedWhenUTC > dateValue2 OR CreateWhenUTC > dateValue3 OR CreateWhenUTC > dateValue4

View 3 Replies View Related

Transact SQL :: Two Row Serial Number Date Compare And Take More Than 5 Hours Between Two Rows

Jul 1, 2015

A vehicle loading confirm after that what time its gated out so i want to take the time duration between finish loading and gate out, find sample table records , i want to take more than 5 hrs difference between finish loading and gate out.

tld_tripno
tld_sno
tld_activitycode
tld_location
tld_actualdate

TLM3004242015

[Code] .....

I want to take the result like this 

Tld_tripno
Finish Loading
Gate Out
Date and Time difference

TLM3004242015
2015-05-11 19:58:00
2015-05-12 08:42:00
12:44:00

View 10 Replies View Related

24h/7d Time Periods TO 10h/5d Time Periods

May 4, 2006

I have to do alot of date calculations. For some calculations, I can use the datediff or dateadd function to get a Time Period between 2 dates.

Now for some dates I need to calculate the time between 2 dates BUT:

a week = 5 days starting from Monday to Friday
a day starts at 8AM and ends at 6PM (so a day is 10 hours)

You can probably calculate this manually but what about summer to winter hour and the month Februari when it has 29 days etc.

So I was thinking ... is it possible to calculate the Time Period for weeks with 24h a day / 7 days AND then transform that Time Period to a time period for weeks with 10h a day / 5 days ?

If anyone has an idea to solve this, either with functions or an other way, please let me know! Thanks

View 3 Replies View Related

A Report Different Time Periods

Jan 10, 2008

There are three tables Loans, CDs and Account.

I want to create a report based on above three tables
It looks like as follows;
















Program Summary

Yesterday/Current
MTD
Previous Month
YTD




Amount
Number
Amount
Number
Amount
Number
Amount
Number

Portfolio












Loans Commenced

$
#
$
#
$
#
$
#


Loans Closed

$
#
$
#
$
#
$
#


CDs Commenced

$
#
$
#
$
#
$
#


CDs Closed

$
#
$
#
$
#
$
#















Loan table - Amount, State
CD - Amount, State
Account - CommencedOnDate(date time), closeondate(date time)

Yesterday - Loans/CDs started yesterday(calander date..not last 24 hours)
MTD - " " First of this month to yesterday
Previous Month - " " during previous month
YTD - " " Loans/CDs started from 1 of this year to yesterday

Loan and cd states are Commenced and Closed

Based on this I have to create a report. But this time format seems to be really complex.
Can anyone help me to write the query pls?

View 1 Replies View Related

Subtracting Periods That Are In YYYYMM Format?

Mar 28, 2014

I have a Column in my data that gives a financial period value in the YYYYMM format. i.e. an asset was re-valued in a particular period for example 201301. I need to find out the number(count) of periods(months) between another given period for example current period (201403) and the period provided in the table i.e. 201301.

Is this possible in the SQL Database?

View 2 Replies View Related

Selecting From Multiple Time Periods

Apr 11, 2008

Your help is greatly appreciated. How do you query each one of the following:

1) SELECT FieldValue WHERE DateTime is from 8:30AM - 12:00PM and 6:00PM - 9:30PM on Mondays thru Fridays (i.e. exclude Sat & Sun) for the whole month of January 2008.

2) SELECT FieldValue WHERE DateTime is from 9:30PM - 8:30AM on Mondays thru Fridays, AND ALL-DAY on Saturdays & Sundays for the whole month of January 2008.

Thank you in advance.

View 8 Replies View Related

Grouping Data Into Periods For Reporting

Aug 17, 2007

Hi there.

I am working on a set of reports where I am summing/averaging data elements based on what period they are in. For example, the report output should look something like this:









Period
Sum

May '07
41

April '07
14

Q2 '07
55

March '07
36

February '07
28

January '07
22

Q1 '07
86

June '07
N/A

YTD '07
141

December '06
33

November '06
27

October '06
42

Q4 '06
102

September '06
58

August '06
84

July '06
52

Q3 '06
194

June '06
40

May '06
41

April '06
14

Q2 '06
95

March '06
67

February '06
38

January '06
N/A

Q1 '06
105

YTD '06
496


For each of the items I am summing, all I have is a datetime of when the event happened. This is a relational database (not a cube), so I am struggling with how to create the 'buckets' based on period. I think the best way is to dynamically create the buckets based on a given date. Is there a way in RS that it can do this bucketing for you?

Thanks, Mike

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved