SQL 2012 :: Use Date Trunc Or Date Function To Select Date Range For Month On Month View
Jul 29, 2015
My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax).In other words, I want to see
Select
Jan 1- 23rd
feb 1-23rd
march 1-23rd
april 1-23rd
,value
from
table
View 9 Replies
ADVERTISEMENT
Oct 21, 2013
Aim – Convert the following field ”[INSTALLATION_DATE]” date format from “20090709” Into this “2009-07-09” ,
Also create a new column called “BegMonth” which selects first day of the given month of the converted date column
The table is ;
SELECT
[FDMSAccountNo],
[INSTALLATION_DATE]
FROM [FDMS].[dbo].[stg_LMPAB501]
Results
FDMSAccountNoINSTALLATION_DATE
87800000088420030521
Required Results
FDMSAccountNoINSTALLATION_DATEBegMonth
8780000008842003-05-212003-05-01
View 3 Replies
View Related
Dec 18, 2001
I need SQL to determine what the date range for the previous month was, ie
Start date 11/01/01 for 11/30/01.
The results will populate a drop down box for user queries.
View 1 Replies
View Related
Nov 19, 2001
I need SQL to determine what the date range for the previous week was, ie
Start date 11/11/01 for Last Sunday through 11/17/01 the following Saturday.
The results will populate a drop down box for user queries.
Thanx.
View 1 Replies
View Related
Jun 26, 2014
I would like to get the first and last day of any month in a given date range.
Ex: Display the first and last day of the months between @startDate and @EndDates.
Input Params= @StartDate='2016-06-21 16:57:11.093'
@EndDate = '2016-09-30 00:00:00.000'
OutPut should be:-
MonthStartDateMonthEndDate
1/06/201630/06/2016
1/07/201631/07/2016
1/08/201631/08/2016
1/09/201630/09/2016
View 1 Replies
View Related
Jan 13, 2015
I've got some records like this:
ID_________Jan Feb...........................Dec
0000030257 0 0 0 0 0 0 1 1 1 1 1 0
where each month field has a 0 or 1, depending on if the person was enrolled that month.
I'm being asked to generate a table like this:
ID_________ Start_Date End_Date
0000030257 July 1, 2014 Nov 30, 2014
Is there some slam dunk way to do this without a bunch of If/Then statements?
The editor compressed all my space fields, so the column headers are off in some places.
View 8 Replies
View Related
Jul 23, 2015
I am trying to query a code where i need to loop a month in a specified date range. Inside the loop I need to return a result of data each month and need to update the table of the returned data. How do I do the update a field inside the loop? Here's my query:
declare @table1 table (
YEAR_EFF int,
MONTH_EFF int,
IDNumber (8),
SUBS_CNT smallint,
MEM_CNT smallint)
declare @StartDate datetime,
[code]....
Others says I need to use exec sp_executesql N'' but how do I use it using my code above?
View 7 Replies
View Related
Oct 8, 2015
I need to recognize only one month in a date range e to make a proportion of the quantity. Practically, period 31-08-2015 - 30-09-2015 is 31 days, 1 belonging August and 30 belonging September so 3.2258% of the quantity must belong August and 96.7742% September. The quantity 200, so 193.54 belong September (That's what I need to achieve). Range 01-09-2015 / 30-09-2015 Qty 500, all 500 belong September.
Range 01-07-2015 / 20-08-2015 Qty 2500 0 belong September. A little bit more complicated if I got 25-06-2015 / 16-12-2015. it 30 day for September and with a datediff I can count the days and make a proportion. I can write piece by piece the code but I'd prefer of course to have only one query for this.
The DDL:
create table forum (idd int, byfrom date, byto date, qty int)
insert into forum values
(1,'2015-06-15','2015-08-18',300),(2,'2015-09-16','2015-10-04',400),(3,'2015-07-28','2015-09-27',1000),
(4,'2015-09-01','2015-09-30',500),(5,'2015-09-03','2015-09-03',300),(6,'2015-08-02','2015-09-02',100),
(7,'2015-07-01','2015-07-30',500),(8,'2015-06-03','2015-12-08',500),(9,'2015-09-01','2015-09-30',500),
(10,'2015-08-04','2015-09-04',300)
View 14 Replies
View Related
Mar 29, 2007
Hi all,
I am trying to use the custom code in the report but I don't think I am understanding how this is being used.
I have a function to get starting months for a report parameter.
The function is below:-
--------------------------------------------------------------
Shared Function GetStartingMonths() as String
dim strDefault as string
dim CurrentMonth as String
Dim SqlString as String
'strDefault = Month(Now) & "/1/" & Year(Now)
CurrentMonth = "5/1/2002"
Do While CDate(CurrentMonth) <= Now
SqlString = SqlString + "Select " & CurrentMonth & " as value, " & MonthName(Month(CurrentMonth)) & " " & Year(CurrentMonth) & " as MonthYear"
CurrentMonth = dateadd("m",1,CDate(CurrentMonth))
if Cdate(CurrentMonth) = Now then
Exit Do
else
sqlString = SqlString & " Union "
end if
Loop
return SqlString
End Function
---------------------------------------------------------------
what i am trying to do here, and hopefully produce a sql string that would fill my dataset of dates and their representation.
In the dataset, I had put the following expression
=Code.GetStartingMonths()
However, I can't seem to get the parameter to display the dates. shows up as disabled in my report.
Am I doing something wrong here or is there a better way to doing this ?
Additionally, I was wondering whether there is a better SQL code that would achieve the same thing I am doing ?
thanks !
Bernard Ong
View 15 Replies
View Related
Jul 22, 2015
In SSRS report I have 2 parameters start date and end date, so when somebody is selecting end date more than a month(if today date is 22 then they are selecting aug 23) then it should give error message to user like "Date out of range".
View 2 Replies
View Related
Feb 16, 2006
I have been spoiled by some report writing tools that have intrinsicfunctions like Last Year Month-to-date. I'm looking for a way to emulatethis in SQL Server now with my fields that are date/time.I'm thinking I need to develop a user defined function to accept a dateinput parameter, but I don't know where to start.Help/Examples appreciated.Thanks,Frank*** Sent via Developersdex http://www.developersdex.com ***
View 1 Replies
View Related
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
Oct 20, 2015
Let's say if the date is 01/01/2015 till 01/01/2016
I want split these dates monthly format wise and then use them in variable in cursors to loop
For an example Monthly date should be 01/01/2015 and 01/31/2015 and use these two values in 2 variables and make a loop until it never ends whole date range which is 01/01/2016
View 2 Replies
View Related
Apr 14, 2008
Hello All,
i have three textboxes in a page and i want fill those textboxes with the date, month,year respectively.....
i have a datecreated column in discount table in a mm/dd/yy format ...how to extract the date, month, year from this format and put the value in textboxes..?
Any help..
Thanks..
Anne
View 3 Replies
View Related
Apr 17, 2008
hi need help to solved date calculation for next month
i explain
i have table employee and the employee insert into table the holidays
the date start >>>> to date end
now i need to create a view only for next moth , in this view i need to see only the relative dates for the next month if the dates is not for the next month i don't need to see it
like this example 09/07/2008 > 09/08/2008 (is not for next month)
like this example 10/09/2008 > 12/09/2008 (is not for next month)
555
EEE
09/07/2008
09/08/2008
4
666
fff
10/09/2008
12/09/2008
1
in this example i need to see only the relative dates for next month only in the view
333
cccc
01/05/2008
15/05/2008
4
4
333
cccc
01/05/2008
31/05/2008
1
30
tb_all_holiday before
id
fname
Start_Date
EndDate
val_holiday
111
aaaa
15/03/2008
10/05/2008
1
222
bbbb
02/05/2008
31/05/2008
3
333
cccc
03/04/2008
15/05/2008
4
333
cccc
29/04/2008
07/07/2008
1
444
dddd
01/05/2008
02/05/2008
1
444
dddd
09/05/2008
19/08/2008
1
555
EEE
09/07/2008
09/08/2008
4
666
fff
10/09/2008
12/09/2008
1
VIEW_all_holiday after -next month only
id
fname
Start_Date
EndDate
val_holiday
sum_day_next_month
111
aaaa
01/05/2008
10/05/2008
1
4
222
bbbb
02/05/2008
31/05/2008
3
29
333
cccc
01/05/2008
15/05/2008
4
4
333
cccc
01/05/2008
31/05/2008
1
30
444
dddd
01/05/2008
02/05/2008
1
1
444
dddd
09/05/2008
31/05/2008
1
22
all the time i need to see only the relative dates for the next month only
tnx
View 10 Replies
View Related
Apr 6, 2015
I have 2 tables, one is table A which stores Resources Assign to work for a certain period. The structure is as below
Name StartDate EndDate
Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000
The table B stores the item process time. The structure is as below
Item ProcessStartDate ProcessEndDate
V 2015-04-01 09:30:10.000 2015-04-01 09:34:45.000
Q 2015-04-01 10:39:01.000 2015-04-01 10:41:11.000
W 2015-04-01 11:44:00.000 2015-04-01 11:46:25.000
A 2015-04-01 16:40:10.000 2015-04-01 16:42:45.000
B 2015-04-01 16:43:01.000 2015-04-01 16:45:11.000
C 2015-04-01 16:47:00.000 2015-04-01 16:49:25.000
I need to select the item which process in 2015-04-01 16:40:00 and 2015-04-01 17:30:00. Beside that I need to know how many resource is assigned to process the item in that period of time. I only has the start date is 2015-04-01 16:40:00 and end date is 2015-04-01 17:30:00. How I can select the data from both tables. There is no need for JOIN, just seperate selections.
Another item process time is in 2015-04-01 10:00:00 and 2015-04-04 11:50:59.
The result expected is
Table A
Name StartDate EndDate
Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000
Table B
Item ProcessStartDate ProcessEndDate
A 2015-04-01 16:30:10.000 2015-04-01 16:32:45.000
B 2015-04-01 16:33:01.000 2015-04-01 16:35:11.000
C 2015-04-01 16:37:00.000 2015-04-02 16:39:25.000
Scenario 2 expected result
Table A
Name StartDate EndDate
Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Table B
Item ProcessStartDate ProcessEndDate
Q 2015-04-01 10:39:01.000 2015-04-01 10:41:11.000
W 2015-04-01 11:44:00.000 2015-04-01 11:46:25.000
View 8 Replies
View Related
May 6, 2008
In my database . Some to the dates has been stored wrongly .
for example
03/04/2008 to be stored in database . But in my database it was stored as 04/03/2008 . like this i have more that 100 records . How can i change this to correct format using query . Guide me urgent .
View 8 Replies
View Related
Jun 3, 2014
I am trying to add month to a date. Here is my code
declare @CollectionDate date='10-28-2014'
select @CollectionDate
;WITH CTemp AS (
SELECT TransactionDate=CAST(@CollectionDate AS DATE) ,RemainingTransaction=1
UNION all
[Code] ....
It is working fine. But when I am giving date '10-30-2014' it shows me the error
Msg 241, Level 16, State 1, Line 3
Conversion failed when converting date and/or time from character string.
I can understand the problem that it is for the month of February. But How do I overcome the situation?
View 3 Replies
View Related
Jul 23, 2014
I am trying to SUM a column of ActivityDebit with current Calendar_Month to a Column of Trial_Balance_Debit from Last Calendar_Month. I am providing Temp Table code as well as fake data.
=====
IF OBJECT_ID('TempDB..#MyTrialBalance','U') IS NOT NULL
DROP TABLE #MyTrialBalance
CREATE TABLE #MyTrialBalance (
[Trial_Balance_ID] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL,
[FISCALYEAR] [smallint] NULL,
[Code] ....
Here is my Query I am trying but not working. I cant figure out how to doo the dateadd for correct column.
SELECT A.Trial_Balance_ID,A.ACTIVITYDEBIT --SUM(A.ACTIVITYDEBIT + B.Last_Trail_Balance_Debit) AS New_TB
FROM
(SELECT [Trial_Balance_ID], [Calendar_Month],[ACTIVITYDEBIT]
FROM Mytrialbalance
WHERE actindx='48397' AND ACTIVITYDEBIT='820439.78000'
)A INNER JOIN
(SELECT [Trial_Balance_ID],DATEADD(MM, -1,Calendar_Month)AS Last_Month
FROM Mytrialbalance) B ON B.Trial_Balance_ID=A.Trial_Balance_ID
View 9 Replies
View Related
Apr 28, 2015
I've written sql code which takes a date and finds the Last Day of the Month one year ago. For example, it takes the date '2015-04-17' and returns the date '2014-04-30'. The code works fine in a query. Now I'm trying to turn this into a function. However, when I try to create the function I get the error:
Operand type clash: date is incompatible with int
Why is this error being returned?
Here is my function:
CREATE FUNCTION dbo.zEOM_LY_D(@Input Date)
RETURNS date
AS
BEGIN;
DECLARE @Result date;
SET @Result = convert(DATE, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,dateadd(m, -11, @Input)+1),0)),101)
RETURN @Result;
END;
View 11 Replies
View Related
Mar 18, 2015
This is what I have for a query but i would like to take the year off. is there a way i can just have the month and day without the year attached?
View 3 Replies
View Related
Jan 31, 2014
I have a table with Month , Year as varchar. I realized it was a big mistake. Since its getting too complicated to query this way.
Year Month Productname
2013 11 ACB
2013 11 CDE
I would now like to add another column called date and store these Year Month as a date to my existing table
Year Month ProductName Date
2013 11 ACB 2013-11-01
2013 11 CDE 2013-11-01
Is there a way I can do it for all the columns of the existing table ??
View 3 Replies
View Related
Feb 2, 2006
Hi all,I have a SP that is passed a date, and then need to do a test of some data returned from the DB by getting the passed Date, finding out its month, and then using the BETWEEN clause to get all rows from teh DB where their date falls between the 1st and the last (28th, 30th, 31st) of the month contained in the Date that is passed to the SP. I currently have the following SQL in my WHERE clause to get the first day of the month, but it seems long-winded. Is there a smaller, smarter way of getting it...DATEADD(MONTH, +1, DATEADD(MONTH, -1, @RequiredMonth))ThanksTryst
View 2 Replies
View Related
Sep 3, 2003
Is there any function in sql to find last date of the given month?
View 5 Replies
View Related
Jul 21, 2014
I'm trying to write a query that will tell me the day of the week for the 20th of any given month.
View 6 Replies
View Related
Apr 14, 2004
I have a question about creating view for these coloum and have a lot trouble, before that I describe my tables :
Table A :
Code as nvarchar
Ammount1 as money
Date as datetime -> transaction date
primary key are Code and Date1
Table B :
Code as nvarchar
Ammount2 as money
Date as datetime -> transaction date
primary key are Code and Date2
I like joining this table and create table/view that result :
Code | Amount1 Today | Amount2 Today | Amount1 Last Year | Amount1 Month to date (Sum from first day month till today) | Amount2 month to date | Amount1 Last Year month to date
The parameter only for @Date.
First I have problem to get queries specially for first date in the month, I used CAST but it doesn't work, then I used CONVERT and CAST still doesn't work.
Anybody help me ?
Thx a lot.
ad1k4r4
View 6 Replies
View Related
Jun 2, 2008
Hello all,
Is there a way to set a variable that includes the first day of the last month.
For example, we are june 2nd. I want a function that returns - for every day in june - 2008/05/01.
Thanks,
Raph.
View 14 Replies
View Related
Nov 23, 2005
Hi guys,
How to get the first day of the month.
Say the given Cut Off Date is 11/30/2005. I need to get the StartDate as 11/01/2005.
Thank you.
View 2 Replies
View Related
Sep 27, 2006
Hi All,
Can anyone tell me the function name by which I can get the month Name form a date value
When I am using the command print month('2006-09-26 16:30:18.793')in SQL Analyzer window it is giving 6as output but i need the name i.e. JUNE
View 2 Replies
View Related
Jun 18, 2007
I need to sort some data by month or extract data for a particular month. My date format is 5/7/2007 11:38:54 AM. What is the easiest way to achieve this?
Melvin Felicien
IT Manager
DCG Properties Limited
View 4 Replies
View Related
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
Mar 17, 2004
Hello,
I have made a query that does a cross-tab of data. The date data is normal like 02/04/2004, 03/04/2004 etc.
I am using Month Name to group the dates by Month name.
My problem is the query result sorts the month names in alphabetical order, not month order.
I am using the query to generate a graph and the months are not in order.
Is there a way to sort the months by month order not alphabetical order.
Thanks
Goong
View 4 Replies
View Related
Apr 20, 2006
Hi
Problem:
one table "tbl_SalesReporting" in this table salesEr every day submit his information.
i need which day salesEr not submit his information. this is check up to current date.
E.G:
salesEr submit information start this date "1/04/2006" to "17/04/2006" and
currentdate: 20/04/2006
i need 18/04/2006,19/04/2006 this date Using SQL queries........
Please help me........
View 9 Replies
View Related
