# Calculate Date

Nov 12, 1999

Hi All!
I need a query to find all dates from today to one-year back.
If I start from today day I need find all dates until 11/12/98.
Thanks a lot.
Greg.

## SQL Server 2012 :: Calculate Number Of Days Based On Computer System Date And Due Date Row

Mar 18, 2014

I have a query to run a report where the results has a column named “Due Date” which holds a date value based on the project submission date.Now, I need to add 4 columns named, “45 Days Expectant”, “30 Days Overdue”, “60 Days Overdue” and “90 Days Overdue”.I need to do a calculation based on the “Due Date” and “System (I mean default computer date) Date” that if “System Date” is 45 days+ to “Due Date” than put “Yes” in “45 Days Expectant” row.

Also, if “Due Date” is less than or equal to system date by 30 days, put “Yes” in “30 Days Overdue” and same for the 60 and 90 days.how to write this Case Statement? I have some answers how to do it in SSRS (Report Designer) but I want to get the results using T-SQl.

## Power Pivot :: Calculate Sum Of Actual Sales Until Date And Forecast Sales After A Date?

Sep 30, 2015

I want to calculate the sum of actual sales until a date and forecast sales after a date.I am not sure what the best approach to this problem is, but I have tried my best with the following approach. Any better ways to solve this (using DAX).

I have created a parameter table that offers the last date of each month as possible choices to the user. I have tried to create a measure that sums actual sales up until this date.

SalesQuantityActual:=IF(HASONEVALUE(parLastActualMonth[Date]);CALCULATE(factSalesActual[Quantity];factSalesActual[Date]<=VALUES(parLastActualMonth[Date]));BLANK())

Unfortunately the measure above does not work.

In addition to the parameter table, I also have a normal date table.

## Calculate Week From Date

Jun 22, 2004

Hi,

I want to convert a date to a weeknumber in my view.
How is this possible with SQL?

## Calculate Age From DOB Using Specific Date?

Mar 2, 2015

I know how to calculate age from DOB using the current date, but how do I do it if I want to say show me the age as of Jan 1, 2015. Where the DOB is short date looks like:

1946-04-13

## Calculate Workdays From A Date

Oct 24, 2007

This is one way you can add a number of workdays to a specific date and return a workday (monday-friday).CREATE FUNCTION [dbo].[fnAddWorkdays]
(
@StartDate DATETIME,
)
RETURNS DATETIME
AS
BEGIN
RETURN@StartDate
+ CASE(@@DATEFIRST + (DATEPART(WEEKDAY, @StartDate) - 2)) % 7 WHEN 5 THEN 2 WHEN 6 THEN 1 ELSE 0 END
+ @DaysToAdd / 5 * 7
+ CASE WHEN (@@DATEFIRST + (DATEPART(WEEKDAY, @StartDate + CASE (@@DATEFIRST + (DATEPART(WEEKDAY, @StartDate) - 2)) % 7 WHEN 5 THEN 2 WHEN 6 THEN 1 ELSE 0 END) - 2)) % 7 + @DaysToAdd % 5 > 4
THEN 2
ELSE 0
END
END

E 12°55'05.25"
N 56°04'39.16"

## Calculate Weekending Date.

Jul 20, 2005

Can someone help me with this. I need to calculate the week endingdate of the first week of the year based upon a year provided by theuser. Is there a simpler way other than writing my own UDF?

## Calculate Date Field

Oct 30, 2007

I want to get the number of days pending for a perticular column. I want the out put as a integer value.

This is what I used for my calculated field.
But when i write this day is underlined in red. It says unrecognized identifier.

=DATEDIFF(Day, GETDATE(),Fields!SubmittedDate.Value)

If I use "day", then it is not underlined but still it does not work.

When I use following query, I get no error message. but still no data in the column too.

=DATEDIFF("d", Fields!SubmittedDate.Value, "GETDATE()" )

Can anyone tell me why is that?

Thanks

## Trigger To Calculate Age From Date Of Birth

Dec 12, 2003

Here's the problem. I want to insert age in years to a table of children, using a MS SQL trigger to calculate age from Date of Birth (DOB). DOB format is mm/dd/yyyy. The DOB input comes from an ASP Insert statement. I've tried to use DateDiff and a user-defined function to calculate age without any success. Also the trigger needs to account for children of less than 1 year old, could be a 0. Age is integer in the children table. Any help is greatly appreciated. The sooner the better. Even a kick start is better than nothing.

## How To Calculate A Date Difference In Days

Apr 2, 2007

Suppose I have these two days fields
ddold 1/1/2005 12:00:00 AM
ddnew 2/1/2007 12:00:00 AM

How can i get the DateDifference of these two dates in days.

## How To Calculate Expiry Date Using Stored Procedure

Oct 16, 2004

Hi, i'm trying to calculate on the expiry date for product which include the start date and end date.

the product can't b displayed if the start date haven't reached the date, besides, it oso can't b displayed if it has expired.

the storedprocedure tat i've written is:

WHERE
DateDiff(day, StartDate, getDate())<=0
AND
DateDiff(day, EndDate, getDate())>=0

i wonder why it can's show the result!!! is the code correct?

## Calculate Date Difference Excluding Dates

Aug 27, 2012

I have already seen stored procedures that can calculate a difference in dates, excluding the weekends. Any extension of such a SQL query to exclude not only weekends, but other dates as well. We have a table of "holidays" (not necessarily standard holidays), and I am wondering if there is a way to exclude them from the calculation.

## 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)

## SQL Server 2008 :: How To Calculate Date Time

Apr 9, 2015

My data is looks like this,

Date---------------------------------------A
2015-03-01 13:38:07.343----------------1
2015-03-01 14:04:04.460----------------1
2015-03-02 19:33:55.117----------------3
2015-03-02 19:33:55.117----------------4
2015-03-02 19:39:26.580----------------1

I want data looks like this

Date-------------------------------------------A
Day 1------------------------------------------2
Day 2------------------------------------------8

## How To Calculate The Total Days Between Open And Close Date

Apr 6, 2006

Hi All,
I have a table call case and case_status have two fields, date and status as below:
date                          status
04/01/2006               open
04/05/2006               closed
04/10/2006               open
04/15/2006               closed
Whenever i open and closed the case, one record is insert into the case_status table.
Now I would need to calculate the total days of the case in storeprocedure.
Aung

## T-SQL (SS2K8) :: Calculate And Return Previous Date At 18:00 Hours

Mar 4, 2014

How can I calculate and return the previous Date at 18:00 Hours?

Here is a miserable attempt:

DECLARE @RunDate SmallDateTime
DECLARE @CurrentDate SmallDateTime
DECLARE @RunDateWoTime SmallDateTime
SET @CurrentDate = GETDATE()

SET @RunDate = DATEADD(day,-1,@CurrentDate)-- AS CurrentDate
SELECT @RunDate AS RunDate

-- Desired Result is the following:

-- 2014-03-03 18:00

## Calculate Date Difference Excluding Bank Holidays

Aug 28, 2013

I have a Bank holiday table (ID column, and Date column), how to exclude bank holidays in the datediff returned.

Create FUNCTION [dbo].days_diff ( @date1 datetime,@date2 datetime )
RETURNS int
AS
BEGIN
declare @i int
Declare @count int

[Code] .....

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

## Calculate Number Of Working Days Back From A Date

May 6, 2008

Hi,
I need to calculate the number of working days from a date backwards. For example 2 working days before Thursday would be the Tuesday (as a basic example)

I use the following code and a Calendar table to calculate the working days from a date but can anyone help with reworking this query to do the reverse

declare @WorkingDate as datetime

SELECT @WorkingDate=dt
FROM tblCalendar AS c
WHERE (@WorkingDays =
(SELECT COUNT(*) AS Expr1
FROM tblCalendar AS c2
WHERE (dt >= @StartDate) AND (dt <= c.dt) AND (IsWeekday = 1) AND (IsHoliday = 0))) AND (IsWeekday = 1)
AND (IsHoliday = 0)

-- Return the result of the function
RETURN convert(varchar(12),@WorkingDate,106)

Hope someone can help

Thanks

## T-SQL (SS2K8) :: Calculate 90 Days And 3 Years Ago From Effective Date In A Table?

Sep 30, 2014

What would be the most straight forword to Calculate 90 days and 3 Years ago from an Effective Date in a table?

as in

SELECT EffectiveDate
from FL.CEFHistory

I need to return the effective date - 90 days and 1 year from that.

[URL]

## T-SQL (SS2K8) :: Stored Procedure - Calculate Closest Date For Manufacturing

May 5, 2015

I'm trying to write a Stored Procedure that have to calculate the closest date for manufacturing.

What I have:

- The BOM (bill of materials) with the needed quantity for production

DECLARE @BOM TABLE
(
ItemIDINT
,neededQuantityfloat
)
INSERT INTO @BOM (ItemID, neededQuantity)

SELECT 1, 10
UNION ALL SELECT 2, 10
UNION ALL SELECT 3, 5

- a calculated table that told me the availability for each component of the BOM, sorted by date. (each row have a plus or minus of the quantity so it can by summarized)

DECLARE @WhareHouseMovement TABLE
(
ItemIDINT
,Quantityfloat
,DateDATETIME
)

INSERT INTO @WhareHouseMovement (ItemID, Quantity, Date)
SELECT 1, 10, '2015-03-01'

[Code] ....

My question is: how do I check when is the closest date to manufacturing? I have to check that the quantity of ALL the components of the BOM is enough to produce the product, but I can't get how to do it.

If I'm not wrong the example should give the result 2015-03-26.

## SQL Server 2012 :: Calculate Number Of Days From A Date - Exclude Weekends And Holidays

Feb 2, 2014

I have already created a table name 'tblHolidays' and populated with 2014 Holidays. What I would like is be able to calculate (subtract or add) number of days from a date. For example subtract 2 days from 07/08/2014 and function should return 07/03/2014.

CREATE FUNCTION [dbo].[ElapsedBDays] (@Start smalldatetime, @End smalldatetime)
RETURNS int
AS
BEGIN
/*
Description:
Function designed to calculate the number of business days (In hours) between two dates.

[Code] ......

## Case Statement - Calculate No Of Days Based On User Input Start And End Date

Jan 5, 2015

I have to produce a report to calculate no of days based on user input start date and end date. I have tried to explain below.

say for eg: in the tables I have emp name
user 'Phani' started work from - EStart 20/11/2014EEnd 10/01/2015 - total days --datediff
within his work period he did different roles:

PhaniMarketing (prSt Date) 20/11/2014prE date (28/11/2014) Total 9 days
PhaniAdmin (prSt Date) 29/11/2014prE date (20/12/2014) Total 22 days
PhaniCRM (prSt Date) 20/12/2014prE date (10/01/2015) Total 22 days
Total days 53 Days

For this :

I calculated datediff + 1 and got sub jobs days BUT

say financial director wants to see Title of 'Sub Jobs' with 'Days' from 1st Dec to 31st Dec

so on paper I calulated as :

1-31 Dec 2014
PhaniMarketing NULL (Do not fall in Req Dt)
PhaniAdmin 20 (Deduct 2 days of Nov & calculated 20 days of Dec)
PhaniCRM 11 (Deduct 20 days of Nov and deduct 11 days of Jan so for Dec , we got 11 days)
Total days 31

HOW CAN I USE Case statement to calculate days for given start date and end date. I have to include all three totals, 1 for Job dates, 2, subjobs dates, 3 cal of days for a requested period.

## SQL 2012 :: Produce Report To Calculate No Of Days Based On User Input Start And End Date

Jan 5, 2015

I have to produce a report to calculate no of days based on user input start date and end date.

say for eg: in the tables I have emp name
user 'Phani' started work from - EStart 20/11/2014EEnd 10/01/2015 - total days --datediff
within his work period he did different roles:

PhaniMarketing (prSt Date) 20/11/2014prE date (28/11/2014) Total 9 days
PhaniAdmin (prSt Date) 29/11/2014prE date (20/12/2014) Total 22 days
PhaniCRM (prSt Date) 20/12/2014prE date (10/01/2015) Total 22 days
Total days 53 Days
for this :

[code]...

HOW CAN I USE Case statement to calculate days for given start date and end date. I have to include all three totals, 1 for Job dates, 2, subjobs dates, 3 cal of days for a requested period.

## Transact SQL :: Calculate DateTime Column By Merging Values From Date Column And Only Time Part Of DateTime Column?

Aug 3, 2015

How can I calculate a DateTime column by merging values from a Date column and only the time part of a DateTime column?

Sep 21, 2006

## Calculate % Qry

Feb 13, 2007

Hi,
I am trying to execute a query which calculate % on group by. here is the query
SELECT     C.USR_HIGHEST_DEGREE,COUNT(DISTINCT C.MASTER_CUSTOMER_ID) AS [# MEM],(convert(numeric(5,2),COUNT(DISTINCT C.MASTER_CUSTOMER_ID))
/    (

SELECT convert(numeric(5,2),COUNT(CC.MASTER_CUSTOMER_ID))    FROM         MBR_PRODUCT AS MPP WITH (NOLOCK) INNER JOIN                      PRODUCT AS PP WITH (NOLOCK) ON MPP.PRODUCT_ID = PP.PRODUCT_ID INNER JOIN                      ORDER_MASTER AS AA WITH (NOLOCK) INNER JOIN                      CUSTOMER AS CC WITH (NOLOCK) ON AA.SHIP_MASTER_CUSTOMER_ID = CC.MASTER_CUSTOMER_ID INNER JOIN                      ORDER_DETAIL AS BB WITH (NOLOCK) ON AA.ORDER_NO = BB.ORDER_NO ON MPP.PRODUCT_ID = BB.PRODUCT_ID              WHERE (CC.CUSTOMER_STATUS_CODE = 'ACTIVE') AND (BB.CYCLE_END_DATE >= GETDATE()) AND (AA.ORDER_STATUS_CODE = 'A') AND                       (BB.LINE_STATUS_CODE = 'A') AND (BB.FULFILL_STATUS_CODE IN ('A', 'G')) AND (MPP.LEVEL1 IN ('NATIONAL'))

)*100 AS [%]

FROM         MBR_PRODUCT AS MP WITH (NOLOCK) INNER JOIN                      PRODUCT AS P WITH (NOLOCK) ON MP.PRODUCT_ID = P.PRODUCT_ID INNER JOIN                      ORDER_MASTER AS A WITH (NOLOCK) INNER JOIN                      CUSTOMER AS C WITH (NOLOCK) ON A.SHIP_MASTER_CUSTOMER_ID = C.MASTER_CUSTOMER_ID INNER JOIN                      ORDER_DETAIL AS B WITH (NOLOCK) ON A.ORDER_NO = B.ORDER_NO ON MP.PRODUCT_ID = B.PRODUCT_IDWHERE     (C.CUSTOMER_STATUS_CODE = 'ACTIVE') AND (B.CYCLE_END_DATE >= GETDATE()) AND (A.ORDER_STATUS_CODE = 'A') AND                       (B.LINE_STATUS_CODE = 'A') AND (B.FULFILL_STATUS_CODE IN ('A', 'G')) AND (MP.LEVEL1 IN ('NATIONAL')) GROUP BY C.USR_HIGHEST_DEGREE
My problem here it , query gives error. I would appreciate if someone can give me any idea how to go about this. I tried calculating % outside sql but still doesn't work.
Any help would be appreciate.

## Calculate Sum Of SQL Top 10

Mar 30, 2007

I have an SQL statement which returns the Top 10 states with the number of visitorsSELECT TOP 10 Customer.State States, COUNT(Customer.state) Visitors    FROM [Customer] WHERE Customer.year = '2006'    GROUP BY Customer.state    ORDER BY COUNT(Customer.state) DESCSo far this is what I havestate| visitorsMD341527.2PA215417.2NJ127510.2NY10258.2VA8136.5MA2922.3FL2562DE2431.9OH2411.9CA2381.9But what i need is to calculate the total for the Visitors column in my SQL so that is like soMD341527.2PA215417.2NJ127510.2NY10258.2VA8136.5MA2922.3FL2562DE2431.9OH2411.9CA2381.9Total Top 10995279.3Total for All Years12555100I tried using the sum but I was only getting one value and not the rest...So how can i accomplish this?Thank you

## How To Calculate?

Feb 13, 2008

Hi all,

I'm having the table in the following structure,
UId  - int
Pct - int
Amt - money
Example
UId      Pct     Amt
12      25       1500
12      30       2500
12      45       2000

i want to calculate to calculate the sum of amount for the UId 12 in the following manner,
sum of ( Amt * (Pct/100)) for UId = 12
how to write query in sql server 2000?
when i calculate if the value (Pct/100) < 0 that is 0.25 then it will take it as 0 so i'm getting all the values as 0.
Thanks!

## Calculate Mtd And Ytd.

Mar 7, 2001

I have a table that has a providername and totals field.
I want to be able to calculate the MTD and YTD totals for each provider.

EX. Provider MTD YTD

Bob 100.00 300.00

But I am not sure how. I can get both totals seperately, but not n the same line....
Thanks
Ron

## How To Calculate Age

Nov 10, 2006

Can anyone help me with how to calculate the exact age.

## Calculate Age?

Jan 25, 2004

I have a DOB field in my sql table, does anyone know how to display that field as an actual age in a view or a SP?

## Calculate %

Apr 15, 2008

I want to calculate rate as percentage using code below:

SELECT OrderBy, NumShiped/Total AS Rate FROM Order

I always got Rate = 0 even thought NumShiped = 80 and Total = 100

What is problem?