Help with query date code... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help with query date code...

Author

Topic

goingcrazy
Starting Member

6 Posts

Posted - 2003-06-19 : 14:31:29

I have a table I want to query...

The table is called history

the fields I want to query are createdate and userid

I want to show all userid according to today's date.

All entries stored in createdate are in this format:

7/26/2001 7:35:10 AM

that is an example I pulled out of my table.

So,

select userid from history where createdate=GETDATE()

My query returns zero rows for userid.

Am I doing something wrong? I'd imagine it has something to do with the time also being recorded into the date field.

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-06-19 : 14:35:40

You must be a lawyer, asking questions you already know the answers to

.

Yes, in SQL Server generally there is no distinction between date and time, they are always together. getdate() ( or current_timestamp, which i prefer because the name is more descriptive ) returns the current date and time.

use datediff(), as in: datediff( dd, date1, date2 ) = 0, which is the test for both date1 and date2 being the same day.

Jonathan
{0}

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-06-19 : 14:36:33

This will do it:


select userid 
from history 
where CONVERT(CHAR(10), createdate, 101) = CONVERT(CHAR(10), GETDATE(), 101)

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-06-19 : 14:37:27

DATEDIFF, yep that's a better solution.

Tara

nr
SQLTeam MVY

12543 Posts

Posted - 2003-06-19 : 14:43:42

And I would recommend using style 112 internally.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

goingcrazy
Starting Member

6 Posts

Posted - 2003-06-19 : 15:00:27

You guys rock!

Thanks so much, this worked like a champ

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-06-19 : 15:59:55

and if your date field has an index that you'd like to use:

select *
from
history
where
date >= dateadd(d,datediff(d,0,getdate()),0) and
date < dateadd(d,datediff(d,0,getdate()) + 1,0)

(I think i wrote that one right .. may have transposed a "diff" or "add" or something in there.)

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources