Need help with select statement

12 Sep 2006 3:35 PM

jdemerath2

I have a SQL database and am having problems with a SELECT statement.
I have an application that date and time stamps entries into the
database. I am trying to create a SQL statement that will return all
records that are like 8/31/2006. I don't want it to look at the time.
I am unable to get the statement to work. I've tried SELECT * FROM
Tickets where DateSubmitted like '%8/31/2006%' and nothing gets
returned.

Please point me in the right direction.

Thanks.

12 Sep 2006 3:41 PM

SQL Menace

SELECT *
FROM Tickets where DateSubmitted > = '20060831'
and DateSubmitted < '20060901'

also take a look here

http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/fc1fe8bb1a65aa9d/c2f488bca10b18f8#c2f488bca10b18f8

Denis the SQL Menace
http://sqlservercode.blogspot.com/

jdemera***@new.rr.com wrote:
Show quoteHide quote

> I have a SQL database and am having problems with a SELECT statement.
> I have an application that date and time stamps entries into the
> database. I am trying to create a SQL statement that will return all
> records that are like 8/31/2006. I don't want it to look at the time.
> I am unable to get the statement to work. I've tried SELECT * FROM
> Tickets where DateSubmitted like '%8/31/2006%' and nothing gets
> returned.
>
> Please point me in the right direction.
>
> Thanks.

12 Sep 2006 3:41 PM

Tracy McKibben

jdemera***@new.rr.com wrote:

> I have a SQL database and am having problems with a SELECT statement.
> I have an application that date and time stamps entries into the
> database. I am trying to create a SQL statement that will return all
> records that are like 8/31/2006. I don't want it to look at the time.
> I am unable to get the statement to work. I've tried SELECT * FROM
> Tickets where DateSubmitted like '%8/31/2006%' and nothing gets
> returned.
>
> Please point me in the right direction.
>
> Thanks.
>

I just answered this same question for somebody else:
http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/fc1fe8bb1a65aa9d

--
Tracy McKibben
MCDBA
http://www.realsqlguy.com

12 Sep 2006 4:10 PM

jdemerath2

The thing is, I'm not trying to get it between dates, I'm trying to
find records for a specific day, time doesn't matter. But I want all
records for 8/31/06 and don't want to have to enter in two dates to do
it.

Tracy McKibben wrote:
Show quoteHide quote

> jdemera***@new.rr.com wrote:
> > I have a SQL database and am having problems with a SELECT statement.
> > I have an application that date and time stamps entries into the
> > database. I am trying to create a SQL statement that will return all
> > records that are like 8/31/2006. I don't want it to look at the time.
> > I am unable to get the statement to work. I've tried SELECT * FROM
> > Tickets where DateSubmitted like '%8/31/2006%' and nothing gets
> > returned.
> >
> > Please point me in the right direction.
> >
> > Thanks.
> >
>
> I just answered this same question for somebody else:
> http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/fc1fe8bb1a65aa9d
>
>
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

12 Sep 2006 4:18 PM

SQL Menace

you can do it with one date like this

declare @d datetime
select @d = '20060831'

SELECT *
FROM Tickets where DateSubmitted > = @d
and DateSubmitted < dateadd(d,1,@d)

or like the way Tracy showed you

Denis the SQL Menace
http://sqlservercode.blogspot.com/

jdemera***@new.rr.com wrote:
Show quoteHide quote

> The thing is, I'm not trying to get it between dates, I'm trying to
> find records for a specific day, time doesn't matter. But I want all
> records for 8/31/06 and don't want to have to enter in two dates to do
> it.
>
>
> Tracy McKibben wrote:
> > jdemera***@new.rr.com wrote:
> > > I have a SQL database and am having problems with a SELECT statement.
> > > I have an application that date and time stamps entries into the
> > > database. I am trying to create a SQL statement that will return all
> > > records that are like 8/31/2006. I don't want it to look at the time.
> > > I am unable to get the statement to work. I've tried SELECT * FROM
> > > Tickets where DateSubmitted like '%8/31/2006%' and nothing gets
> > > returned.
> > >
> > > Please point me in the right direction.
> > >
> > > Thanks.
> > >
> >
> > I just answered this same question for somebody else:
> > http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/fc1fe8bb1a65aa9d
> >
> >
> >
> > --
> > Tracy McKibben
> > MCDBA
> > http://www.realsqlguy.com

12 Sep 2006 5:46 PM

jdemerath2

Thanks. I really appreciate the help.

SQL Menace wrote:
Show quoteHide quote

> you can do it with one date like this
>
> declare @d datetime
> select @d = '20060831'
>
>
> SELECT *
> FROM Tickets where DateSubmitted > = @d
> and DateSubmitted < dateadd(d,1,@d)
>
> or like the way Tracy showed you
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
>
> jdemera***@new.rr.com wrote:
> > The thing is, I'm not trying to get it between dates, I'm trying to
> > find records for a specific day, time doesn't matter. But I want all
> > records for 8/31/06 and don't want to have to enter in two dates to do
> > it.
> >
> >
> > Tracy McKibben wrote:
> > > jdemera***@new.rr.com wrote:
> > > > I have a SQL database and am having problems with a SELECT statement.
> > > > I have an application that date and time stamps entries into the
> > > > database. I am trying to create a SQL statement that will return all
> > > > records that are like 8/31/2006. I don't want it to look at the time.
> > > > I am unable to get the statement to work. I've tried SELECT * FROM
> > > > Tickets where DateSubmitted like '%8/31/2006%' and nothing gets
> > > > returned.
> > > >
> > > > Please point me in the right direction.
> > > >
> > > > Thanks.
> > > >
> > >
> > > I just answered this same question for somebody else:
> > > http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/fc1fe8bb1a65aa9d
> > >
> > >
> > >
> > > --
> > > Tracy McKibben
> > > MCDBA
> > > http://www.realsqlguy.com

12 Sep 2006 4:56 PM

Tracy McKibben

jdemera***@new.rr.com wrote:

> The thing is, I'm not trying to get it between dates, I'm trying to
> find records for a specific day, time doesn't matter. But I want all
> records for 8/31/06 and don't want to have to enter in two dates to do
> it.
>

<sigh>... Copy/pasted from the discussion that I linked to:

"Instead of converting the date, try this:

SELECT     EntiyId, Source, LDAP_DN, EntryType, Message, EventTime
FROM         dbo.ONT_LDAP_LOG
WHERE     EntryType = 'Error'
    AND DATEDIFF(day, EventTime, @Date) = 0

See
http://realsqlguy.com/serendipity/archives/5-No-Time-For-DATETIME-Val... "

--
Tracy McKibben
MCDBA
http://www.realsqlguy.com

12 Sep 2006 5:10 PM

Jim Underwood

as SQL Menace posted...

SELECT *
FROM Tickets where DateSubmitted > = '20060831'
and DateSubmitted < '20060901'

This will select all values where the date is 2006/08/31, regardless of
time.

when you use a date without a time, the system assumes a time of midnight,
or 00:00:00.000

So the example above says select all values where date submitted is equal to
or later than midnight on August 31st 2006, but less than (and not equal to)
midnight on september 1st 2006. Only August 31st meets this criteria.

The datediff function that Tracy posted will also do the trick, but you
MIGHT find the date range faster, depending on the query and your indexes.

<jdemera***@new.rr.com> wrote in message
Show quoteHide quote

news:1158077419.101791.299960@i3g2000cwc.googlegroups.com...
> The thing is, I'm not trying to get it between dates, I'm trying to
> find records for a specific day, time doesn't matter. But I want all
> records for 8/31/06 and don't want to have to enter in two dates to do
> it.
>
>
> Tracy McKibben wrote:
> > jdemera***@new.rr.com wrote:
> > > I have a SQL database and am having problems with a SELECT statement.
> > > I have an application that date and time stamps entries into the
> > > database. I am trying to create a SQL statement that will return all
> > > records that are like 8/31/2006. I don't want it to look at the time.
> > > I am unable to get the statement to work. I've tried SELECT * FROM
> > > Tickets where DateSubmitted like '%8/31/2006%' and nothing gets
> > > returned.
> > >
> > > Please point me in the right direction.
> > >
> > > Thanks.
> > >
> >
> > I just answered this same question for somebody else:
> >

http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/fc1fe8bb1a65aa9d
Show quoteHide quote

> >
> >
> >
> > --
> > Tracy McKibben
> > MCDBA
> > http://www.realsqlguy.com
>