Calculate Age From DOB Using Specific Date?
Mar 2, 2015I 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
ADVERTISEMENT
SQL Server 2012 :: Calculate Number Of Days Based On Computer System Date And Due Date Row
Mar 18, 2014I 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.
Using A Date In Where Clause, Without Hardcoing A Specific Date
Jun 20, 2006i want to limit the data being stored in a table to the past 6 months only.
data in the table is refreshed each night.
how do i do this without hard coding a date?
Calculate Date
Nov 12, 1999Hi 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.
Power Pivot :: Calculate Sum Of Actual Sales Until Date And Forecast Sales After A Date?
Sep 30, 2015I 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, 2004Hi,
I want to convert a date to a weeknumber in my view.
How is this possible with SQL?
Calculate Workdays From A Date
Oct 24, 2007This 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,
@DaysToAdd INT
)
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
+ @DaysToAdd % 5
+ 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, 2005Can 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?
View 1 Replies View RelatedCalculate 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, 2003Here'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.
View 11 Replies View RelatedHow To Calculate A Date Difference In Days
Apr 2, 2007Suppose 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, 2004Hi, 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, 2012I 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.
View 7 Replies View RelatedSQL 2012 :: How To Calculate Date Difference In Months
Jan 23, 2015I 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, 2015My 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
Add Specific Date
Apr 16, 2008Hi all
How do i set a specific between date in here
i want to add this line or How do i set specific between date
WHERE between '06-06-2005' And '04-01-2007'
SELECT * FROM gradeaverage
WHERE Name='student'
ORDER BY Date
Thank advance
How To Calculate The Total Days Between Open And Close Date
Apr 6, 2006Hi 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.
Anyone can help me please.
Aung
T-SQL (SS2K8) :: Calculate And Return Previous Date At 18:00 Hours
Mar 4, 2014How 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, 2013I 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, 2006Hi 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, 2008Hi,
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, 2014What 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, 2015I'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.
Get Date With Specific Hour
Jul 8, 2014from getdate(), how can i get today's date with specific time :
For example : (i just need specific 11oclock today)
2014-07-08 11:00:00.000
This do the trick :
select DATEADD( HH, 11, CAST(CAST(DATEADD(DAY,-1,GETDATE()) AS INT) AS DATETIME))
But at second half of the day it changed from 2014-07-08 11:00:00.000 to the next day, i dont know why who controls the past controls the future, who controls the present controls the past. ¯(º_o)/¯ ~~~
Get Timezone Specific Date
Aug 11, 2006
Hello,
I am struggling with one algo i.e. I want to retrieve the date according to time zone
I have table called TIMEZONe in which i have the information about the hours difference (+12 to -12) from GMT...
now my scenario is that I want to get the date, using information from timezone area table (time difference from GMT), of any particluar time zone
can any one tell me, how will I able to achieve this task.
for example:
it 4:00 AM in the mornign in GMT TIME and date is 11-08-2006 but in US it 11:00 PM and date is 10-08-2006
Note: Server date is set to GMT (System date)
regards,
Anas
Search For A Specific Date When Specified As String?
May 18, 2007Dear all
I am pretty new to SQL server 2005 and have the following issue:
I get the date from the asp applciation in format dd/mm/yyyy and then try to do a search for all records that might match that date in sql server 2005. The date is storred in ther database in datetime format.
I am having trouble composing the stored procedure to do that.
Any help would be apreciated.
Sincerely
d
Specific Date With In An Ordered Sequence
Mar 7, 2008
How would i find the 3rd date in a sequence of dates, that is ordered?
Example
Date1 = 08-01-01
Date2 = 08-02-01
Date3 = 08-03-01
Date4 = 08-04-01
i need to retrieve Date3
SQL Server 2012 :: Calculate Number Of Days From A Date - Exclude Weekends And Holidays
Feb 2, 2014I 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, 2015I 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, 2015I 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.
Counting Content Files For A Specific Date Range
Apr 10, 2008I'm trying to find a way to count the number of times a ContentFile, appears in a ContentLog, for a specified period of time.
SELECT DISTINCT ContentFile FROM ContentLog
Shows a list the list of content files.
I'm not sure though how to count for a specific ContentFile, or how to count for on a specific date range.
Thanks for any help you can give.
Transact SQL :: Update Date With Specific Hour / Minute And Second
May 7, 2015How I can update the following date to get the desired result with specific hour, minute and second
2014-01-01 00:00:00.000
Desired Result
2014-01-01 17:20:20.000
How To Get The Week Number Of A Specific Date In SQL Server 2005
Jan 10, 2008Hi,
Can anybody tell me to How to get the week number of a specific Date in SQL Server 2005.
Like : say Date "03/01/2008" belongs to the 1st week of the year 2008, same way "21/02/2008" is 8th week of the year 2008.
So, the same way I need to know the week number of a given (variable) date.
I need to write / use a Function in SQL server which will return the Number (INT) (i.e. 1 or 8 or 10 etc...) against a specific (Parameter) date.
regards,
Saibal
Note : Please feel free to write me if my question is not clear.