In my database table there is a datetime column which can obviously contain
a timestamp
(in the format YYYY-MM-DD HH:MM:SS.NNN).
Now I want to get all those records which have a date of lets say 2005-02-03
regardsless of
their time value.
Ok, I could do this by statement like
SELECT * FROM MYTAB WHERE MYDATE >= '2005-02-03 00:00:00.000' AND MYDATE <=
'2005-02-03 23:59.59.999'
but this is rather inconvenient. I feel that there must be a shorter version
like
SELECT * FROM MYTAB WHERE MYDATE = '2005-02-03 **:**:**.***'
or a built-in fuction like:
SELECT * FROM MYTAB WHERE datepartonly(MYDATE) = '2005-02-03'
How does this work?
GeorgeHi
What you are looking for:
SELECT * FROM MYTAB WHERE CONVERT(CHAR(10), MYDATE, 120) = '2005-02-03'
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"George Dainis" <george.dainis@.bluecorner.com> wrote in message
news:cue1up$k5b$01$1@.news.t-online.com...
> In my database table there is a datetime column which can obviously
contain a timestamp
> (in the format YYYY-MM-DD HH:MM:SS.NNN).
> Now I want to get all those records which have a date of lets say
2005-02-03 regardsless of
> their time value.
> Ok, I could do this by statement like
> SELECT * FROM MYTAB WHERE MYDATE >= '2005-02-03 00:00:00.000' AND MYDATE
<= '2005-02-03 23:59.59.999'
> but this is rather inconvenient. I feel that there must be a shorter
version like
> SELECT * FROM MYTAB WHERE MYDATE = '2005-02-03 **:**:**.***'
> or a built-in fuction like:
> SELECT * FROM MYTAB WHERE datepartonly(MYDATE) = '2005-02-03'
> How does this work?
> George
>|||> SELECT * FROM MYTAB WHERE MYDATE >= '2005-02-03 00:00:00.000'
> AND MYDATE <= '2005-02-03 23:59.59.999'
In fact this won't give the answer you want. '2005-02-03 23:59.59.999'
will be rounded up to 2005-02-04!
Instead try:
SELECT *
FROM MYTAB
WHERE mydate >= '20050203'
AND mydate < '20050204'
Note also that the date format you used is not safe under all regional
connection settings - it may cause a syntax error. The safe formats
are:
'2005-02-03T00:00:00.000'
'2005-02-03T00:00:00'
'20050203'
David Portas
SQL Server MVP
--|||On Wed, 9 Feb 2005 23:13:46 +0100, George Dainis wrote:
>In my database table there is a datetime column which can obviously contain
a timestamp
>(in the format YYYY-MM-DD HH:MM:SS.NNN).
>Now I want to get all those records which have a date of lets say 2005-02-0
3 regardsless of
>their time value.
(snip)
You've already gotten some suggestions that will work, but if there's an
index on the datetime column, you should use this one instead:
SELECT ...
FROM ...
WHERE MyDateCol >= '20050203' -- Standard date format has no dashes!
AND MyDateCol < '20050204'
An index can only be used if the indexed column is on it's own on one side
of a comparison operator - if it's in a function or other expression, the
index can't be used to quickly locate the rows you need.
Your suggested query:
>SELECT * FROM MYTAB WHERE MYDATE >= '2005-02-03 00:00:00.000' AND MYDATE <= '2005-0
2-03 23:59.59.999'
will also return the rows with mydate equal to 2005-02-04 00:00:00.000, as
datetime has a precision of 3/1000th of a second - 23:59:59.999 gets
rounded up to 0:00:00.000, not down to 23:59:59.997.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||George Dainis wrote:
> In my database table there is a datetime column which can obviously contai
n a timestamp
> (in the format YYYY-MM-DD HH:MM:SS.NNN).
> Now I want to get all those records which have a date of lets say 2005-02-
03 regardsless of
> their time value.
> Ok, I could do this by statement like
> SELECT * FROM MYTAB WHERE MYDATE >= '2005-02-03 00:00:00.000' AND MYDATE <
= '2005-02-03 23:59.59.999'
> but this is rather inconvenient. I feel that there must be a shorter versi
on like
> SELECT * FROM MYTAB WHERE MYDATE = '2005-02-03 **:**:**.***'
> or a built-in fuction like:
> SELECT * FROM MYTAB WHERE datepartonly(MYDATE) = '2005-02-03'
> How does this work?
> George
What you are suggesting above is implicit conversion which Oracle
advises against and which is likely to lead to many problems. A
date is not a string and a timestamp is not a string. So surrounding
them with single-quotes is a bad idea in many respects.
What you need to look at is the following functions:
TO_CHAR, TO_DATE, and TO_TIMESTAMP.
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace 'x' with 'u' to respond)|||On Wed, 09 Feb 2005 15:52:39 -0800, DA Morgan wrote:
(snip)
>What you are suggesting above is implicit conversion which Oracle
>advises against and which is likely to lead to many problems. A
>date is not a string and a timestamp is not a string. So surrounding
>them with single-quotes is a bad idea in many respects.
>What you need to look at is the following functions:
>TO_CHAR, TO_DATE, and TO_TIMESTAMP.
Hi DA,
This is a SQL Server newsgroup. TO_CHAR, TO_DATE, and TO_TIMESTAMP are not
valid functions in SQL Server.
In SQL Server, implicit conversion from string to datetime is perfectly
valid, as long as unambiguous date and datetime formats are used:
yyyymmdd
yyyy-mm-ddThh:mm:ss
yyyy-mm-ddThh:mm:ss.ttt
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||George Dainis wrote:
> In my database table there is a datetime column which can obviously contai
n a timestamp
> (in the format YYYY-MM-DD HH:MM:SS.NNN).
> Now I want to get all those records which have a date of lets say 2005-02-
03 regardsless of
> their time value.
> Ok, I could do this by statement like
> SELECT * FROM MYTAB WHERE MYDATE >= '2005-02-03 00:00:00.000' AND MYDATE <
= '2005-02-03 23:59.59.999'
> but this is rather inconvenient. I feel that there must be a shorter versi
on like
> SELECT * FROM MYTAB WHERE MYDATE = '2005-02-03 **:**:**.***'
> or a built-in fuction like:
> SELECT * FROM MYTAB WHERE datepartonly(MYDATE) = '2005-02-03'
> How does this work?
> George
>
Morning George,
select * from mytab
where trunc(mydate) = to_date('2005-02-03', 'yyyy-mm-dd');
However, if mydate is indexed then the trunc() call will prevent the
index being used, so you need to use a statement similar to the one you
have mentioned above except I would use BETWEEN instead of >= and <= but
that's just personal preference.
Cheers,
Norm.
PS. Oracle stores DATE and TIMESTAMP columns in it's own internal
format, not the format you 'think' it is stored in. Always, when doing
date stuff, specify the format mask to TO_DATE because one day your code
may be running on a database which has a different default date format
and it will barf. Been there, got bitten, fixed it.|||On Thu, 10 Feb 2005 07:48:22 +0100, Norman Dunbar wrote:
(snip)
>select * from mytab
>where trunc(mydate) = to_date('2005-02-03', 'yyyy-mm-dd');
Hi Norman,
The result of this query will be:
Server: Msg 195, Level 15, State 10, Line 2
'trunc' is not a recognized function name.
Why are you posting Oracle syntax as replies to questions in a MS SQL
Server newsgroup?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Thu, 10 Feb 2005 06:13:46 +0800, George Dainis wrote
(in article <cue1up$k5b$01$1@.news.t-online.com> ):
> In my database table there is a datetime column which can obviously contain a[/col
or]
> timestamp
> (in the format YYYY-MM-DD HH:MM:SS.NNN).
> Now I want to get all those records which have a date of lets say 2005-02-
03
> regardsless of
> their time value.
> Ok, I could do this by statement like
> SELECT * FROM MYTAB WHERE MYDATE >= '2005-02-03 00:00:00.000' AND MYDATE <=[/color
]
> '2005-02-03 23:59.59.999'
> but this is rather inconvenient. I feel that there must be a shorter versi
on
> like
> SELECT * FROM MYTAB WHERE MYDATE = '2005-02-03 **:**:**.***'
> or a built-in fuction like:
> SELECT * FROM MYTAB WHERE datepartonly(MYDATE) = '2005-02-03'
> How does this work?
> George
>
use the between function.|||George Dainis wrote:
> In my database table there is a datetime column which can obviously contai
n a timestamp
> (in the format YYYY-MM-DD HH:MM:SS.NNN).
> Now I want to get all those records which have a date of lets say 2005-02-
03 regardsless of
> their time value.
> Ok, I could do this by statement like
> SELECT * FROM MYTAB WHERE MYDATE >= '2005-02-03 00:00:00.000' AND MYDATE <
= '2005-02-03 23:59.59.999'
> but this is rather inconvenient. I feel that there must be a shorter versi
on like
> SELECT * FROM MYTAB WHERE MYDATE = '2005-02-03 **:**:**.***'
> or a built-in fuction like:
> SELECT * FROM MYTAB WHERE datepartonly(MYDATE) = '2005-02-03'
> How does this work?
> George
>
TRUNC is your friend, by default it will truncate the date part to the
DD-MON-YY part, dropping the time area, so:
CREATE TABLE mytab (mydate timestamp);
INSERT INTO mytab VALUES (SYSDATE); <-- few entries over a few seconds.
SELECT * FROM mytab WHERE TRUNC(mydate) = '12-FEB-05';
Hope that helps,
Enoch.
