Sql date help

morpheus · June 21st, 2004, 01:57 PM

I want to select only fields with todays date, I tried this but it dosen't work.

Select * from table WHERE DateField = '" & Date() & "'

The data is stored in the DB like this.

2003-05-01 00:00:00.000

The table column is a datetime, can I just store the date instead of the time as well?

-----------------------------------------------------------
"Don't follow someone who's not going anywhere" John Mason

Jeff Mason · June 21st, 2004, 07:31 PM

This topic may help...

http://p2p.wrox.com/topic.asp?TOPIC_ID=1346

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

qazi_nomi · June 22nd, 2004, 01:43 AM

You can use getdate() method to get date

Love 4 all

Jeff Mason · June 22nd, 2004, 05:10 AM

Quote:

quote:Originally posted by qazi_nomi
You can use getdate() method to get date

But GetDate() returns both a date and a time. It's also proprietary; use the SQL-92 CURRENT_TIMESTAMP instead (since this is a SQL language forum and not a SQL Server one) - you'll note the name is more descriptive of what it returns - which is a datetime and not just a date...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

morpheus · June 22nd, 2004, 07:44 AM

Thanks for the input guys but the getdate() dosen't work for me because the time part is stored with all zero's. So how do I strip off the time part because it is already insterted that way. Or can I set the time to zero with the getdate() function?

-----------------------------------------------------------
"Don't follow someone who's not going anywhere" John Mason

happygv · June 22nd, 2004, 08:23 AM

Convert(varchar(10),getdate(),121) returns you the date part alone.

When that is assigned to a datetime variable or checked against the datetime type column that is considered as "2004-06-21 00:00:00.000"

This actually strips off the time part as you wanted.

Or you can directly use this

CAST(Convert(varchar(10),getdate(),121) as datetime)

Hope that helps.
Cheers!

_________________________
-Vijay G

Strive for Perfection

morpheus · June 22nd, 2004, 08:41 AM

Okay thank you, that works great, one more question. I can't find much syntax on the getdate() with the books online. Can I use this to select everything from today's date back 5 days?

Thanks

-----------------------------------------------------------
"Don't follow someone who's not going anywhere" John Mason

owain · June 22nd, 2004, 08:49 AM

The integer portion of a datetime represents the day with the fractional part representing the hours, minutes and seconds. Therefore you can use GETDATE() - 5 to retrieve the date 5 days ago.

Regards
Owain Williams

happygv · June 22nd, 2004, 08:50 AM

Use this for back 5 days.
CAST(Convert(varchar(10),getdate()-5,121) as datetime)

May be you can use somthing like this

Code:

Declare @EndDay datetime
Declare @StartDay datetime

Set @StartDay = CAST(Convert(varchar(10),getdate()-5,121) as datetime)
Set @EndDay = CAST(Convert(varchar(10),getdate(),121) as datetime)

Select * from YOURTABLE where DATECOLUMN between @StartDay and @EndDay

On Books online you can search for "DATETIME Functions"
Or on SQL query analyser, type the function that you want to see help on or type getdate(), select that word and press SHIFT + F1, which would show you that function related help, and at the end of the page you can see the other related functions displayed there. For there on you can go learning.

Hope that helps.
Cheers!

_________________________
-Vijay G

Strive for Perfection

morpheus · June 22nd, 2004, 08:58 AM

Thanks a lot guys, I can't believe I forgot how to use the help :(
Thanks for your help

-----------------------------------------------------------
"Don't follow someone who's not going anywhere" John Mason