I am trying to find a group of clients that were entered last week. I am getting all the dates to go with the Monday of the week they were entered. i.e. If they were entered on 8-2 or 8-3, they go with 8-1. If they were entered, 8-9, they will be under the 8-8 Monday.
No problem with finding the Mondays using either the nice 'weekcommence' function I found here or using numerous t-sql examples.
One thing I don't understand is- how come I can't use an equal sign in my syntax?
For instance,
SELECT dbo.weekcommence(date_added)
FROM clients
where dbo.weekcommence(date_added) = dateadd(wk, -2, GETDATE() )
won't return any results. If I use a greater than sign, I get this week, 8-8 and last week 8-1. I only want to see the week of 8-1.
Now- look at what I have below: Shouldn't I really be able to see ONLY last week's Mondays with this?
To me- the way this reads is: show me 2 weeks ago, from these dates (first Mondays function).
The results are the last two MONTHS, not weeks. What am I misinterpreting?
I can also use this:
SELECT dbo.weekcommence(date_added) FROM clients
WHERE (dbo.weekcommence(date_added) >= dateadd("d",-7,DATEADD(wk, DATEDIFF(wk,1,getdate()), 0)))
and get the week's of 8-1 and 8-8. But I can't get JUST 8-1 if I take out the greater than sign.
To me- this one reads: Select all my Mondays from the function. Show me Mondays from my function that are = 7 days from this Monday's date.
It seems like it should be straightforward, but I'm obviously missing something big. Any help?
Just want to double check this.To add 30 days to the current date in a stored procedure using SQL Server should be this: DATEADD(day, 30, GETDATE())Right?Thanks,Zath
I am trying to calcuate the finalsuit date for every 2 years becasue that when employees have to get their gaming license renewed, so I'm trying to add the renewal year by 2 because they have to get it done every two years
If I am running a scheduled job at the first of the month and I want to run an update sequence for the previous month, i was essentially using this sql:
What is the correct format for getting the last three months of data. in sql its DATEADD(mm-3,getdate()) but for some reason the report doesnt want to take this? what am i doing wrong?
I am using Derived Column Transformation Task to format my data as I am converting my DTS to SSIS. One of the columns requires to use DATEADD where I add 1 month and substruct one day from the date that I get in the flat file.(note that I always hardcode the day to "01" regardless of what I get in my flat file)
in managed code I get the right answer 11/1/2006. Between 1753 and 2007, there should have been 2007 - 1753 = 254 yrs x approx 365 = 92,710 days. Where is the year 2153 coming from?
Hi, I have a problem with working out some dates. I have a query that has a start date field and a number of days field. I know i can create another field that could provide the return date (DATEADD function) by adding the number of days to the start date. However the problem I have is that i need to discount the weekends from the return date. For example if the start date was a wednesday and the number of days was 3 the datadd sum would give a return date of saturday when in reality it should be monday. I am not sure if i am making sense but if anyone out there has any ideas it would be more than welcome. Andrew
hello, i have a Pictures table: PictureID, Name, Description, DateAdded (GETDATE() when insert), IsActive... i need to make some stored procedures to show me the pictures added in last 24hours, in last 3 days, last 2 weeks and so on the pictures added in database are active (available to be seen by users) only 1yaer after the date added I tryied to make a stored procedure (in fact i maked a lots of them, for 1day 3 days 1 week 1 month), but i have a problem with that DateDiff and DateAdd Here is what i tryied CREATE PROCEDURE LastAdded_2monthsAgo
AS
SELECT Pictures.ProductID, Pictures.Name, Pictures.Description, Pictures.DateAdded FROM Pictures WHERE (DATEDIFF(month, Pictures.DateAdded, GETDATE()) >= 0) AND (DATEDIFF(month, Pictures.DateAdded, GETDATE()) <= 2) ORDER BY DateAdded DescI have a feeling that is wrong, please make your own version and show me what i should write...I don't know what should be first the today date or the DateAdded...i need to select the last added products from a specific interval of time...Should i do something with that "1 year available" like WHERE (DATEDIFF(month, GETDATE(), DATEADD(year, 1, Products.DateAdded)) >= 0) AND (DATEDIFF(month, GETDATE(), DATEADD(year, 1, Products.DateAdded)) <= 2) I am sure is a stupid thig up there...if you can, make your own version how you would do it and show me..please help me
Hi ALL!anyone can help me resolve this problem.I create SQL sentence and using DATEADD to Update DATETIME FIELDand i need Increase 1 Year and Month is 04 and day is 01.example: origin datetime field : 2007/06/26result after update is : 2008/04/01so by DATEADD(yy,1, datefield)?Have any expression for datefield to set month and day like what i need?Thanks .
:eek: Hi, I'm getting problem while using DATEADD Function.
When I use DATEADD(MONTH, 1 '01/31/2005'), it returns 02/28/2005 and when I use DATEADD(MONTH, 1 '02/28/2005'), it returns 03/28/2005 but I want the result as 03/31/2005 ie last day of the month.
hi, can something tell me what function do i need if i want to know what happen the last three hours? for example: dateadd (hh, -3, getdate()) it seems not working?!!
Hi I'm trying to break down some code to work out how it's working. I've encountered DateDiff and DateAdd and I think this adds a new date of midnight today after reading up on the syntax (still dont fully understand how it works to be honest)
SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
However, this is slightly different because where there is a 0 above there is a 13 in this one so I'm wondering what the 13 is actually doing.
I want it to only list records with LastDateIn 10 days prior to today's date.
CREATE Procedure [dbo].[spRMU_NoFilesBookedInByDay] AS
SELECT Convert(nvarchar, LastDateIn,103) AS Expr1, COUNT(Status) AS Expr2 FROM tblFiles where Convert(datetime, LastDateIn,103) < dateadd(day, -10, getdate()) GROUP BY Convert(nvarchar, LastDateIn,103) ORDER BY Convert(nvarchar, LastDateIn,103) DESC
can i have brief explaination of datediff and dateadd function, regarding their arguments and params, definitions.. like the books online definition.. anybody have the books online url?
becoz im curious what this line do in detail(found it in http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62729 : count age function and it's kewlLLl)
I need help in T-SQL.I am using DATEADD function and I want to add 6 months to a date. Butit does not return me the rusults, which I wante.g. SELECT DATEADD(m,'20040630') returns 20041230which is logical correct? But I want it to return end of month (i.e.20041231)Any help in this context will be highly appreciatedAbdul N. Khan
I have a bigint column called "MillisecondsSince1970" that I need to convert to a date - SSIS is erroring out when I use DATEADD with the 8 byte int (if I use 4 byte it works but the column is bigger than 4 byte). The error is really lame:
[Derived Column [79]] Error: The "component "Derived Column" (79)" failed because error code 0xC0049067 occurred, and the error row disposition on "output column "Date" (100)" specifies failure on error. An error occurred on the specified object of the specified component.
Anyone have a way around it... a VB.NET equivalent of DATEADD or something else I can do?
Problem: I have two nearly identical querys that are running. One of the query's is using the SQL DATEADD function. These query's are running on a SLQ2005 MSDE. There is now service pack four applied to this server. The question is: Are there any known issues with the DATEADD function in SQL 2005? This query is used in a report that has been in use for about two years. There were some changes made to the server (BIOS upgrade) including adding this service pack. These changes did not cause any other SQL issues. This query now takes almost five minutes to run. The nearly identical query (the difference is the date is passed in as a parameter rather than using the DATEADD function) takes from one to two seconds. These reports have a history of nearly identical running time.
What is a good way to try and correct the issue or at least to try and isolate the problem? Have there been any known issues with this function?
Heres My SQL to get me 2 hours back of Midnight, two months ago (so 11pm of the prev day)
SELECT DATEADD("hh",-2,DATEADD("m",-2,DATEADD(dd,-(DAY(GetDate())-1),DATEADD(day,DATEDIFF(day, 0, GETDATE()),0)))) So I converted it into RS for my report start date, but it's not running...cant even get preview to load, it detects an error right away.,,just doesnt tell me what it is
I'm pretty sure I have the "h", "m" and "d" syntax right, I tried them all individually and they give me the correct dates...other than changing GetDate() to Now() I dont know what makes this diffenent...is it just too much nesting to Reporting Services?
I'm using DTS for the first time and am having difficulty understanding some of its usage. In essence, I have 70 tables which are on an ORACLE database. I've created the schema on SQLServer from an ERWin model. I now wish to use DTS to import the data into SQLServer. Of course there are referential integrity constraints. The DTS 'import wizard' seems oblivious of these and carries out the import in alphabetical order! I'm now trying the DTS designer (which I don't fully understand yet). It appears that I have to create a workflow which moves the workflow from table to table in the order dictated by RI - this is not a trivial task! (I want point and click!!). Are there any shortcuts I can take to have DTS automatically arrange the workflow in the correct order? Can I do it programaticaly? I don't want to have to fiddle with 140 'workflow' and 'success' links. Cheers.
I am using the dateadd function to return a recordset. The function seems to allow me to plug variables into parameter2 if its a valid number type, and paramter3 as long as its a valid date type. How ever the function is not allowing me to use a variable into parameter1. I have tried a varchar and all other data types and can't get it to work. Any thoughts?
As a newcomer to SQL2000, I need to create a DTS Global Variable that will import a file daily automatically. I've got the DTS working great, but I have to manually execute it every day. The log file name Accounting 02-22-2002.csv changes by the date every day. So the next record set would be Accounting 02-23-2002.csv and so on. Any ideas on how to create this? I think some VBScript is necessary from what I understand. I'm looking at the function DATEADD(d, 1, date).
Hi, I encountered a strange error today. I have an Integer parameter "hours" that I am trying to use in my SQL Query.
DATEADD(hh, @hours, @startTime)
It works if I have it set such as DATEADD(hh, 8, @startTime), but I need that parameter there for what I need.
I get this error:
Error Source: System.Data Error Message: Failed to convert parameter value from Decimal to DateTime. I tried a variety of CInt and other conversion functions to no avail.
I'm creating a data flow task to export a set of records that were created within a specific time frame. The date offsets I'm using are read into user variables that are of type Int32. I have an OLEDB source connected to a SQL Server 2005 database using the following query to get the records I want:
select * from claim where date_created > dateadd(day,?,getdate)
I've mapped Parameter0 to my offset variable, which has a value of -7. When I hit OK to close out of the OLE DB Source editor, I get a message saying "Argument data type datetime is invalid for argument 2 of dateadd function." I can't figure out why it keeps talling me this even though the variable I'm passing in is an integer, not a datetime. I've done a lot of searching and found some instances of other people having this problem, but so far no answers. I could just go ahead and try to create an equivalent query using datediff or something, but I'd like to know what's going on here. Is this a bug in Integration Services itself, or is there another explanation?
I was tasked with optimizing a query that was taking about 2.5 minutes to run. It is now running in 2 seconds, so great, it's working....except I don't understand why. The ONLY change made is the where...
Here is the origional
Code Snippet
WHERE (j.datelaunched > GETDATE() - 7 OR (j.jobtype_id = 2 AND j.datecreated > GETDATE() - 30)
Here is the optomized
Code Snippet
WHERE (j.datelaunched >= DATEADD(DD, DATEDIFF(DD,0,GETDATE() - 7), 0) OR (j.jobtype_id = 2 AND j.datecreated >= DATEADD(DD, DATEDIFF(DD,0,GETDATE() - 30), 0)))
when i write this sentence in textbox in reporting service 2005 : = iif(DateAdd( "h",Parameters! t.Value,Fields! completionTime. Value),"" ,Fields!completi onTime.Value)
I have a varchar field that contains values to represent the month and year in the format mmyy (eg. 0107, 0207, 0307 etc).
I want to be able to select the value that is 6 months before the current month and year, for example if I were running the query in the current month/year (April 08) I would return the value 1007 (October 07).
