This code displays records by the the month they where recorded and displays the months as names (January...)
How can I do the Same for Days (Mon, Tues) and Hours (9.00,10.00)
Select DATENAME(mm,dDate) as Month, count(iStatID) as Total_Po
from ListingStats
Group by DATENAME(mm,dDate)
Order by DATENAME(mm,dDate)
I get a column in hours in a table and when I try to convert it to months and days its giving me incorrect results.
My timehours column in my table has hours recorded timehours value is 5832 and I get results as 11 months and 27 days.
This query is giving me wrong result -select months=(timehours %365)/30,days= (timehours % 365)%30Â
I have hours which can be like 32.5 and would like to have them in 1 month 2 Days format.
View 2 Replies View RelatedI want to display Days Hours Mins Format.
I am Having two columns Like below,
Col1 (in days) Â Â col2 (In Hours : Mins)
3days 4:5Â
In this first have to  add Col1 and Col2 (Here one day is equals to 9 hours ) so the addition is 31.5
From this 31.5 I should display 3 Days 4 Hours 30 Mins because 31.5 contains 3 (9 hours) days 4 Hours and .5 is equals to 30 mins.
How do i take full backup of last 20 days transactions, and i can use that backup to restore those 20 days transactions.
View 10 Replies View RelatedI have two field and I need to find the sum I guess of them. One is called the clm_dout (process date) and the other one is clm_rcvd (received Date). These are both date and time fields and it looks like this 2006-03-17 00:00:00.000. I need to create a formula that wiil i guess give me the difference and create it into hour and minutes because I an trying to create a turn around report. this is what I had...
TimeValue({clm_doubt}-{clm_rcvd})
This is not working for me. Can someone give me a suggestion please.
Thanks!
Hi Guys,
I have data in minutes which is integer value ex 14454.I wanto convert it to days,hours,minutes & seconds.
Is there any simple way?
Regards.
I have to calculate the total working hours between days, there hours must get automatically round off to nearest value example:
Date :12-05-2013 time : 4:15 will change to 4.00 and if Date :13-05-2013 time: 4:25 then needs to 4.30 and sum the above total hours and results Total : 8.30 hrs.
I have written a function that returns the number of Days, Hours and minutes from a given number of minutes. On testinf the results are close but not quite there. Can anyone see where I have gone wrong or is there an easier way of doing this? Code is as follows:
CREATE FUNCTION dbo.GetTimeBetweenLong
(@StartTime DateTime, @EndTime DateTime, @CurrentDate DateTime)
RETURNS VarChar(50) AS
BEGIN
DECLARE @TotalTime Numeric
DECLARE @Minutes Numeric
DECLARE @Hours Numeric
DECLARE @Days Numeric
DECLARE @MinutesInDays Numeric
IF @EndTime IS NULL
BEGIN
SET @Days = DATEDIFF(Day, @StartTime, @CurrentDate)
SET @Hours = DATEDIFF(Hour, @StartTime, @CurrentDate) - (@Days * 24)
SET @Minutes = DATEDIFF(Minute, @StartTime, @CurrentDate) - ((@Days * 24)*60) - (@Hours * 60)
END
ELSE
BEGIN
SET @Days = DATEDIFF(Day, @StartTime, @EndTime)
SET @Hours = DATEDIFF(Hour, @StartTime, @EndTime) - (@Days * 24)
SET @Minutes = DATEDIFF(Minute, @StartTime, @EndTime) - ((@Days * 24)*60) - (@Hours * 60)
END
IF(@Days <0)
BEGIN
SET @Days = @Days - @Days - @Days
END
IF (@Hours < 0)
BEGIN
SET @Hours = @Hours - @Hours - @Hours
END
IF (@Minutes <0)
BEGIN
SET @Minutes = @Minutes - @Minutes - @Minutes
END
RETURN CONVERT(nVarChar(10),@Days) + ' Days, ' + CONVERT(nVarChar(5), @Hours) + ' Hours, ' + CONVERT(nVarCHar(5), @Minutes) + ' Mins'
END
declare @siva1 datetime;
declare @siva2 datetime;
set @siva1='2014-03-10 05:02:11'
set @siva2='2014-03-12 23:52:11'
i want output like this 2.18 means 2day 18hours difference how using query
Community:
This is my scenario: a Cube in SSAS, dimension time with an attribute "cdate" defined as datetime, model deployed using "Generate Model" option of Report Server.
Querying in Report Builder: only _date attribute of time dimension is selected. Filter use _date fields , from ...to option is selected, from the calendar I select from 1 january 2007 to 10 january 2007, results a list of dates between 1 january 2007 to 1 October 2007!!!
When I select from 1 january 2007 to 15 january 2007 the list is correct!!!
Clearly it interchange days and months in first case, and do it right in second case.
What is it about? it's about Report Server settings? SQL Server settings? .Net Settings? Windows Settings?
What do I have to do ?
Regards
Julio DÃaz C.
My table as data as follow,
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[table_Data]') AND type in (N'U'))
DROP TABLE [dbo].[table_Data]
GO
/****** Object: Table [dbo].[table_Data] Script Date: 04/21/2015 22:07:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[table_Data]') AND type in (N'U'))
[code].....
I have data coming from a telephony system that keeps track of when anemployee makes a phone call to conduct a survey and which project numberis being billed for the time the employee spends on that phone call in aMS SQL Server 2000 database (which I don't own).The data is being returned to me in a view (see DDL for w_HR_Call_Logbelow). I link to this view in MS access through ODBC to create alinked table. I have my own view in Access that converts the integernumbers for start and end date to Date/Time and inserts some otherinformation i need.This data is eventually going to be compared with data from someelectronic timesheets for purposes of comparing entered hours vs hoursactually spent on the telephone, and the people that will be viewing thedata need the total time on the telephone as wall as that total brokendown by day/evening and weekend. Getting weekend durations is easyenough (see SQL for qryTelephonyData below), but I was wondering ifanyone knew of efficient set-based methods for doing a day/eveningbreakdown of some duration given a start date and end date (with theday/evening boundary being 17:59:59)? My impression is that to do thiscorrectly (i.e., handle employees working in different time zones,adjusting for DST, and figuring out what the boundary is for switchingfrom evening back to day) will require procedural code (probably inVisual Basic or VBA).However, if there are set-based algorithms that can accomplish it inSQL, I'd like to explore those, as well. Can anyone give any pointers?Thanks.--DDL for view in MS SQL 2000 database:CREATE VIEW dbo.w_HR_Call_LogASSELECT TOP 100 PERCENT dbo.TRCUsers.WinsID, dbo.users.username ASInitials, dbo.billing.startdate, dbo.billing.startdate +dbo.billing.duration AS EndDate,dbo.billing.duration, dbo.projects.name ASPrjName, dbo.w_GetCallTrackProject6ID(dbo.projects.descript ion) AS ProjID6,dbo.w_GetCallTrackProject10ID(dbo.projects.descrip tion) AS ProjID10,dbo.billing.interactionidFROM dbo.projects INNER JOINdbo.projectsphone INNER JOINdbo.users INNER JOINdbo.TRCUsers ON dbo.users.userid =dbo.TRCUsers.UserID INNER JOINdbo.billing ON dbo.users.userid =dbo.billing.userid ON dbo.projectsphone.projectid =dbo.billing.projectid ONdbo.projects.projectid = dbo.projectsphone.projectidWHERE (dbo.billing.userid 0)ORDER BY dbo.billing.startdateI don't have acess to the tables, but the fields in the view comethrough as the following data types:WinsID - varchar(10)Initials - varchar(30)startdate - long integer (seconds since 1970-01-01 00:00:00)enddate - long integer (seconds since 1970-01-01 00:00:00)duration - long integer (enddate - startdate)ProjID10 - varchar(15)interactionid - varchar(255) (the identifier for this phone call)MS Access SQL statement for qryTelephonyData (based on the view,w_HR_Call_Log):SELECT dbo_w_HR_Call_Log.WinsID, dbo_w_HR_Call_Log.ProjID10,FORMAT(CDATE(DATEADD('s',startdate-(5*60*60),'01-01-197000:00:00')),"yyyy-mm-dd") AS HoursDate,CDATE(DATEADD('s',startdate-(5*60*60),'01-01-1970 00:00:00')) ASStartDT,CDATE(DATEADD('s',enddate-(5*60*60),'01-01-1970 00:00:00')) AS EndDT,DatePart('w',[StartDT]) AS StartDTDayOfWeek, Duration,IIf(StartDTDayOfWeek=1 Or StartDTDayOfWeek=7,Duration,0) ASWeekendSeconds,FROM dbo_w_HR_Call_LogWHERE WinsID<>'0'
View 3 Replies View Related
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
I have a float variable that holds a decimal number of hours.
So 1.5 equals 1 hour 30 minutes.
I need to change this to the format 1:30
Any idea how to do this?
I have an SQL code below which removes weekends and non working days when calculating days difference between two dates:
ce.enquiry_time represents when the enquiry was logged
(DATEDIFF(dd, ce.enquiry_time, getdate()) + 1)
-(DATEDIFF(wk, ce.enquiry_time, getdate()) * 2)
-(CASE WHEN DATENAME(dw, ce.enquiry_time) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, getdate()) = 'Saturday' THEN 1 ELSE 0 END)
-(SELECT COUNT(*) FROM nonworking_day WHERE nonworking_day.nonworking_date >= ce.enquiry_time AND nonworking_day.nonworking_date < dateadd(dd,datediff(dd,0,getdate()),1))
It works but I don't understand how it works it out. I am having issues understanding each coloured piece of code and how it works together.
Hello,I am writing a query to select records added to a table today, in the last 3 days, in the last 7 days, and so on.Here is what I have (which seems that its not working exactly). -- total listed today
SELECT COUNT (*) FROM mytable WHERE DATEDIFF(Day, mydatecolumn, getdate() ) <= 0-- total listed yesterday
SELECT COUNT (*) FROM mytable WHERE DATEDIFF(Day, mydatecolumn, getdate() ) <= 1-- total listed in the last 3 days
SELECT COUNT (*) FROM mytable WHERE DATEDIFF(Day, mydatecolumn, getdate() ) <= 3I'd like to be able to select the count for records added within the last X number of days. Can someone please help me out? Thanks so much in advance.
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
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]
)
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 RelatedWe have a few Servers that have space issues that need full DB Backups every day. As a result, we have to carefully watch how many DB Backup files we keep on the Server. On the Maintenance Plan, if I set the 'Delete files older than:' to '1' 'Day', will this delete anything over 24 hours old? What I have noticed, is that it tends to delete on Tuesday anything that was created on Sunday, but not Monday. Therefore, I am assuming that instead of '1 Day' being 24 hours, it is anything less than 47 hours and 59 minutes...
Is my assumption correct or do I need to look at something else. Thanks!
Billy
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 RelatedHi 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
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.
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.
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
Hi All,
I need to display the rent not paid months in the year. How can I form the query?
Help me.
Kamal.
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...
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
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.
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."
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
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