473,231 Members | 1,630 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,231 software developers and data experts.

SQL Query - Date, between dateadd problem

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
Jul 20 '05 #1
1 17289
On 1 Jun 2004 11:33:04 -0700, Raghu wrote:
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


Hi Raghu,

You forgot to mention what exactly went wrong. That's usually needed to be
able to help.

In this case, however, I think I can guess what your problem is. Either
some or all rows with dtDate1 equal to May 1st are omitted.

The datetime datatype stores a combination of date and time. If you enter
only a date, a default time of midnight is assumed. Try the following:

SELECT convert(datetime,'2004-05-01')

Your calculation is based on adding and subtracting whole days and months
to getdate(), a function that returns both current date and time. The time
is retained as it is. See for yourself:

SELECT dateadd(year,1,dateadd(month,-1,getdate()))
returns
2005-05-01 21:08:55.937

All dates in your database of May 1st, midnight are before this date, so
they are not selected. To include them as well, you need to trim off time
portion. This can be done by converting to character, using a style code
that leaves the time out. The char value will be converted back to
datetime, but the time portion is lost at that time.

SELECT dateadd(year, 1, convert (varchar,
dateadd(month,-1,getdate()), 106))
returns
2005-05-01 00:00:00.000

This suffices if all dates stored in dtDate1 are stored with the default
time (00:00:00:000). If other times are stored as well, you'll have to do
something about the end date as well.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 20 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Mark | last post by:
I have an Access 2000 database that pulls a file in everyday via the TransferText method. The file is named "psjrnl 25-Sep-2003.txt" (the 25-Sep-2003 will change and have the current days date). My...
8
by: Ishbel Kargar | last post by:
Since upgrading from old laptop (Windows 98) to new laptop (Windows XP), my mail-merge letters are doing strange things with date formats. For instance, my reminder letter for lapsed subs carries...
1
by: Brian Jorgenson | last post by:
I am looking for a formula to put in my query to pull data based on the last 3 months. It starts with the current day and will go back 3 months. Here is my wrkflow language for example: ...
5
by: Ian Davies | last post by:
Dear Access expert I am trying to use an mde application developed in Access2k with Access 2002. I get an Access error box: Function is not available.. Date() etc. Is Access 2002 back...
2
by: Thomas Beyerlein | last post by:
I am binding dates to a textbox, the date is stored in SQL in a datetime field. When it gets bound it turns it into a long date (Sunday, Dec. 25 2005), in SQL when viewing the table it views as a...
1
by: zsolt | last post by:
Hi, I'm trying to convert a string to date by specifying the format. The value is like this: "03rd of April 2006". Now this is converted fine by using the following format: "dd\r\d \o\f MMMM...
2
by: zdk | last post by:
I have table name "actionlog",and one field in there is "date_time" date_time (Type:datetime) example value : 11/1/2006 11:05:07 if I'd like to query date between 24/07/2006 to 26/07/2006(I...
2
by: siyoyok007 | last post by:
Hello, now i have two DTPicker, and one MS calendar, DTPicker1 is for the start date and DTPicker2 is for the end date. My problem is, how can i only enable only the date between DTPicker1 and...
1
by: IamMacro | last post by:
hulp5 = "02-02-2006" timesynced = Date.Parse(hulp5) Dim msqlDA As New MySqlDataAdapter("select * from Work where DateCreated <' " & timesynced & " ' ", myconnection) I cant seem to get the...
19
by: phill86 | last post by:
Hi I am re-posting this thread because it has become very confusing and I have got some way to solving the problem so it is a slightly different question from the initial thread. here is the...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.