Fetch Data Of User Who Have Created Profile Within 7 Days - DateDiff Function
May 2, 2015
I have added one webpage designed in ASP.Net with C# and sql server 2005 as database. There is table for user registration in which there is a column for ProfileCreationDate the data type of that column is date time .
I would like to fetch data of those user who have created profile within 7 days. For getting desired result I am trying this query.
select Name ,Profession,ProfileCreationDate from tblRegistration where DATEDIFF ( Day , '" + System.DateTime.Now + "',ProfileCreationDate)<7 order by ProfileCreationDate DESC
System.DateTime.Now is a function for getting current date time in C#
The query is neither giving error nor giving desired result.
This is my first time to deploy an asp.net2 web site. Everything is working fine on my local computer but when i published the web site on a remote computer i get the error "Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed" (only in pages that try to access the database) Help pleaseee
does anyone know how i can make it so you can view all the users in the database on a page and there names are clickable with a link and then shows there profile page which is basicly a text box that all users can edit there own.
i have a "users" table and mysite stores there username in a cookie called user for the session.
I'm looking for a DATEDIFF function which will give me the BUSINESS days difference between 2 dates. e.g: datediff(day,'08/08/2007','08/14/2007) would normally result in 6 (i think), however in business days this would be 4. is there such a function? Thx
Hi! I'm trying to create a query to calculate the number of days between two dates, but I only want to include working days. Is there a way to do this?
with datediff all I can get it to return is months or a total of thedays...so if the difference in dates is 12 months 4 days how do I adjust theSQL to accommodate both?
I am trying to use the DateDiff function to calculate the difference between two dates in working days only... Is this possible in SSRS 2005, or can anyone suggest an alternate solution?
Hi I would like to return data for working days only. This will need to exclude holidays.For eg In the Month of August we have 31 Days and every 1st day of 1st week is holiday.So my output should retrieve me 31-4=27 . Any ideas?
Aim – Calculate the number of days between the [CreatedDate] and getdate, however if stage name = ‘Live Transactions’ then Calculate the number of days between [CreatedDate] & [CloseDate]
This is my query so far
SELECT [CreatedDate] ,[StageName] ,[CloseDate] ,DATEDIFF(dd,CONVERT(datetime,[CreatedDate]),GETDATE()) as Age FROM [FDMS].[Dan].[Raz_Reporting_LCS]
I have a user who cannot login to a .mdb when he is logged in to Windows, on his workstation or any other. No one can login to the .mdb when he is logged in to Windows.
He CAN login to the same .mdb if someone else is logged in to Windows, either on his workstation or any other.
When the user logs in he gets the following error:
MS SQL Server Login
Connection Failed:
SQL State: €˜28000€™
SQL Server Error 18456
[Microsoft][ODBC SQL Server Driver][SQL Server] Login failed for user €˜domainusername€™
I have removed and recreated the ODBC Connection while he is logged in. I have tried changing the trusted login to administrator (in the window that pops up when he fails to connect).
He has the permissions he needs to the .mdb because he can login under anyone else€™s profile.
I tried copying his profile to a brand new user and ended up with the same result.
This leads me to wonder if there is some corruption in one of his profile€™s .dat files or ???
This is the boss€™ account and he does not want to be given a new username and profile€¦
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Running on MS Windows 2003 Server Standard SP1
All workstations are XP SP2 with MS Access 2003 SP2 (from Office Pro distro)
We use roaming profiles. All systems within the same physical and logical network and domain.
Any help you can provide will be greatly appreciated.
Please let me know if there is any additional information you need.
So I am trying to work out the difference between today's date (GETDATE()) and a Target Date in a specific table (targetdate)
When I use the DATEDIFF function it is including non working days in the calculation (weekends and bank holidays). Although our date calandar table provided to us from a third party supplier will tell you the weekends, it does not tell you the bank holidays.
Luckily there is another table in the database called - ih_non_work_days.
The format of the date is "2014-12-25 00:00:00.000" for example in that table.
How do I using my "targetdate" and today's date calculate in days the differance - excluding the dates that exist in the ih_non_work_days database?
So for now my basic script looks like -
SELECT com.comm_reference AS 'Referance' ,com.current_task_target_date AS 'TargetDate' , DATEDIFF(D,com.current_task_target_date,GETDATE()) AS 'Incorrect Date Calculation' FROM [dbo].[em_communication] as com
I have a four tables called plandescription, plandetail and analysisdetail. The table plandescription has the columns DetailQuestionID which is the primary and identity column and a QuestionDescription column.
The table plandetail consists of the column PlanDetailID which the primary and identity column, DetailQuestionID which is the foreign key attribute of plandescription table and a planID column.
The third table analysisdetail consists of a analysisID which the primary and identity column, PlanDetailID which is the foreign key attribute of plandetail table and a scenario.
Below is the schema of the three tables
I have a two web form that will insert, update and delete data into these three tables in a two transaction. One web form will perform CRUD operations in plandescription and plandetail table. When the user inserts QuestionDescription and planid in this web form, I will insert the QuestionDescription Value in the plandescription table and will generate a DetailQuestionID value and this value is fed to the plandetail table with the planid. Here I will generate a PlanDetailID.
Once this transaction is done, I will show the second web form in which the user enters the scenario and this will be mapped with the plandescription using the PlanDetailID.
This schema cannot be changes as this is the client requirement. When I insert values I don’t have any problem. However when I update existing data, I need to delete existing PlanDetailID in the plandetail table and recreate PlanDetailID data for that DetailQuestionID and planID. This is because, the user will be adding or deleting a planID associated with the QuestionDescription.
Once I recreate PlanDetailID for that DetailQuestionID and planID, I need to update the old PlanDetailID with the new PlanDetailID in the third table analysisdetail for the associated analysisID.
I created a #Temp table called #DetailTable to insert the values analysisID, planid and old PlanDetailID and new PlanDetailID so that I can have them in update statement once I delete the data from plandetail table for that PlanDetailID.
Then I deleted the plandetailid from the plandetail table and recreate PlanDetailID for that DetailQuestionID. During my recreation I fetched the new PlanDetailID’s created into another temp table called #InsertedRows
After this I am running a while loop to update the temp table #DetailTable with the newly created PlanDetailID for the appropriate planID’s. The problem is here. When I have the same number of planID’s for example 2 planID’s 1,2 I will have only two old PlanDetailID and new PlanDetailID for that planID and analysisID.But When I add a new PlanID or remove a existing planID I am getting null value for that newly added or deleted planID. This is affecting my update statement of analysisdetail table as PlanDetailID cannot be null.
I tried to remove the Null value from the #DetailTable by running the update statement of analysis detail in a while loop however its not working.
DECLARE @categoryid INT = 8 DECLARE @DetailQuestionID INT = 1380 /*------- I need the query to run for the below three data. Here i'm updating my planids that already exists in my database*/ DECLARE @planids VARCHAR(MAX) = '2,4,5'
Could somebody please help me out? I'm running Windows 2000 on my laptop. I opened a new user account and gave it administrative privilegies. I copied the administrative profile to the new account and everything seemed to work except when I tried to open any office application a dialogue appeared prompting me to place the Windows 2000 office CD rom in the drive. Why has this happened? How can rectify this problem since I don't have the CD rom.
after i login, i should be able to see the login person's profile, and edit it. anyone know how to do this?currently i am using grid view to display the profile, and created a store procedure in database ALTER PROCEDURE [dbo].[select_customer_by_login] -- Add the parameters for the stored procedure here(@Username nvarchar(50))AS SELECT C.CustomerName, C.TFN, C.Address, C.City, C.State, C.PostCode, C.Phone From dbo.Login L, dbo.Customer C Where L.CustomerID = C.CustomerID and L.UserID=@Username; @Username is the login account when people login. the error output is Procedure or Function 'select_customer_by_login' expects parameter '@Username', which was not supplied.
Hi, I am facing problem rite now.. I want to calculate the date different minutes between 23:00:00 and 01:00:00. My code : datediff(Minute,'01:00:00','23:00:00') The result is 1320 minutes. (22 hours)... But, the result that I want is 120 minutes (2 hours).... Can anybody help ??? Thanks in advance...
Hi,I'm using the datediff function to display the ages of the users in my database. However the age rounds up once they are 35.5 etc...I could create another function which works similar to the DateDiff function, but use math.floor to always round down, but I need to use this function in a SQL statement WHERE clause. Is there any way around this?Thanks,Curt.
I am trying to break up the age into column from a dob field for a cross tab report. I can query the datediff with an alias but can not individually change the columns I need like a virtual or temp column, but can't figure out how to accomplish it.
use 'Database'
Select datediff(Year, dob, getdate()) As "Under 22", datediff(Year, dob, getdate()) As "22-45",
datediff(Year, dob, getdate()) As "46-65", datediff(Year, dob, getdate()) As "Over 65"
from 'Table'
WHERE (DATEDIFF(yy, dob, GETDATE()) < 22 OR
DATEDIFF(yy, dob, GETDATE()) BETWEEN 22 AND 45 OR
DATEDIFF(yy, dob, GETDATE()) BETWEEN 46 AND 65 OR
DATEDIFF(yy, dob, GETDATE()) > 66) AND (status = 'Current' OR
Hello I have a report i want to cache each night. To make this possible, we do not use parameters but use filters based upon de user that is executing the report. So we can cache the report the first time any user executes it and the same report is already cached for all users becasuse the security is based on filters, not in query parameters.
When i try to schedule it, i get the message: "user profile dependencies and cannot be run unattended rsHasUserProfileDependencies"
Although user variable paramter is not used in the query and it is only used in the filter when any user views the report RS dont allow me to schedule it.
How can I schedule this report to be executed all night unattended ?? The user is not a query parameter so it should allow it.
This will give you the time information about how apart two dates are.CREATE FUNCTION dbo.fnTimeApart ( @FromTime DATETIME, @ToTime DATETIME ) RETURNS @Time TABLE ([year] SMALLINT, [month] TINYINT, [day] TINYINT, [hour] TINYINT, [minute] TINYINT, [second] TINYINT, [millisecond] SMALLINT) AS BEGIN DECLARE@Temp DATETIME, @Mts INT, @year SMALLINT, @month TINYINT, @day TINYINT, @hour TINYINT, @minute TINYINT, @second TINYINT, @millisecond SMALLINT
RETURN ENDAnd to test the functionSELECTd.FromDate, d.ToDate, x.* FROM( SELECT'19690906' AS FromDate, '19760608' AS ToDate UNION ALL SELECT'19991231', '20000101' UNION ALL SELECT'20070207', '20070208' UNION ALL SELECT'20000131', '20000228' UNION ALL SELECT'20070202', '20070201' UNION ALL SELECT'20070207', '20070307' UNION ALL SELECT'20000131', '20000301' UNION ALL SELECT'20011231 15:24:13.080', '20020101 17:15:56.343' UNION ALL SELECT'20011231 17:15:56.343', '20020101 15:24:13.080' UNION ALL SELECT'20020101 15:24:13.080', '20011231 17:15:56.343' UNION ALL SELECT'20000131', '20000229' ) AS d CROSS APPLYdbo.fnTimeApart(d.FromDate, d.ToDate) AS x ORDER BYd.FromDate, d.ToDateAnd the output isFromDateToDateyearmonthdayhourminutesecondmillisecond 19690906197606086920000 19991231200001010010000 200001312000022800280000 200001312000022900290000 20000131200003010110000 20011231 15:24:13.08020020101 17:15:56.34300115143263 20011231 17:15:56.34320020101 15:24:13.08000022816736 20020101 15:24:13.08020011231 17:15:56.34300022816736 20070202200702010010000 20070207200702080010000 20070207200703070100000 Peter Larsson Helsingborg, Sweden
I have two date columns one is sent_date and other is approved_date my requirment is to find the difference between the two dates which can be minutes/hrs/days. using datediff function iam able to get it in minusts or hrs but my output should be of the format hh:mm 23:10 (ie 23 hrs and 10 min) or say 48:00 (for 2 days)
recently i created a system using php and mysql to record faults that the ict technicians could get reported faults out of. so far it does everything i want but some senior members of staff now want the front end to give a report of status, one of the things they wish to see is how many jobs have been completed this week month and year
within my tables is a field date. naturally it has the date submited contained within it. the code i have been using to try and get an output is
mysql_query("SELECT date, datediff('day', date, CURRENTDATE) FROM softwarerepairtable WHERE datediff<= '7' and complete='yes' ");
the complete feild is another contained within the datebase. i have also tried SELECT * datediff('day', date, CURRENTDATE) FROM softwarerepairtable WHERE datediff<= '7' and complete='yes' ");
unfortunatly my limited knowledge of php and sql is holding me back on this. any help guys would be appriacted
We have a previous SQL 2012 cluster that emails us when a new database is added. I am unable to figure out why we get this error any time we add a new database to it, and also it prevents us from adding a new availability group to this cluster because of this error.
I also am unable to figure out what profile it is talking about as this was setup before me and I am not a DBA.
I'm trying to use the DateDiff function within my select statement, but I'd like to add the parameter of greater than 30 days. This will have the query only return records where my bill stop date is greater than 30 days from the completion date. I currently have the datediff function within my select statement as
DATEDIFF (d,A.StopBillDate, a.CompletionDate) as [DIFFERENCE]
I would prefer to keep the datediff function within the select statement so as to have difference in days appear as a column within my output.I have been unable to add the parameter of > 30 days to the query without getting an error.
Hi Experts,I am working on SSRS 2005, and I am facing a problem in counting theno of days.My database has many fields but here I am using only two fieldsThey are Placement_Date and Discharge_DateIf child is not descharged then Discharge_Date field is empty.I am writing below query to count the number of days but is is notworking it is showing the error"The conversion of a char data type to a datetime data type resultedin an out-of-range datetime value."select casewhen convert(datetime,Discharge_Date,103) = '' thendatediff(day,CONVERT(datetime,Placement_Date,103), GETDATE())elsedatediff(day,CONVERT(datetime,Placement_Date,103),CONVERT(datetime,Discharge_Date,103))end NoOfDaysfrom Placement_DetailsSo please tell me where I am wrong?Any help will be appriciated.RegardsDinesh
When running a query such as this on a database view:
select count(*) from WWALMDB.dbo.v_AlarmEventHistory2
SQL throws the following error:
The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.
Oddly on a Table the count(*) function works.
Is there a limit on views or the count(*) function that I am not aware of?
