Dateadd Confusion

Aug 10, 2005

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?

select dateadd(wk, -2, dbo.weekcommence(date_added))from clients

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?

View 8 Replies


ADVERTISEMENT

Dateadd

Oct 17, 2006

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

View 1 Replies View Related

Dateadd In RS

Mar 1, 2006

Hi

Hope someone can help.

I've got to produce a report that filters information dependent on the purchase date based on various parameters.

The parameters are:

@yearend (Datetime)
@months(int)

the script that I wrote in SQL is

select assetno from assets where purch_date < = dateadd(mm,-@months, @yearend)

but when I try to use this in the dataset it comes up with the following error:

"Application usess a value of the wrong type for the current operation"

Any help would be very much appreciated.

View 3 Replies View Related

Dateadd

Feb 6, 2006

Hi guys I am trying to convert this to date add because MS SqL and Jet SQL dont exactly speak the same language

=DateSerial(Year([FINALSUIT])4,Month([FINALSUIT]),Day([FINALSUIT]))

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

View 2 Replies View Related

Dateadd()

Mar 22, 2007

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:

@startdate = dateadd(mm, -1, getdate())
@enddate = dateadd(dd, -1, getdate())

So, will the @enddate represent the last day in the previous month or will that not work?

The Yak Village Idiot

View 1 Replies View Related

Sum Iff With DateADD

Apr 17, 2008

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?






Code Snippet

=IIF(Sum(iif(Fields!Database_Size_Datetime.Value >= DATEADD(mm, - 3, GETDATE()), Cdbl(Fields!MonthlyDBTotal.Value), 0.0))/ 3)





By the way, im trying to get the average over the last three months by doing this.

View 3 Replies View Related

Need Help With DATEADD

Sep 26, 2006



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)

The current script looks like this:

DTSDestination("PeriodEndDate") =
dateadd("d",-1,dateadd("m",1,cdate(mid(cstr(DTSSource("Col004")),1,4) & "-" & mid(cstr(DTSSource("Col004")),5,2) & "-" & "01")))

Does any one know how to convert it so it works in my derived column?

Thanks...

Maria

View 3 Replies View Related

Bug In Dateadd?

Nov 19, 2007

when I execute the following:

select dateadd(dd,92710,1/1/1753)

I get 2153-10-31 00:00:00.000

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?

View 1 Replies View Related

DATEADD - Add Weekdays Only

Sep 20, 2006

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

View 8 Replies View Related

DateDiff / DateAdd - Please Help Me:(

Sep 28, 2007

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

View 5 Replies View Related

DATEADD Function?

Feb 21, 2008

 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  .

View 2 Replies View Related

DateAdd Function

Jul 23, 2007

Hi,

Can someone please tell me why the following won't work:


Code:

SELECT * FROM Validation where CourseDate > DateAdd(wk, -3, GetDate())



I want to select all records where the coursedate is more than 3 weeks old.

Thanks.

View 2 Replies View Related

Using DATEADD Function

Jan 3, 2005

: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.

View 10 Replies View Related

Function Dateadd

Nov 9, 2006

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?!!

View 2 Replies View Related

DATEDIFF And DATEADD

Apr 22, 2008

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.

SELECT DATEADD(dd, DATEDIFF(dd, 13, GETDATE()), 0)

In this one there's a 21

SELECT DATEADD(dd, DATEDIFF(dd, 21, GETDATE()), 0)

Can someone guide me please?

Thanks
Daniel

View 5 Replies View Related

DATEADD Function

May 23, 2008

I'm using a query that is not retrieving all of the data it should because of the datetime stamp. If I use the following in the where clause:

{table.date} between '4/1/2008' and dateadd(day, 1 '4/30/2008')

that works fine for April but not for other months, where I might get more data from the following month than I should.

Is there a way to add hours, minutes, and seconds (23:59:59), all at the same time, without moving ahead an entire day?

View 8 Replies View Related

Using DateAdd And Convert

Jan 18, 2007

Here's my sp which doesn't do what I want

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

GO

View 5 Replies View Related

Datediff And Dateadd

Feb 11, 2007

hi all,

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)

datediff(mm,dateadd(yy,AGE_IN_YEARS,START_DATE),END_DATE)
thanks

~~~Focus on problem, not solution~~~

View 8 Replies View Related

SQL DATEADD Function

Jul 23, 2005

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

View 2 Replies View Related

DATEADD With 8 Byte Int?

Dec 18, 2006

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?

View 3 Replies View Related

DATEADD Function

Sep 7, 2007

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?

Thanks for any help!

View 2 Replies View Related

DateAdd SQL Query Different In RS?

Apr 21, 2008

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



=DATEADD("h",-2,DATEADD("m",-2,DATEADD("d",-(DAY(Now())-1),DATEADD("d",DATEDIFF("d", 0, Now()),0)))

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?

View 5 Replies View Related

DATEDIFF, DATEADD

Nov 16, 2007



How was the DATEDIFF function written? I would like to know the logic behind it.

Also what does it mean to say

SELECT DATEDIFF(MONTH, 30, '2004-12-30')


I thought it accepts two dates only. but instead, it took 30 as start date and returned 1259!!

Thanks

View 1 Replies View Related

DTS Confusion...

Nov 28, 2001

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.

View 1 Replies View Related

Confusion In AWE

Apr 26, 2007

suppose i have enabled only AWE option in sql server but i didn't add 3

GB switch in boot.ini. in this case what will be the effect on physical memory?

View 9 Replies View Related

Variables In DATEADD Function

Aug 22, 2000

DATEADD(par1, par2, par3)

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?

View 1 Replies View Related

DTS Global Variables DateAdd

Jan 22, 2002

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).

View 1 Replies View Related

Can't Use Parameter With DateAdd Function??

May 18, 2007

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.





Any ideas?

View 5 Replies View Related

Using Dateadd In A Parameterized Query

Apr 15, 2008

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?

View 7 Replies View Related

Why Is Getdate() Slower Than DATEADD()

Aug 22, 2007

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)))

View 10 Replies View Related

Can't Use Integer Parameter With Dateadd?

May 11, 2007

Hey guys I have the following in my SQL statement:

DATEADD(hh, @hours, @startTime)

hours is an integer and startTime is actually a string, but the thing is that this statement works fine if I use it like this:

DATEADD(hh, 8, @startTime)

The first version is what I need so that the user can control from a parameter and I get the following error:


Error Source: System.Data
Error Message: Failed to convert parameter value from a Decimal to a DateTime.

Thanks!

BJ

View 2 Replies View Related

How To Ignore Null Value With Dateadd

Oct 4, 2007

Salamo alikom,

when i write this sentence in textbox in reporting service 2005 :
= iif(DateAdd( "h",Parameters! t.Value,Fields! completionTime. Value),"" ,Fields!completi onTime.Value)

Because this column contain null value.

So how to ignore null value with dateadd

AnyBody Can Help me.

View 3 Replies View Related

DATEADD And Char Field

Apr 25, 2008

Hi,

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).

Is someone able to help me with this please?

Thanks

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved