|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Need help with select statementI 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. 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. jdemera***@new.rr.com wrote:
> I have a SQL database and am having problems with a SELECT statement. I just answered this same question for somebody else:> 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. > http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/fc1fe8bb1a65aa9d 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 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 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 jdemera***@new.rr.com wrote:
> The thing is, I'm not trying to get it between dates, I'm trying to <sigh>... Copy/pasted from the discussion that I linked 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. > "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... " 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... http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/fc1fe8bb1a65aa9d> 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: > > Show quoteHide quote  |
|||||||||||||||||||||||
Other interesting topics