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.

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,

If this.Helped
Then Say("Thank You")
Else Goto 'Help'

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

Uhm.....the datetime datatype in Access includes time as well....

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.

On Error Goto Hell

Hell:
    Msgbox("An error occurred, but was sent to Hell. You may continue.")

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.

On Error Goto Hell

Hell:
    Msgbox("An error occurred, but was sent to Hell. You may continue.")

Ta da!

pootle flump ur codings are working excelent.

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.

On Error Goto Hell

Hell:
    Msgbox("An error occurred, but was sent to Hell. You may continue.")

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.

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

How dare you?

Pootle

pootle flump ur codings are working excelent.

Now, don't get your fur all up.

Careful Blindman, you know those small doggies are the ones that bite the most...

If this.Helped
Then Say("Thank You")
Else Goto 'Help'