Datatime order by Problem

Hi everybody,
I have onde table called (TB) with two fields : F1(int) and F2(varchar:10)
In this table the field F2 is used to store dates in the format
dd/mm/yyyy (27/12/2002). Its is record as varchar and undesired.
I need to select all records from a single id from the field F1 and
then order by the result set by date to result set be this way:

F1 F2
------------
01 15/12/1975
01 15/12/1980
01 16/12/1998
01 27/12/2003
------------

To do this Im using the follow syntax :

SELECT * FROM TB
WHERE (F1 = '01')
ORDER BY CAST(F2 AS datetime(103))

But it gives the follow message erro trying to select data :

[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

How can I convert the format used in the select to order by date type in a SQL Server range datetype to get a result set as above?

Thansk for attention.

Leonardo AlmeidaUse convert(datetime, f2, 103) instead of the cast.sql

DataTime for linked Server

DataTime for linked Server

I have a SQL server in Country A (Server A) and another Server in Country B (Server B) Server A has Server B as linked server, I run a stored procedure in Server A and getdate()gives me current date/time for Server A, how can I do getdate() for Server B?

The Getdate() on ServerB might not be the same as ServerA and can cause conflicts for time sensitive data/logic.

Generally for applications spanning countries and timezones it is advised to use UTC time getUTCDate() instead of getdate(). Any localized information to the user can be done by converting the UTC time into local timezone.

|||

Ok. Thanks, how can I convert UTC to local time?

|||

you can use any of the date functions - DateAdd, DateDiff etc..

DataTime for linked Server

DataTime for linked Server

I have a SQL server in Country A (Server A) and another Server in Country B (Server B) Server A has Server B as linked server, I run a stored procedure in Server A and getdate()gives me current date/time for Server A, how can I do getdate() for Server B?

Hi Jim.H.

You should be able to do this with openquery - try this:

SELECT

*

FROM

OPENQUERY(yourLinkedServer,'SELECT getdate()')

|||

If you're on sql2k5, you can also do this.

Code Snippet

exec ('select getdate() as [date]') AT linked_server_B

datatime error

I have some problem with datatime.

SELECT * FROM stat WHERE data > '2005-05-24 14:07:28' ORDER BY id Asc

I got the error:

Microsoft OLE DB Provider for SQL Server error '80040e07'

The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.

Why? Format of date is the same in database?

Regards,
Hi
just try it this way:

SELECT *
FROM stat
WHERE
data > convert(varchar(10),'2005-05-24 14:07:28',101) ORDER BY id Asc

best Regards,
Chandra
http://groups.msn.com/SQLResource/
http://chanduas.blogspot.com/
------------

*** Sent via Developersdex http://www.developersdex.com ***|||On Tue, 31 May 2005 14:31:19 +0200, Zibi wrote:

>I have some problem with datatime.
> SELECT * FROM stat WHERE data > '2005-05-24 14:07:28' ORDER BY id Asc
>I got the error:
>Microsoft OLE DB Provider for SQL Server error '80040e07'
>The conversion of a char data type to a datetime data type resulted in an
>out-of-range datetime value.
>Why? Format of date is the same in database?

Hi Zibi,

Assuming that "data" is declared as a [small]datetime column, then it
has no format in the database. The internal representation of datetime
is, in fact, a set of two integers (but the internal representation is
in fact not relevant).

For your query, the date/time constant is first converted to the
internal representation of either datetime or smalldatetime (to match
that of the "data" column), then the comparison is made. Obviously, the
first part (the conversion of '2005-05-24 14:07:28' to [small]datetime)
is the cause of your error. Obviously, some locale settings on your SQL
Server make it think that you use a yyyy-dd-mm hh:mm:ss format.

To prevent this kind of errors, use only the guaranteed safe formats for
date and date/time constants:

* yyyymmdd (date only - note: no dashes, slashes, dots or whatever)
* yyyy-mm-ddThh:mm:ss (date plus time - note: dashes between the
components of the date; colons between the components of the time and an
uppercase T to seperate date from time)
* yyyy-mm-ddThh:mm:ss.ttt (as above, but with milliseconds, seperated
from the time by a dot).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Tue, 31 May 2005 15:01:55 GMT, Chandra wrote:

>Hi
>just try it this way:
>SELECT *
>FROM stat
>WHERE
>data > convert(varchar(10),'2005-05-24 14:07:28',101) ORDER BY id Asc
>best Regards,
>Chandra

Hi Chandra,

This won't work, for two reasons.

First: if data is a datetime column (which I hope it is - otherwises,
the OP has a bag of other problems), then converting the constant to
varchar won't do any good. It is just an extra conversion to slow down
the process; in the end, it'll be converted to datetime in order to make
the comparison.

Second: the expression
convert(varchar(10),'2005-05-24 14:07:28',101)
returns the string constant '2005-05-24'. Since you're converting a
varchar constant to varchar, the stylle parameter is not used; you
simply get the first 10 characters. As a result, the time portion in
stripped and the query will return too many rows.

Third: since the format yyyy-mm-dd is not guaranteed safe either, this
version might result in the same error as well.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
> For your query, the date/time constant is first converted to the
> internal representation of either datetime or smalldatetime (to match
> that of the "data" column), then the comparison is made. Obviously, the
> first part (the conversion of '2005-05-24 14:07:28' to [small]datetime)
> is the cause of your error. Obviously, some locale settings on your SQL
> Server make it think that you use a yyyy-dd-mm hh:mm:ss format.

Actually, this happens if you have a SET DATEFORMAT dmy somewhere,
explicitly or implicitly. While ymd is possible to set, it's rarely
used in practice. dmy, on the other hand is common with many
language settings.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Uzytkownik "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> napisal w
wiadomosci news:rhfp91pppbovern3umptsgr3kimkai7ie1@.4ax.com...
> On Tue, 31 May 2005 14:31:19 +0200, Zibi wrote:

> * yyyymmdd (date only - note: no dashes, slashes, dots or whatever)
> * yyyy-mm-ddThh:mm:ss (date plus time - note: dashes between the
> components of the date; colons between the components of the time and an
> uppercase T to seperate date from time)
> * yyyy-mm-ddThh:mm:ss.ttt (as above, but with milliseconds, seperated
> from the time by a dot).
Hi,

Thanks all.
I use exctly - SELECT COUNT(id) AS [stat_ile] FROM stat WHERE (data >
CONVERT(DATETIME, '2005-05-24 14:07:28',101)) and it works. I don't need to
use hours. I found that format in database is yyyy-mm-ddThh:mm:ss.ttt when I
use query analyzer but when I use simple SQL manager I see only format
yyyy-mm-ddThh:mm:ss. That's a litlle strange and mistaken.

Regard,|||Uzytkownik "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> napisal w
wiadomosci news:rhfp91pppbovern3umptsgr3kimkai7ie1@.4ax.com...
> On Tue, 31 May 2005 14:31:19 +0200, Zibi wrote:

> * yyyymmdd (date only - note: no dashes, slashes, dots or whatever)
> * yyyy-mm-ddThh:mm:ss (date plus time - note: dashes between the
> components of the date; colons between the components of the time and an
> uppercase T to seperate date from time)
> * yyyy-mm-ddThh:mm:ss.ttt (as above, but with milliseconds, seperated
> from the time by a dot).
Hi,

Thanks all.
I use exctly - SELECT * FROM stat WHERE (data > CONVERT(DATETIME,
'2005-05-24 14:07:28', 102))ORDER BY id Asc and it works. I don't need to
use hours. I found that format in database is yyyy-mm-ddThh:mm:ss.ttt when I
use query analyzer but when I use simple SQL manager I see only format
yyyy-mm-ddThh:mm:ss. That's a litlle strange and mistaken.

Regards,

Zibi|||Zibi (zibi@.nospam.com) writes:
> I use exctly - SELECT * FROM stat WHERE (data > CONVERT(DATETIME,
> '2005-05-24 14:07:28', 102))ORDER BY id Asc and it works. I don't need
> to use hours. I found that format in database is yyyy-mm-ddThh:mm:ss.ttt
> when I use query analyzer but when I use simple SQL manager I see only
> format yyyy-mm-ddThh:mm:ss. That's a litlle strange and mistaken.

Format in the database is binary. Then it is up to the tool to perform
a textual presentation.

This link may be helpful you:
http://www.karaszi.com/SQLServer/info_datetime.asp.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Uzytkownik "Erland Sommarskog" <esquel@.sommarskog.se> napisal w wiadomosci
> Format in the database is binary. Then it is up to the tool to perform
> a textual presentation.
> This link may be helpful you:
> http://www.karaszi.com/SQLServer/info_datetime.asp.
>
Thnks - good site!

DataTime as parameters: Help Needed.

Hello all,
I need to generate a report based on dates(from and to). Is there
anyway I can include a dropdown of datetime in the parameters of the RS
interface.
for example like from mm/dd/yyyy to mm/dd/yyyy.
I have RS 2003 EE with SP1 and have no plans of installing SP2.
Any help will be appreciated.
Thanks a lot
RaviRavi,
You need to create a list of dates as a DS, and then add two params to the
report (well, just add them to the DS for the report itself) and configure
the Params to be query selections from the Dates DS.
Using Adventurewroks DB, the following suffices for selecting particular
records of SalesOrderID, ModifiedDate columns from the SalesOrderDetail
table, by date.
DataSets:
DSDates:
SELECT ModifiedDate, CONVERT(char(20), ModifiedDate, 101) AS dateselect
FROM dbo.SalesOrderDetail
GROUP BY ModifiedDate
ORDER BY ModifiedDate
DS1:
SELECT SalesOrderID, ModifiedDate
FROM dbo.SalesOrderDetail
WHERE (ModifiedDate > @.STARTDATE AND ModifiedDate < @.ENDDATE)
Form:
Table with DS1 as it's source.
Remember to edit the params in Report-Report Parameters, for both @.STARTDATE
AND @.ENDDATE, to be;
From Query; Dataset:DSDates;ValueField:ModifiedDate;LabelField:dateselect.
If you leave out the dateselect column from the first DS then the dropdown
will default to 00:00:00 time, and looks awful.
Hope this helps,
Tony
"Ravi R" wrote:
> Hello all,
>
> I need to generate a report based on dates(from and to). Is there
> anyway I can include a dropdown of datetime in the parameters of the RS
> interface.
> for example like from mm/dd/yyyy to mm/dd/yyyy.
> I have RS 2003 EE with SP1 and have no plans of installing SP2.
> Any help will be appreciated.
> Thanks a lot
> Ravi
>|||Ravi,
In addiditon to my last reply, if you want to make 'intelligent' parameters,
then your end date should be greater than your start date.
To achieve this, complete tasks as per my previous reply and add the
following;
Create a further DS called DSDates2.
SELECT ModifiedDate, CONVERT(char(20), ModifiedDate, 101) AS dateselect
FROM dbo.SalesOrderDetail
GROUP BY ModifiedDate
HAVING (ModifiedDate > @.STARTDATE)
ORDER BY ModifiedDate
and change the source for the @.ENDDATE parameter to point to DSDates2.
You will then find the End Date drop down is disabled until the Start Date
is selected.
If you wish, you can set a default for the Start Date as being the first
date found in the table (Use Top 1 selected from DSDates) for extra
useability.
Hope this assists further,
Tony
"Ravi R" wrote:
> Hello all,
>
> I need to generate a report based on dates(from and to). Is there
> anyway I can include a dropdown of datetime in the parameters of the RS
> interface.
> for example like from mm/dd/yyyy to mm/dd/yyyy.
> I have RS 2003 EE with SP1 and have no plans of installing SP2.
> Any help will be appreciated.
> Thanks a lot
> Ravi
>|||thanks logicalman,
will try that first thing on monday.
Ravi