February 14, 2008 at 5:51 am
HI
This is probably as really stupid question, so sorry about that. I'm a real newbie to this 🙂
I've been playing about making a test database in SQL server 2000 and in one of my tables I've got a datetime colum. All the data was input without too much trouble (it appears yyyy/mm/dd time etc). when I come to just trying to return dates in a query, I can get it to return info with the operators, but not a = one. I'm probably missing something obvious, but its kind of annoying, I just get column names but no data returned, even when there is a matching date.
February 14, 2008 at 8:24 am
when you are querying the datetime field with an = are you including the time portion or just the date? if you want to pull 2007/02/14 and the data is in there as 2007/02/14 00:00:00.0 then you need to select where = '2007/02/14 00:00:00.0'
you could use something like this
select * from table where [datetime] like '2007/02/14%'
or use a between statement like
select * from table where [datetime] between '2007/02/14' and '2007/02/15' this will give you 2007/02/14 from 00:00:00.0 through 23:59:59.9
I'm still run into issues trying to figure out the differences between Oracle and SQL Server when it comes to datetime fiels :crazy:
February 14, 2008 at 8:35 am
Frank Woodard (2/14/2008)
select * from table where [datetime] between '2007/02/14' and '2007/02/15' this will give you 2007/02/14 from 00:00:00.0 through 23:59:59.9
That will return 2007/02/14 from 00:00:00.000 through 23:59:59.997 and 2007/02/15 00:00:00.000. Between is inclusive on both sides.
What you want is either
... where [datetime] >= '2007/02/14' and [datetime] < '2007/02/15'
or
where [datetime] between '2007/02/14' and '2007/02/14 23:59:59.997' (since SQL's precision on the datetime is 3 milliseconds)
The first one will work, but will implicitly convert the date time to a varchar. The problem with that is that SQL won't be able to use indexes on the datetime column (if any exist).
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 14, 2008 at 8:38 am
Thank you Frank, I hadn't realised that about the time part of it. I was just entering the date.
I've got it sorted now though I think
I was using:
SELECT * FROM orders WHERE order_date = '20051123'
I wasn't using the proper CONVERT statement.
SELECT * FROM orders WHERE CONVERT(char(8), order_date, 112) = '20051123'
That seems to work for me, for the time being until I get better with using datetime.
February 14, 2008 at 8:51 am
Actually, if the date is "2/14/2008 00:00:00.000" and you want = "2/14/08', it will come up. But if the date is stored as "2/14/2008 12:00:00.000" and you want = "2/14/08", it won't. Slight flaw in the example by the prior poster.
All datetime fields in SQL store the time, down to +/- 3 milliseconds. If your equality statement doesn't take that into account, and you just ask for the date, SQL assumes you mean midnight ("00:00:00.000" = midnight).
Smalldatetime stores down to the minute, so time still matters there too.
SQL 2008 will have separate date and time data types, but you're using 2000, so that's not particularly relevant.
Just make sure to include the time, and you'll get the row(s) you want.
If you want all rows where the date is a particular day, there are several ways to do that.
First, you can have "where date >= '2/14/08' and date < '2/15/08'".
Second, you can use "between" in a similar fashion, but you have to watch out that you don't end up getting stuff from midnight of the next day, because "between" includes the end value. "Where date between '2/14/08' and '2/15/08'" will get you everything from the 14th, and anything from midnight of the 15th.
Next, you can have a set of computed columns with the day, month and year in them.
alter table (your table name here)
add DateDay as datepart(day, (your datetime column here)),
DateMonth as datepart(month, (your datetime column here)),
DateYear as datepart(year, (your datetime column here))
For example, if the table is Orders and the datetime column is OrderDate:
alter table dbo.Orders
add DateDay as datepart(day, OrderDate),
DateMonth as datepart(month, OrderDate),
DateYear as datepart(year, OrderDate)
go
create index IDX_Orders_OrderDate on dbo.Orders (DateDay, DateMonth, DateYear)
Then, when you want to select from the table, you can do:
select (columns you want)
from dbo.Orders
where DateYear = 2008
and DateMonth = 2
and DateDay = 14
That will give you all rows where the date is 14 Feb 08, regardless of what time they were.
Any of these will work. I like the computed columns one, because I find it easier to read when I'm debugging code. Also, if you add computed columns for the hour and minutes of the datetime column and index those as well, you can pretty easily do things like select all orders that took place after 5 PM, regardless of the day, and so on.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 14, 2008 at 9:17 am
Thats great thank you, the computed columns idea seems like a nice way to keep it all stored and easily accessable.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply