If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > Using BETWEEN with SQL Server Dates

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Jan 2004
Posts: 145
Using BETWEEN with SQL Server Dates

I'm converting my Access 2003 DB to SQL Server 2005 Express and I use date range comparisons all the time. I learned that the dates also include a time in SQL Server which to me right now seems is going to be a hassle. I don't need the time at all. There will be many times when the current date will be input into a record. All I want is the date part. My first thought was to extract the date part each time I need to use it, but it would be better if I could come up with a test condition that will take any date/time and give the right results. I read about one that uses the following:

get the any dates between 1/1/2006 and 12/31/2006.

The test conditon would be:

SELECT * WHERE(date >= 1/1/2006 AND date < 1/1/2007)

You add a day to your upper range and use the less than operator. This wouldn't be so bad for static dates, but each time I used a dynamic date I'd have to add one day to the upper range before testing. I also read using any kind of function or the "Like" operator on a date renders the indexes useless.

I guess what I'm looking for is method to get the results similar to using the "BETWEEN" statement where I don't have to manipulate any of the dates prior or during testing.

My thought right now is from a different perspective. Any time I need the current date I'd truncate the time part leaving me with a date and a time part of "00:00:00". My only problem with this is what if in code I accidentally forget to truncate the time. I could see this happen giving how much I need to use the current date and being used to not having to worry about the time part.

Any help with this will be greatly appreciated. I think this is my last major hurdle before I'm convinced everything will convert over nicely to SQL Server.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Jul 2006
Posts: 87
Let me just clarify here. You are moving from access (no timestamp in your date, based on your message) to SQL Server. So then, why would you encounter a date that included a time in the conversion to SQL Server.

Between exists in SQL Server, so you could still use it in your date comparison calls.

Good Luck,
__________________
Code Carpenter
Code:
If this.Helped
Then Say("Thank You")
Else Goto 'Help'
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Jan 2004
Posts: 145
The problem will occur after I have converted to SQL Server. Once I start using SQL Server and the GETDATE() function I'll be getting times with the dates.

However I just thought of something. I could use VBA to get the current date and pass it as a parameter to a sproc instead of using GETDATE() in the sproc.

It is almost time to go home so I'll save that thought for tomorrow. Now I will be looking forward to work tomorrow
Reply With Quote
  #4 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,554
Quote:
Originally Posted by gwgeller
I'm converting my Access 2003 DB to SQL Server 2005 Express and I use date range comparisons all the time. I learned that the dates also include a time in SQL Server which to me right now seems is going to be a hassle.
Uhm.....the datetime datatype in Access includes time as well....
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
www.LobsterShot.blogspot.com
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Jan 2004
Posts: 145
I guess your right, but I've never had this problem with BETWEEN in Access. I'll have to look at how the data is stored later.
__________________
GG
Code:
On Error Goto Hell

Hell:
    Msgbox("An error occurred, but was sent to Hell. You may continue.")
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Jan 2004
Posts: 145
Again, blindman is correct. The problem is you can't get just the date in SQL Server without doing some work (as far as I've read). I looked back into my Access/VBA code and in one example I use VBA to get the date and pass it to the query, which is probably what I'll end up doing. However Jet SQL has three different functions:

Now() - Returns date and time
Date() - Returns date only
Time() - Returns time only

All three are stored in the same data type. So I guess the question should be is there an equivalent to the Date() function in SQL Server? My guess in no, but I am a mere SQL Server newbie.
__________________
GG
Code:
On Error Goto Hell

Hell:
    Msgbox("An error occurred, but was sent to Hell. You may continue.")
Reply With Quote
  #7 (permalink)  
Old
King of Understatement
 
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,910
Quote:
Originally Posted by gwgeller
So I guess the question should be is there an equivalent to the Date() function in SQL Server?
Code:
SELECT DATEADD(d,DATEDIFF(d,0,GETDATE()),0)
Ta da!
__________________
Testimonial:
Quote:
pootle flump
ur codings are working excelent.
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Jan 2004
Posts: 145
Let me rephrase. Is there a built in function in SQL Server equivalent to Date() in Access? Such as Now() in Access and GETDATE() in SQL Server return the current date/time. Again, regrettably, I will say the answer is no.
__________________
GG
Code:
On Error Goto Hell

Hell:
    Msgbox("An error occurred, but was sent to Hell. You may continue.")
Reply With Quote
  #9 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,554
It is not built-in, but is easily calculated using the code Pootle posted.

If you want, you can wrap that code in a user-defined function.
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
www.LobsterShot.blogspot.com
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Jan 2005
Location: Green Bay
Posts: 201
you can create a user defined function with poodle's code
Reply With Quote
  #11 (permalink)  
Old
Window Washer
 
Join Date: Nov 2002
Location: Jersey
Posts: 10,322
Quote:
Originally Posted by rbackmann
you can create a user defined function with poodle's code

OPr you can just steal some code

http://weblogs.sqlteam.com/jeffs/arc...2/02/2959.aspx

But all date information is stored in SQL Server as datetime...and you want it that way

Easrly on M$ was going to make 2 new datatypes, DATE and TIME

But they gave up
__________________
Brett
8-)

It's a Great Day for America everybody!

dbforums Yak CorralRadio 'Rita
dbForums Member List
I'm Good Once as I ever was

The physical order of data in a database has no meaning.
Reply With Quote
  #12 (permalink)  
Old
King of Understatement
 
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,910
Quote:
Originally Posted by rbackmann
you can create a user defined function with poodle's code
How dare you?

Pootle
__________________
Testimonial:
Quote:
pootle flump
ur codings are working excelent.
Reply With Quote
  #13 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,554
Now, don't get your fur all up.
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
www.LobsterShot.blogspot.com
Reply With Quote
  #14 (permalink)  
Old
Registered User
 
Join Date: Jul 2006
Posts: 87
Quote:
Originally Posted by blindman
Now, don't get your fur all up.
Careful Blindman, you know those small doggies are the ones that bite the most...
__________________
Code Carpenter
Code:
If this.Helped
Then Say("Thank You")
Else Goto 'Help'
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On