Prev: Using an SQL function as part of a view
Next: executing an SQL Stored procedure from within a function
From: opokad06 on 2 Jul 2008 12:57
I am trying to search for a table for some information using this query

select * from table A

where lastdate between '07/01/06' AND '07/30/06'

the lastdate column dates are listed as date and time '2007-01-08
15:38:50.877'
but I am interested in only the date.

the sql above does not return anything - but I have information in that date
range.

Help Needed.....

Thanks in advance.

--
KPoku
From: opokad06 on 2 Jul 2008 13:17
I figure it out

select * from A
where lastdate between '2007/04/01'and '2007/04/30'
--
KPoku


"opokad06" wrote:

> I am trying to search for a table for some information using this query
>
> select * from table A
>
> where lastdate between '07/01/06' AND '07/30/06'
>
> the lastdate column dates are listed as date and time '2007-01-08
> 15:38:50.877'
> but I am interested in only the date.
>
> the sql above does not return anything - but I have information in that date
> range.
>
> Help Needed.....
>
> Thanks in advance.
>
> --
> KPoku
From: Aaron Bertrand [SQL Server MVP] on 2 Jul 2008 13:17
> where lastdate between '07/01/06' AND '07/30/06'

What dates are those? July 1st 2006 -> July 30th 2006? June 1st 2007 ->
June 30th 2007? Do you think SQL Server can read your mind any better than
we can? :-)

> the lastdate column dates are listed as date and time '2007-01-08
> 15:38:50.877'
> but I am interested in only the date.

I don't see how this specific value would be met by any reasonable
interpretation of the date strings you entered above. In general though you
should avoid using BETWEEN due to rounding and other assumptions, and you
should avoid using date formats that are ambiguous.

-- assuming July 1st 2006 -> July 30th 2006
WHERE lastdate >= '20060701' AND lastdate < '20060801'

-- assuming June 1st 2007 -> June 30th 2007
WHERE lastdate >= '20070601' AND lastdate < '20070701'


From: Tom Cooper on 2 Jul 2008 13:27
There are only three safe formats to use when you are converting strings to
datetimes and aren't using the CONVERT function and specifying the format.
These three are:
'yyyy-mm-ddThh:mm:ss.ttt' or 'yyyy-mm-ddThh:mm:ss' or 'yyyymmdd'
If you use the 'yyyymmdd' format, SQL will assume the time is midnight
(00:00:00.000)

So the format you want is '20070601' (assuming that what you mean by
'07/06/01' is June 1, 2007).

Also, in general, you don't want to use BETWEEN for datetime values. The
reason is that if you use
between '20070601' and '20070630'
that really means >= midnight on June 1 and <= midnight on June 30. So you
will miss every June 30th row where the time is not set to midnight. So
instead of using between, use >= the starting date and < the first date
after the

So the bottom line is you should write this as

Select *
From A
Where lastdate >= '20070601' And lastdate < '20070701'

Tom

"opokad06" <opokad06(a)discussions.microsoft.com> wrote in message
news:1EC41059-CAD7-4688-A99A-92218BA7A3F5(a)microsoft.com...
>I am trying to search for a table for some information using this query
>
> select * from table A
>
> where lastdate between '07/01/06' AND '07/30/06'
>
> the lastdate column dates are listed as date and time '2007-01-08
> 15:38:50.877'
> but I am interested in only the date.
>
> the sql above does not return anything - but I have information in that
> date
> range.
>
> Help Needed.....
>
> Thanks in advance.
>
> --
> KPoku


From: Russell Fields on 2 Jul 2008 13:28
KPoku,

What does '07/01/06' mean to you? It could be many things. To me it
normally (here in the US) reads as July 1, 2006. Even though the format of
the date time returns as something similar in your query, that is just a
formatting issue.

Your Language settings control how the string is interpreted from your
connection. You can see this by the following, all of which return
different answers:

SET LANGUAGE us_english
SELECT CAST('07/01/06' AS datetime)
SET LANGUAGE british
SELECT CAST('07/01/06' AS datetime)
SET LANGUAGE korean
SELECT CAST('07/01/06' AS datetime)

A language neutral way to format a date only is YYYYMMDD, for example
20070601 for June 1, 2007.
Here is a useful article on datetime:
http://www.karaszi.com/SQLServer/info_datetime.asp

BETWEEN is inclusive, but datetime has time on it. Better than an
apparently inclusive BETWEEN is:

lastdate >= '07/01/06' AND lastdate < '08/01/06' -- Assuming US shorthand
for date

This will handle all time values in the day. If you actually want to strip
the time out of the date values, read the subheading in Tibor's article:
Getting rid of the time portion

RLF




"opokad06" <opokad06(a)discussions.microsoft.com> wrote in message
news:1EC41059-CAD7-4688-A99A-92218BA7A3F5(a)microsoft.com...
>I am trying to search for a table for some information using this query
>
> select * from table A
>
> where lastdate between '07/01/06' AND '07/30/06'
>
> the lastdate column dates are listed as date and time '2007-01-08
> 15:38:50.877'
> but I am interested in only the date.
>
> the sql above does not return anything - but I have information in that
> date
> range.
>
> Help Needed.....
>
> Thanks in advance.
>
> --
> KPoku


 | 
Pages: 1
Prev: Using an SQL function as part of a view
Next: executing an SQL Stored procedure from within a function