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


ADVERTISEMENT

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

Expression Where Date Is 3 Months Ago?

Apr 17, 2008

What is the correct code for an expression where my "datetime" field is greater then or equal to 3 months ago?

View 1 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

SQL 2012 :: How To Calculate Date Difference In Months

Jan 23, 2015

I would like to calculate difference between end_date and current date in Months.And also how we can calculate the sum of difference in months between start_date and end_date for each ID?

CREATE TABLE datedifference (
id INT
,start_date INT
,end_date INT
)
INSERT INTO datedifference VALUES (10,20091202,20100629)
INSERT INTO datedifference VALUES (20,20071202,20090330)
INSERT INTO datedifference VALUES (30,20051202,20101031)

View 6 Replies View Related

T-SQL (SS2K8) :: Date Logic - How To Get Previous 6 Months Data

Sep 29, 2014

I am working in sqlserver 2008 R2 and below is my sample research query. I am trying to get previous 6 months data.

WITH CutomMonths
AS (
SELECT UPPER(convert(VARCHAR(3), datename(month, DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N, 0)))) Month
,DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N, 0) startdate
,DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N + 1, 0) enddate
FROM (
VALUES (1)

[Code] ...

Current output what i am getting:

Expected Output:

I found why the April month i didn't get the $20 because the startdate of my perks CTE '2014-04-03'. If it is '2014-04-01' then i will get the expected output.

But i should not change the the date on perks. How to neglect this date issue and consider the month instead to get the expected output.

View 6 Replies View Related

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 View Related

Transact SQL :: Last Record Not In Past 9 Months From Todays Date?

Aug 20, 2015

I would like to display Car records to retrieve last Maintenance request for all customer who did not make request in past 9 months from current date.

MC refers to Maintenance.

MCLog data:

carId: SGY12345 (repeated)
mcDate: 2010-01-30
mcDate: 2012-03-30
carId: SGX55661 (repeated)
mcDate: 2015-05-30
mcDate: 2015-06-15

[Code] .....

Here is my Table:

CREATE TABLE Car (
carId Char (20) NOT NULL,
model Char (20) NOT NULL,
importDate smalldatetime NOT NULL,
custId Varchar (50) NOT NULL,
CONSTRAINT Car_PK PRIMARY KEY (carId));

[code]....

View 6 Replies View Related

T-SQL Scritp To Calculate The Last 6 Months Of Data In A Date Field.

Oct 12, 2006

Hi there,

if any body out there has done this before, can you please post the T-SQL script that calculate whatever last months of data based on a Date field.

Thanks very much,



Manny

View 2 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

Power Pivot :: Date Dimension - Create A List Of Months

Jul 21, 2015

I need to create a date dimension where the lowest level is month. I've seen examples which use the list function such as

Source = List.Dates(#date(2000, 1, 1), Duration.Days(DateTime.Date(DateTime.FixedLocalNow())-#date(2000,1,1)), #duration(1,0,0,0)),

The above increments by 1 day which is defined in the 1st argument of the #duration. My question is how can I dynamically change the value of this 1st argument such that its the number of days in the current month hence it will increment to only return the 1st date in the Month e.g

1/1/2000
1/2/2000
1/3/2000
etc..

I prefer to use an elegant approach if possible, the alternative would be return all dates, create a custom column from these dates which returns the month date - delete the dates column - get a distinct list of the month dates.

View 2 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

Reporting Services :: Displaying Previous Months On The Basis Of Start Date

Nov 4, 2015

I have a ssrs report which has a parameter start date.i have made 12 columns acc to client req which i cannot change:

jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec

These columns are dynamic acc to start date for which i have used this exp for first cell:

=monthname(month(Parameters!Startdate.Value),true) and for next 11 cells accordingly.

These 12 columns are grouped quaterly which i have made statically again for clien req which i cannot change so they are grouped staticaly by me in report like:

q12015      |q2  2015     |q32015      |q42015
jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec  

exp for quarterly is :

="Q" & DatePart(DateInterval.Quarter, Parameters!Startdate.Value) &  year(Parameters!Startdate.Value).

Now when i select my start date of months jan,apr,july and oct report work fine.... but when i select my start date months of feb,may,aug and nov then reportis something like: (lets say i choose feb)

q12015        |q22015     |q32015      |q42015
feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec|jan.

What is happening is in q1 feb,mar and apr are coming and what i want in q1 is jan,feb and mar.

And in q4 nov ,dec and jan is coming and what is want in  q4 is oct,nov and dec.

So generally what is am asking is how to display jan in this condition in my first cell.same apllies for rest of months..

Similarly when i select my start date months of mar,june,sep,dec i want previous two months to be displayed.

View 5 Replies View Related

Selecting Data Within 1 Month, 6 Months, 12 Months

Mar 27, 2008



I have the following table



FeedBack Type Date

test2 positive 03/15/08

tes3 negative 03/01/08

.. ....



in my page i need to select the number of negative/positive comments within the last



1 month, 6 months, 12 months



How can I accomplish that?
thanks

View 5 Replies View Related

How To Get Months

Nov 20, 2006

I need to get all the data based on the user selection of the months. So, I will provide a dropdown will all list of the months. When user pick the month, for example January, then I will show all the invoice on January. How to get the month of the year in the where clause in T-SQL?
 TIA

View 7 Replies View Related

3 Months Lag

Mar 21, 2008

I have a question i am confused very much with i am doing on MTD Average , i really need some help with code.

the logic behind the code is



3 MTD should be the past two completed months as well as the dates in the current month as well. So taking the example above – if the reporting period for Feb was 01/30/2007 to 02/27/2007, and March was 02/28/2007 to 03/27/2007, the 3 MTD should then be the average from 01/30/2007 to 04/10/2007.



This code does not actually gives me the exact value, please advise on any correction to the code , or a new way to write the code.



Avg
(
[Time].[Month].CurrentMember.Lag(3) :
[Time].[Month].CurrentMember,
[Measures].[A1_Avg]
)

View 4 Replies View Related

Months Between

Jan 23, 2008

Hi, I am trying to determine the amount of months between today and adate stored in the database. But I cannot seem to figure out get thedifference between the two dates. Thanks in advance.

View 2 Replies View Related

How To Get Months And Year

Nov 20, 2006

Hi All,
I provided the client with the dropdownlist which lists all the months (january - december). This input will be used to filter in the invoiceDT.
How can I write the query where clause to compare the invoiceDT (mm/dd/yyyy) into this input which is only number 1 through 12?
TIA

View 1 Replies View Related

How Do I Pass In # Of Months

Aug 3, 2007

I have a stored procedure that I want to pass in a number of months and use it in my where caluse. I need to minus the number of months that as passed in by @TaskMonths.
But it is minusing days not months.
@TaskMonths intWHERE (tblTasks.Caller = @Caller) AND (tblTasks.DueDate BETWEEN CONVERT(varchar, GETDATE() - @TaskMonths, 101) AND GETDATE()) I apprecaite any help.

View 2 Replies View Related

Retrieving By Months Only

Sep 16, 2004

I have records in a table and 1 column is in the smalldatetime format which stores the date in the format "2004-09-22",2004-09-20",2004-09-12",2004-08-04" etc etc.

Can anyone tell me how to craft an SQL statement so that i can retrieve records for a certain month.For example,if i want to retrieve records for the month of September,i would get "2004-09-22",2004-09-20",2004-09-12" in results.

View 1 Replies View Related

12 Months Back

Jan 18, 2005

SELECT K.TRANS_TYPE,K.COMPANY,K.DIVISION,K.SALES_FORCE,K.SALES_LINE,K.SALES_REGION,K.SALES_ZONE,K.FORECAST_ DATE,C.STATISTICS_PERIOD,K.PRODUCT_NUMBER,
K.CUSTOMER_CODE,K.WAREHOUSE,0,0,0,K.FORECAST_QTY
FROM KPI_SIGNOFF AS K inner join ROUNDED_CALENDAR as C on K.FORECAST_DATE
between C.START_DATE and C.END_DATE


Hi this is the code that I am using to populate a table let us call new_table.
However what I would like to do is get the max forecast date and get the statistics_period for that max forecast date.

Once I get that I would set that as my current statistics period.

Then I want to create a new column in the same new_table call stat_period-1, which would be the one period back.

Please note that the statistics period is like the months 1 – 12.
Does not go higher that 12 and does not go lower than 1.

So for stat_period-1 when statistics period is equal to 1 it would have to be set to 12.

This is what I want but do not know how to place this into the code, please help me with this.
THanks

View 10 Replies View Related

Query For Due Months

May 13, 2008

Hi All,

I need to display the rent not paid months in the year. How can I form the query?

Help me.

Kamal.

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

Ordering By Months

Jun 9, 2004

I am wondering if someone maybe able to help me, I am needing to order my data via months in the calendar sense not alphabetically, below is what I currently have, but it only does it alphabetically.

select to_char(created,'yyyy-Mon'), matdesc, count(*)
from test
group by to_char(created,'yyyy-Mon'), matdesc
order by to_char(created,'yyyy-Mon') desc

any help would be greatly appreciated

Thank you

Stephen

View 11 Replies View Related

Previous Months

Mar 19, 2007

I have a View there I need my criteria to select 13 months from previous month.

Example. This is 3/1/2007 - I need to query 2/1/2007 to 2/1/2006.

View 7 Replies View Related

Group By Months

Jun 24, 2007

Hi all.
I have probs on grouping by...

Sample data...
table - hrEmpPayrollSummary

empno period1 period2 pagibig_share
1 1/1/2007 1/15/2007 2000
1 1/16/2007 1/30/2007 5000
1 2/1/2007 2/16/2007 6000
2 1/1/2007 1/15/2007 8000
3 1/16/2007 1/28/2007 9000
3 12/1/2007 1/15/2007 10000
3 12/1/2007 12/15/2007 10000

expected result...
result for the year 2007

empno january february march april may june july august september october november december

1 7000 6000 0 0 0 0 0 0 0 0 0 0
2 8000 0 0 0 0 0 0 0 0 0 0 0
3 9000 0 0 0 0 0 0 0 0 0 0 10000

-Thanks





-Ron-

"A logician trying to explain logic to a programmer is like a cat trying to explain to a fish what it's like to get wet."

View 13 Replies View Related

Months As Columns

Dec 14, 2007

Hi i need a sql stored procedure below, this returns 2 columns of data, what i now need is to display the months as columns

ie.
TotalQty - OL_St_Code - Jan - Feb - March

then each month column shows a TotalQty for that month, do i need a query of query? if so how would i go about this


select count(OL_Qty)as TotalQty, OL_St_Code
from dbo.X_TBL_ORDER_LINEWEB LW

LEFT JOIN dbo.X_TBL_ORDERWEB OW ON OW.O_ID = LW.OL_O_ID

WHERE MONTH(O_Date) Between '11' AND '12'
group by Month(O_Date), OL_St_Code

View 19 Replies View Related

First Day Of Month Of 3 Months Ago

Jul 20, 2005

How can I get the date of the first day of the month of3 months ago in one statement ?(And with no time in it ??)Arno de Jong, The Netherlands.

View 1 Replies View Related

Last 3 Months Query

Jan 31, 2008

Hi,

I need to count last 3 months records. The query I'm trying to use was based on last 8 days. How can I use this query in months and not days? Or should I use a new query?




Code Snippet
SELECT COUNT(Column1) AS [Nº de Cartões], Column2 AS [Tipo de Cartão], Column4 AS Periodicidade
FROM main_client_file
WHERE (Column9 >= DATEADD(Day, DATEDIFF(Day, 0, GETDATE() - 3), 0)) AND (Column9 <= DATEADD(Day, DATEDIFF(Day, 0, GETDATE() - 1), 0))
GROUP BY Column2, Column4





Thx.

View 4 Replies View Related

Data For Last 13 Months

May 8, 2008



Hi,

I want to select data from a table for last 13 months.
Can anyone tell me how to write the T-sql code for this?

The table looks like this.


FileMonth Type

2007-04-01 00:00:00.000 Total NULL 199886109.65
2007-04-01 00:00:00.000 Total Line 170724936.92
2007-05-01 00:00:00.000 Total Loan 29161172.73
2007-05-01 00:00:00.000 InstLend NULL 49780163.49
2007-06-01 00:00:00.000 InstLend Line 42450387.44
...........

So I wanna pull data from 04/01/07 to 04/01/08.

So I wanna write a query some thing like this.

Select *
from table1
where Filemonth BETWEEN PreviousMonth AND Last13thMonth


Thanks

View 1 Replies View Related

Use DateDiff To Get Age In Months

Apr 16, 2008

Hi, I've just noticed that there is a proc in my database using a person's age in a where clause but it seems to be rounding the age. The idea is select records where the age (using the person's birthdate) is between @xMonths and @yMonths.




Code Snippet
select datediff(month, '4-30-2006', '4-1-2008');





Result: 24

If 4/30/06 is the birthdate and 4/1/08 is today, it needs to be 23 months. Can someone explain how it determines the month boundaries and how to exclude partial months?

View 7 Replies View Related







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