SQL Query - Date, Between Dateadd Problem
Jul 20, 2005
Hello...
I am running into a problem while running a query..can some1 help..
this is the query :
**************
SELECT *
from Table S
where S.dtDate1 BETWEEN dateadd(year,1,dateadd(month,-1,getdate()))
AND dateadd(day,-1,(dateadd(month,1,dateadd(year,1,dateadd(month,-1,getdate())))))
***************
(first part of the date calculation comes out to be '2005-05-01' and
second part is '2005-05-31')
Here is the interesting twist:
The query runs right if the first date in the between clause is
entered directly i.e. the query wud run right if i rite it as
SELECT *
from vCustomerProductDetail S
where S.dtDate1 BETWEEN '2005-05-01'
AND dateadd(day,-1,(dateadd(month,1,dateadd(year,1,dateadd(month,-1,getdate())))))
The funny part is the ending date in the between part is depending on
the starting date if u notice..so if there is somethg rong in the
first part there shud be somethg rong with the second too if u get wat
i am saying but the query runs right with the second part left as it
is..
can some1 please help..
thanks
-Raghu
View 1 Replies
ADVERTISEMENT
May 16, 2007
Hi,
Does any one know how I can use DATEADD and COVERT in the same select statement for retrieving data from a smalldatetime field?
I’m using ASP3.JS and MS-SQL db for a web based project
I want to retrieve a date, add a year to it, then convert it to dd/mm/yyyy format without the time showing. It works seperatly EG:
convert date - SELECT adId, CONVERT(CHAR(11),datefrom,103) AS datefrom1 FROM ...
Add date - SELECT adId, DATEADD(year, 1, datefrom) AS datefrom FROM....
How do you combine the 2 statements? I have been going slowly mad trying to figure this out
Thanks
View 2 Replies
View Related
Apr 3, 2008
I am doing monthly reporting for whole months, all starting on the 1st of each month and finishing on the last day of the month
I use dateAdd to calculate the end of the month - so I add 1 month and subtract 1 second (start time is always 00:00:00).
--dFrom is Jan 01 2008 00:00:00
select @dTo=dateadd(ss,-1,dateadd(mm,1, @dFrom))
when I print dTo it is not Jan 31 2008 23:59:59 but Feb 01 2008.
Why is this?
Only starts to behave when I set the number of seconds I am adding from -1 to -31.
Time of date is unimportant: as storing all dates as midday (any data type which ONLY supports date? not interested in the time really).
Subtracting one minute from midnight works fine, but that said: I am curious to understand why I am getting the funny behaviour above.
View 8 Replies
View Related
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
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
Apr 11, 2015
I created a function to use in a View, works similar to DATEADD but only with my company's Business days Monday-Thursday.
I am running a query but it never ends to run.
This is the function:
USE [RA_dev]
GO
/****** Object: UserDefinedFunction [Production].[GBDATEADD] Script Date: 4/11/2015 5:58:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[Code] ...
This is how I am trying to use it in the View:
Production.GBDATEADD(Production.Schedule.START_DATE, Production.Operations_Data_Pool.MFG_DAY - 1) AS SCH_DATE
View 2 Replies
View Related
Aug 3, 2015
I'm trying to return a query based on the dateadd function. I have a column in the database called date_added which is am successfully using the the DATEADD function above as date1. The Var1 variable I need to populate from the database too from a column called life_span which is an int data type. The error I get is An expression of non-boolean type specified in context where a condition is expected near select
My query is as follows: select guid, dateadd(day,life_span,date_added) as datepayday. From User_table
View 5 Replies
View Related
Feb 23, 2004
I have events which require certain things be done several days before the event and things be done several days after the event. I attempted to use the DATEADD function to subtract 3 days from the event date. The SQL Statement I created did just that, but, it displays 3 days back from today's date.
There are 2 tables:
CalendarCategories -- Table
CalCategoryID -- int field
CalCategoryName -- varchar field
CalendarEvents -- Table
CalCategoryID -- int Field
Title -- varchar Field
StartDate -- DateTime Field
I have to perform some tasks 3 days before the event. So, TODAY, I want to see a listing of those events which are scheduled for 3 days FROM NOW.
This is my current SQL Statement:
SELECT DATEADD(d,-3,StartDate) AS [Update Payoffs & Ins], Title AS [Closing Description] FROM CalendarEvents WHERE datepart(dd,StartDate)=datepart(dd,getdate()) AND datepart(mm,StartDate)=datepart(mm,getdate())
This SQL Statement takes TODAY'S events and gives them a date of February 20. See example of the results at http://www.joelwilliamslaw.com/DesktopDefault.aspx?tabid=141
This is what I have already done relative to my calendar listings:
Specific Event Types for the Current Month :
SELECT StartDate AS [Date and Time], CalCategoryName AS [Cls Type], Title AS [Closing Description] FROM CalendarEvents inner join CalendarCategories
on CalendarEvents.CalCategoryID = CalendarCategories.CalCategoryID
where (CalendarEvents.CalCategoryID = 1 OR CalendarEvents.CalCategoryID = 2 OR CalendarEvents.CalCategoryID = 3 OR CalendarEvents.CalCategoryID = 4 OR CalendarEvents.CalCategoryID = 20) AND (datepart(mm,StartDate)=datepart(mm,getdate()) AND datepart(yy,StartDate)=datepart(yy,getdate())) AND CalendarEvents.ModuleID = 360 ORDER BY StartDate ASC
Specific Event Types for the Current Day:
SELECT StartDate AS [Date and Time], CalCategoryName AS [Cls Type], Title AS [Closing Description] FROM CalendarEvents inner join CalendarCategories
on CalendarEvents.CalCategoryID = CalendarCategories.CalCategoryID
where (CalendarEvents.CalCategoryID = 1 OR CalendarEvents.CalCategoryID = 2 OR CalendarEvents.CalCategoryID = 3 OR CalendarEvents.CalCategoryID = 4 OR CalendarEvents.CalCategoryID = 20) AND (datepart(dd,StartDate)=datepart(dd,getdate()) AND datepart(mm,StartDate)=datepart(mm,getdate()) AND datepart(yy,StartDate)=datepart(yy,getdate())) AND CalendarEvents.ModuleID = 360 ORDER BY StartDate ASC
Your assistaince is much appreciated.
Joel
View 6 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
Jul 23, 2005
Hi Group!I am struggling with a problem of giving a date range given the startdate.Here is my example, I would need to get all the accounts opened betweeneach month end and the first 5 days of the next month. For example, inthe table created below, I would need accounts opened between'5/31/2005' and '6/05/2005'. And my query is not working. Can anyonehelp me out? Thanks a lot!create table a(person_id int,account int,open_date smalldatetime)insert into a values(1,100001,'5/31/2005')insert into a values(1,200001,'5/31/2005')insert into a values(2,100002,'6/02/2005')insert into a values(3,100003,'6/02/2005')insert into a values(4,100004,'4/30/2004')insert into a values(4,200002,'4/30/2004')--my query--Select *[color=blue]>From a[/color]Where open_date between '5/31/2005' and ('5/31/2005'+5)
View 2 Replies
View Related
Nov 3, 2006
Can someone tell me sql query for filtering date field for current day,not last 24hours but from 00:00 to current time?
View 2 Replies
View Related
Nov 1, 2013
I'm currently using the SQL to find records older than todays date in the SSD_SED field. I'm having to update the date manually each day. Is there a way I can automate this?
AND (SSD_SED < '2013-11-01')order by SSD_SED DESC
View 3 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
View Related
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
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
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
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
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