How To Find Next 6 Months Dates Basing On Current Date

Jan 28, 2015

I need to find out All mondays / Tuesdays etc dates for next 6 months from the current date.

I have a table called DayOfWeek and the records are

1Sun
2Mon
3Tue
4Wed
5Thu
6Fri
7Sat

so if I pass "1" to the stored procedure, I need to find all Sunday dates for the next 6 months

And so if I pass "2" to the stored procedure, I need to find all Monday dates for the next 6 months...etc

View 5 Replies


ADVERTISEMENT

Find Months Between 2 Dates

Apr 2, 2008

hai friends,

I have a problem in finding month and year sequence between to date. one date is minimum date for example march 2005 and one maximum date is today's date april 2008. I need output of month between these years like this

March 2005

April 2005

May 2005

......

January 2006

...

December 2007

January 2008

.....
April 2008



Can any one help me to develop a stored procedure to produce this output...



Thanks in advance,
Abilina

View 2 Replies View Related

Date In Current Year Or Last Two Months

Apr 17, 2008

Hello

I had a requirement to filter records for the current year only... I went back to the user and explained that the logic wasn't sound... eg if somebody did the search early in January then they would probably get no results back. My suggestion was to go back two months if the current date is in January or february.

So, this is what I have.......




Code Snippet

where cs.startdate > case when year(cs.startdate) = year(getdate()) and month(getdate()) > 2 then
convert(char(4),year(getdate()))+'-01-01' else dateadd(month,-2,getdate()) end





Is there a neater/better way of coding this?

Jon

View 5 Replies View Related

Transact SQL :: Find Missing Months In A Table For The Earliest And Latest Start Dates Per ID Number?

Aug 27, 2015

I need to find the missing months in a table for the earliest and latest start dates per ID_No.  As an example:

create table #InputTable (ID_No int ,OccurMonth datetime)
insert into #InputTable (ID_No,OccurMonth) 
select 10, '2007-11-01' Union all
select 10, '2007-12-01' Union all
select 10, '2008-01-01' Union all
select 20, '2009-01-01' Union all
select 20, '2009-02-01' Union all
select 20, '2009-04-01' Union all
select 30, '2010-05-01' Union all
select 30, '2010-08-01' Union all
select 30, '2010-09-01' Union all
select 40, '2008-03-01'

For the above table, the answer should be:

ID_No OccurMonth
----- ----------
20 2009-02-01
30 2010-06-01
30 2010-07-01

1) don't include an ID column,

2) don't use the start date/end dates in the data or

3) use cursors, which are forbidden in my environment.

View 9 Replies View Related

SQL Server 2008 :: Count How Many Records Within 6 Months From Current Record Date

May 27, 2015

My data has 2 fields: Customer Telephone Number, Date of Visit.

Basically I want to add a field ([# of Visits]), which tells me what number of visit the current record is within 6 months.

Customer TN | Date of Visit | # of Visits (Within 6 month - 180 days)
1111 | 01-Jan-2015 | 1
1111 | 06-Jan-2015 | 2
1111 | 30-Jan-2015 | 3
1111 | 05-Apr-2015 | 4
1111 | 07-Jul-2015 | 3

As you can see, the last visit would counts as 3rd because 180 days from 07-Jul-2015 would be Jan-8-2015.

View 3 Replies View Related

T-SQL (SS2K8) :: Pass Dates For Previous Months Start And End Date

Mar 16, 2015

I've SSRS sales report to which I need to pass the dates for previous month's start date and end date which I am able to pass using below code. However, since the sales report has data from the past year(2014) I need to pass the dates for last year as well. The below code gives StartDate1 as 2015-02-01 and EndDate1 as 2015-02-28. I need to get the dates for past year like 2014-02-01 as StartDate2 and 2014-02-28 as EndDate2

SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000201', GETDATE()), '19000101') AS StartDate1,
DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '18991231') AS EndDate1

View 1 Replies View Related

Find Items And Datediff From Current Date And Print

Apr 4, 2012

What I want to do is I have a table checks which looks like-:

Items--Waitarea--Datecreated
1-------A1-------2011-10-12
1-------A1-------2011-10-13
1-------A1-------2011-10-14
1-------A1-------2011-10-15
1-------A2-------2011-10-16
1-------A2-------2011-10-18
2-------A2-------2011-10-19
2-------A2-------2011-11-17
2-------A2-------2011-12-17
3-------A2-------2012-01-17
3-------A2-------2012-01-18
3-------A3-------2012-01-19
3-------A1-------2012-01-20
4-------A2-------2012-01-21
4-------A2-------2012-01-22
4-------A2-------2012-01-31

What i need to find is items and datediff from current date to created date with respect to wait area. You will get more clear with example

For Eg. For items 1 the wait area changes from A1 to A2 at
2011-10-16 and remains A2 so i need to find the date differeence from
today date to 2011-10-16

For item 2 that wait area remains same for entire period that is
A2 so datediff from today date to 2011-10-19(last created date of that item)

For item 3 that wait area A2 then it becomes A3 then becomes A1
so need datediff from today to 2012-01-20

For item 4 that wait area A2 remains for the entire period
so need datediff from today-2012-01-21

The expected output wanted
items---daycount
1-----datediff(day,checks.datecreated,getdate()--here checks.datecreated will be 2011-10-16
2-----datediff(day,checks.datecreated,getdate()--here checks.datecreated will be 2011-10-19
3-----datediff(day,checks.datecreated,getdate()--here checks.datecreated will be 2012-01-20
4-----datediff(day,checks.datecreated,getdate()--here checks.datecreated will be 2012-01-21

View 9 Replies View Related

SQL 2012 :: Script To Find Current Date Modify Table List?

Jun 10, 2015

Need a script to capture current date modify table list(12 AM to 11:59 PM PST) in a database.

View 7 Replies View Related

Get Current Month And Previous 2 Months

Feb 10, 2014

I have table 'Open_Months' as shown below

Emp_IdMonthYear
00FBG 42013

I need current month and previous 2 months, for this i have concatinated month and year column into date formate, the query for this as shown below

SELECT cast(CONVERT(datetime, CONVERT(varchar(4), OM_Year) + RIGHT('0' + CONVERT(varchar(2), OM_Month), 2) + '01', 112) AS Datetime) from Open_Months where OM_Emp_ID = '00FBG'

Date
2013-04-01

From the above date I need to find current month and its previous 2 months.

View 1 Replies View Related

Get Records From Last 3 Months But Without Current Month

Apr 3, 2014

I have a problem with a date in my sql view, I need the records from the 3 last months but without the current month, if I execute my view right now I have the records from January to april but I just need from January to march, I must have always the 3 previous month but I don't know how I can do it

(dbo.frhkrg.fakdat >= DATEADD(MM, - 3, GETDATE()))

View 2 Replies View Related

Months Between 2 Dates

May 24, 2008

Hi Friends,

i need a Month name between 2 dates...

Example :

If i give start date(MM/dd/yyyy) is 03/20/2008
and End Date(MM/dd/yyyy) is 05/24/2008)

The Output Should be

March,
April,
May

How can i get this...

Thank you...

View 1 Replies View Related

Get Last Day Of Each Month For Months Between 2 Dates

Sep 14, 2012

I'm trying to write some code that will get me the last day of each month for the months that fall between 2 dates.

So if i have a table with a record with a begin date of 01-01-2012 and an end date of 09-14-2012, i would want a result set of the following 9 records:

01-31-2012
02-29-2012
03-31-2012
04-30-2012
05-31-2012
06-30-2012
07-31-2012
08-31-2012
09-30-2012

So i basically get the last day of each month between the dates listed, including the months that the dates are in themselves (inclusive list).

I have a date dimension table with 1 row for every day from 1990 to 2025. There is also a field with the Last day of Month for a given date. My issue is that I'm not sure how to write the T-SQL to get what I need.

View 4 Replies View Related

Nb Months Between 2 Dates (one In DB2 Format)

Jan 19, 2004

How can I get the number of months (or years or days...)
between the current_timestamp
an a date in the DB2 format ?

DateDB2 Char(26) = 2000-12-30-12.55.30.123456
DateSQL = GetDate() or Current_Timestamp

View 4 Replies View Related

Delete Dates &> 6 Months

May 6, 2004

Hello,

I have created a Table Called Log in SQL Server 7. I use this table as you can guess to log all transaction performed in my program. However, I would like my program to run an SQL Statement that will flush all entries in the table Log that are 6 months old from today's date.

I am unable to find the statement that will execute this operation.

Anyone has an idea please.

Thanks

Mark

View 14 Replies View Related

Query For Getting Missing Dates In Months

Feb 5, 2008

I have a table like FK_ID, Value, Date (here FK_ID is  foreign key)this table getting updated frequently by daily bases that means one record per one day(For example in January month it has maximum 31 records or minimum 0 records, in February it has maximum 28 or 29 or minimum 0 records, based on calender year)I need to query this table to get missing dates in between particular monthsfor example for one FK_ID has only 25 records in Jan 2008 month and in Feb 2008 it has 10 records , so i need to get those missing 6 dates from JAN month and 18 dates from FEB monthhow can i query this 

View 2 Replies View Related

Return All Months Within A Range Of Dates

Jun 18, 2004

I currently have a stored procedure that returns a list of dates based on a date range a user enters.


CREATE PROCEDURE sp_GetContactScheduleDates
@MonthFrom int,
@YearFrom int,
@MonthTo int,
@YearTo int,
@DaysInMonth int
AS
Select distinct s.ScheduleMonth, s.ScheduleYear
From OnCall_Schedules s
Where CAST(cast(s.ScheduleMonth as nvarchar) + '/' + cast(s.ScheduleDate as nvarchar) + '/' + cast(s.ScheduleYear as nvarchar) as smalldatetime)
>= CAST(cast(@MonthFrom as nvarchar) + '/' + cast('01' as nvarchar) + '/' + cast(@YearFrom as nvarchar) as smalldatetime)
And CAST(cast(s.ScheduleMonth as nvarchar) + '/' + cast(s.ScheduleDate as nvarchar) + '/' + cast(s.ScheduleYear as nvarchar) as smalldatetime)
<= CAST(cast(@MonthTo as nvarchar) + '/' + cast(@DaysInMonth as nvarchar) + '/' + cast(@YearTo as nvarchar) as smalldatetime)
Order by s.ScheduleYear, s.ScheduleMonth
GO


However, this only brings back those dates that are in the table. I need to get ALL dates within the range.

For example, the OnCall_Schedules table contains schedules that are saved by the user. If no one has ever saved a schedule at any time in May 2004 and the range of dates entered is January 2004 to June 2004, then May 2004 will not be returned. I need to get back all dates within that range regardless if it has something scheduled or not. How can this be done?

Note - I do not want to set up any dummy records or create a table with valid dates as the user will be allowed to choose any range of dates and we do not want to have to maintain anything.

Can some sort of function be used? What would the code look like?

View 2 Replies View Related

Getdate() To Select Dates Less Than 15 Months

Apr 7, 2008

Hi,
I need help. I want to select dates less than 15 months, as i am new to sql server , can anyone advise me.

I tried this query but it gave me the dates that are greater than 15 months

Selec calendardate from table
where calendardate < getdate()-457

Thanks

View 3 Replies View Related

Check If Date Is Current Date And Stay Current

Apr 25, 2008

Hi,

I'm making some sort of application where people can add their resume.
They also need something to add places where they have worked or currently are working.

I have a form where they can add this and i add this experience using the following stored procedure:


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_Insert_Experience]
@ExperienceId uniqueidentifier,
@ExperienceEmployee nvarchar(100),
@ExperienceFrom datetime,
@ExperienceUntil datetime,
@ExperienceQualifications nvarchar(250),
@ExperienceTechnologies nvarchar(250),
@ExperienceTasks nvarchar(250)
as
insert into Experiences
values(@ExperienceId,@ExperienceEmployee,@ExperienceFrom,@ExperienceUntil,@ExperienceQualifications,
@ExperienceTechnologies,@ExperienceTasks);


It must be possible to add the place where they currently are working. Then the ExperienceUntil has to be something like still going on. Then I decided that the user then had to set the current date.

But what I want is the following, I want that the ExperienceUntil keeps updating automatically, like everytime i get that record, it has to have current date.

Is this possible ?

But if the ExperienceUntil isn't the current date , it just had to take the supplied parameter date.

View 7 Replies View Related

Analysis :: Dynamic Set Needed For Last 12 Months Based On Current Month Selected By User

Sep 30, 2015

I need to create a set so that when a user selects a month in filter (say 201506) then it should give me a list of months from 201406 to 201506. Any appropriate MDX query.

View 7 Replies View Related

SQL Server 2012 :: Number Of Months Between Two Dates In YYYYMM Format

Jun 15, 2015

I am looking to calculate no of months between two dates which are in YYYYMM format.

Like no of months between 201505 and 201305

View 7 Replies View Related

SQL Server 2008 :: Calculate Number Of Months Between Dates For Multiple Records?

Oct 7, 2015

I have a challenge and I'm not sure the best route to go. Consider the following dataset.

I have a table of sales. The table has fields for customer number and date of sale. There are 1 - n records for a customer. What I want is a record per customer that has the customer number and the average number of months between purchases. For example, Customer 12345 has made 5 purchases.

CustomerNumber SalesDate
1234 05/15/2010
1234 10/24/2010
1234 02/20/2011
1234 05/02/2012
1234 12/20/2012

What I want to know is the average number of months between the purchases. And do this for each customer.

View 6 Replies View Related

Get Rows Between Current Date And Current Time

Jun 8, 2008

I've this query
SELECT
t1.ID, t1.Date, t1.Time,
t1.VALUE
FROM RESULT WHERE t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND
t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101)

Let's say, current date is 8 AUG 2005 and current time is 2045
So, i will get
ID | Date (this is datetime) | Time (this is integer) | Value
--------------------------------------------------
204 | 8/1/2005| 2359 | 90
205 | 8/1/2005| 2250 | 99
206 | 8/1/2005| 1950 | 88
...
...
207 | 8/7/2005| 1845 | 77
208 | 8/7/2005| 2255 | 77
209 | 8/7/2005| 2140 | 77

Can someone can show me to filter data between
t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND TIME>=CurrentTime
t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101) AND TIME<=CurrentTime

If current date is 8 AUG 2005 and current time is 2045, so the result shown as follow

ID | Date (this is datetime) | Time (this is integer) | Value
--------------------------------------------------
204 | 8/1/2005| 2359 | 90
205 | 8/1/2005| 2250 | 99
...
...
207 | 8/7/2005| 1845 | 77

I only have this query,
SELECT
t1.ID, t1.Date, t1.Time,
t1.VALUE
FROM RESULT WHERE t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND
t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101)

lack of idea to put the TIME condition.

Plz help me..

View 14 Replies View Related

Rolling Sum To Find Total Of 3 Previous Months Gas Production

Nov 5, 2012

I'm in need of creating a rolling sum to find the sum of 3 previous months gas production. Below is an example of the basic data I will be using to find the sum.

OperatorIDRRCID YearReported MonthReported YearCode DateReported GasProduction
71054993207201222012022012-02-01 89
71054993207201212012012012-01-01 721
710549932072011122011122011-12-01 353
710549932072011112011112011-11-01 409
710549932072011102011102011-10-01 544
71054993207201192011092011-09-01 707
71054993207201182011082011-08-01 506
71054993207201172011072011-07-01 536
71054993207201162011062011-06-01 472

I want the sum of the Year & Month of 2012-2 to be the sum of the gas production for 2012-1, 2011-12, and 2011-11.

View 1 Replies View Related

SQL Server 2008 :: Find Patients With Age 6 Months To 5 Years

Mar 3, 2015

formula to return patients ages 6 months to 5 years?

create table dbo.TEST
(
MRN varchar(10),

[Code]....

View 6 Replies View Related

T-SQL (SS2K8) :: Current Year Begin And End Dates

May 6, 2014

I am working on some payroll related code which currently has the following hard-coded CASE statement (I won't include the entire thing, it is lengthy):

AND b.TCDateTime BETWEEN '2013-01-01 0:00' and '2013-12-31 23:59'

I want to get rid of the hard coding, and have this use current year dates. So for 2014, it should reference rows where the TCDateTime is >='2014-01-01 0:00' AND <'2015-01-01 0:00'..I think the following would accomplish this, but would like confirmation that this is the 'best' way (and that it will work!)

SELECT CONVERT(DATETIME, CONVERT(CHAR(4), DATEPART(yyyy, GETDATE())) + '0101') AS curryrbegin
--output should be yyyy-01-01 00:00:00.0 where yyyy is current year
SELECT CONVERT(DATETIME, CONVERT(CHAR(4), DATEPART(yyyy + 1, GETDATE()))
+ '0101') AS nextyrbegin
--output s/b nextyear-01-01-00:00:00.0

Then, change the CASE statement to read:

AND (b.TCDateTime >= curryrbegin AND < nextyrbegin)

View 8 Replies View Related

Sql To Pull Appt Dates 2 Days Greater Than Current

Feb 4, 2008

I am pulling data that shows appt information on a patient:

FName,LName, PhoneNumber, ApptDate, ApptTime, and Status that equals Active. I need to pull this info everyday. How can I set this up in a stored procedure to pull all this information automatically on a daily basis where it pulls by Appt Date that is 2 days greater than the current date?

Thanks!

Thanks!
Lisa

View 9 Replies View Related

Generate Sequence Number Basing On Each Id

Mar 28, 2008

Hi all
anbody can help me writing sql code for this. All i need is to generate sequence basing on id_no
Ex: if ID=ABC(twice) in seq_col as abc --1
abc ---2
Tables which I have
Uniques_No ID_NO SEQ
---------------------------------------------
1 ABC
2 ABC
3 ABC
4 BBC
5 BBC


Expected results as below :
------------------

Uniques_No ID_NO SEQ
---------------------------------------------
1 ABC 1
2 ABC 2
3 ABC 3
4 BBC 1
5 BBC 2

Thanks in advance

View 4 Replies View Related

How To Get Date From 3 Months Ago

Oct 10, 2006

Hi all,How to get the date from the 3 months ago? So, if user enter a date (10/10/2006), how can I get to 07/10/2006.Thanks in advance...

View 3 Replies View Related

Need To CalcuThe Number Of Days Between The Current Date And A Stored Date

Sep 22, 2006

I need help with creating a query that compares the current date with a stored date field. If the difference between the two dates is greater or equal to 5 days for example, I need to be able to return these records. I am not sure if this can be done through a query alone but any help and suggestions would greatly be appreciated. Thanks in advance.

View 4 Replies View Related

Transact SQL :: How To Write A Query To Get Current Date Or End Of Month Date

Sep 29, 2015

how to write a query to get current date or end of month date if we pass year and month as input

Eg: if today date is 2015-09-29
if we pass year =2015 and month=09 then we have to get 2015-09-29
if we pass year =2015 and month=08 then we have to get 2015-08-31(for previous months we have to get EOMonth date & for current month we have to get current date).

View 3 Replies View Related

SELECT Where Date &>= 14 Months Ago

Nov 29, 2004

Hi,

I need a select statement with the condition that the date (a timestamp with format yyyy-mm-dd) is either equal to or longer than 14 months from today's date.

I am using SQL Server

Can anyone point me in the right diretion?

Thanks

View 1 Replies View Related

Date Difference In Months

Feb 24, 2006

Hi to all,

I want to make an sql query that will subtract two dates and the difference will be number in months.

i have this table TestTable with Fields ACost, Adate, AMonth

my formula is this X = (Now-Adate), should give me number in months
and Y = (ACost/AMonth)
i want to get the product of X and Y, (X*Y).

how can i make this in sql views?

can anyone help me with this?

thanks a lot!


best regards,

saldiboy

View 3 Replies View Related

Add A Number Of Months To Date

Oct 15, 2014

I have a simple query which returns a purchase date plus the number of months warranty an item has. What I need to do is add these months to the purchase date to get a warranty expiry date. What is the best way to do this? My query and results are below, I need an extra column in the results with that calculated warranty date:

select item, purchase_date, warranty_period
from inventory where item like '05MC%'

item purchase_date warranty_period
-------------------- ----------------------- ---------------
05MC0001 2014-07-22 00:00:00 36
05MC0002 2014-07-11 00:00:00 36

View 3 Replies View Related







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