|
hi all
in my table i had a colum contain dattime data the formating date
like this "13/12/2007 09:16:41 "
when i make a select statement by where clause and between condition
as
select * from MessageAccepted where AcceptedDateTime between '22/10/2007'and '23/10/2007'
SQL gives me this error
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
is there is a way to convert the datetime datatype and how???
or how can i solve this problem
please i need your help
thanks for all
Kareem Elhosseny
|
|
|
|
|
Convert on DateTime datatype as:
select * from MessageAccepted where AcceptedDateTime between cast('22/10/2007' as DateTime) and Cast('23/10/2007' as DateTime)
I Love T-SQL
|
|
|
|
|
thank's for your effort but i use it before and SQL gives me the same error
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Kareem Elhosseny
|
|
|
|
|
Post your data how they are stored in table, and is datatype of column DateTime or varchar?
I Love T-SQL
modified on Sunday, April 20, 2008 4:07 AM
|
|
|
|
|
Dear Blue_Boy
the datatype in my table is Datetime and the data stored as
this format
23/10/2007 01:48:57 PM
Kareem Elhosseny
|
|
|
|
|
this should works for you...
<br />
select convert(varchar(15),AcceptedDateTime,103) from table1 <br />
where convert(varchar(15),AcceptedDateTime,103) >='20/10/2007' and convert(varchar(15),AcceptedDateTime,103)<='21/10/2007'
I Love T-SQL
modified on Sunday, April 20, 2008 7:41 AM
|
|
|
|
|
thank's alot it works but please could u tell me why we don't use Between
& what's the error when we use it & what is the error Causer?
thank's again for your help,time,and your attention
Kareem Elhosseny
|
|
|
|
|
I guess it should works and with between clause.
You are welcome anytime, my pleasure to help you...
select convert(varchar(15),AcceptedDateTime,103) from table1 <br />
where convert(varchar(15),AcceptedDateTime,103) between '20/10/2007' and '21/10/2007'
I Love T-SQL
|
|
|
|
|
thank's God it workes by Between
select * from MessageAccepted where convert(varchar(10),AcceptedDateTime,103) between '22/10/2007'and '23/10/2007'
but i have a Question
if i made the query as above it return 3 record
and when i change in convert method just only the varchar size to 8 as example its return to me 2 record
can u tell me the reasion please
Kareem Elhosseny
|
|
|
|
|
Why you need to change varchar length? Can't be length 15 or you need length 8?
I Love T-SQL
|
|
|
|
|
no i just try but it work without datatype size because its an optional parametr in convert method
here is my account kareem_elhosseny@hotmail.com
you can add me in your contact list
i hope to be friends to exchange experiance
thank's alot my brother
Kareem Elhosseny
|
|
|
|
|
Yes, you can let it without specify size of varchar, but it is more usable if you specify size.
My add is s_voca@hotmail.com
I Love T-SQL
|
|
|
|
|
I would strongly recommend that you use the following convert statement for your dates when doing comparisons:
convert(char(8),getdate(),112))
as it will return your dates in ISO format which means that comparisons will be valid every time.
Continuous effort - not strength or intelligence - is the key to unlocking our potential.(Winston Churchill)
|
|
|
|
|
We have an application where we do not want to close the cursors after a commit.
I am testing the app with SQL Server 2005 SP2 on Windows XP Professional with ODBC and SQLNativeClient
I have done the following
- The server option "Close cursor on COMMIT" is not enabled
- The database option 'cursor close on commit' is not enabled
- In the connection code of my VC++ 2005 app I wrote
SQLSetConnectAttr(*phDbConnect, SQL_COPT_SS_MARS_ENABLED, (SQLPOINTER)SQL_MARS_ENABLED_YES, SQL_IS_UINTEGER);
SQLSetConnectAttr(*phDbConnect, SQL_COPT_SS_PRESERVE_CURSORS, (SQLPOINTER)SQL_PC_ON , NULL);
SQLSetConnectAttr(*phDbConnect, SQL_AUTOCOMMIT , (SQLPOINTER)SQL_AUTOCOMMIT_OFF, NULL);
In spite of these configurations SQL Server closes the cursors when a commit is done. What should I do?
I also tried the following ExecuteSql bevor executing a query:
ExecuteSql("SET CURSOR_CLOSE_ON_COMMIT OFF");
The reason, why I need open cursors after a commit is very simple:
What I do is
1) getting all rows from table y
SELECT xxx FROM y
2) fetching over each row from y
while (FETCH)
3) updating table z inside the fetch of table y
UPDATE z SET xxxx WHERE....
4) committing the changes made to table z
COMMIT
5) while-end
On 4) SQL Server closes also the cursor for the SELECT statement from 1)  ...
Thank you very much
Mat
|
|
|
|
|
Right off the bat it seems like a rather complex way of updating some rows (cursors are usually a bad way of doing such things, but sometimes needed), but that aside....
is there any need to wrap it in transaction and thus committing after each update of Z?
And if you want it in an transaction - wouldn't it be better to only committing after you're done updating all rows of z?
---------------------------
127.0.0.1 - Sweet 127.0.0.1
|
|
|
|
|
Hello!
Is there any way to check if Referential Integrity is implemented in Stored-Procedures?
Not a single relationship is defined on DB tables!!!
Regards,
Adeel
Do rate the reply, if it helps or even if it doesnot, because it helps the members to know, what solved the issue. Thanks.
|
|
|
|
|
What do you want to check?
Referential integrity is on table level, so I'm unsure what you are trying to accomplish?
---------------------------
127.0.0.1 - Sweet 127.0.0.1
|
|
|
|
|
In normal circumstances, Referencial Integrity (RI) is used on table-level, by defining relationships FKs and PKs....but in special circumstances RI can be implemented at Stored-Procedure (SP) level.
Example:
In proper LDM:
table1.col1 = PK
table2.col1 = PK
table2.col2 = FK
Relationship created and its done!
In this special case:
If table1 and table2 are to linked, in SPs where-ever table1.col1 is inserted or changed or updated, table2.col2 is also changed!
The problem of this approach is, we have to keep all the relationships intact manually and there is no record of which table is related to which table!!!
So the problem is, if we have such system implementation and we dont know what relationships exist (offcourse we didnt implement the system) how can we find it out?
Can we do that finding the update on each table in each SP code?
Regards,
Adeel
Do rate the reply, if it helps or even if it doesnot, because it helps the members to know, what solved the issue. Thanks.
|
|
|
|
|
If this is SQL server, you could use sp_depends on each table to get a list of the stored procedures that reference each table, but still a lot of manual work to determine if the reference is RI or not.
No design documents I guess.
|
|
|
|
|
Thanks for the reply! Very right, no documentation!
Can you please drill-down on manual work? Like what sort of work!  I am willing to do that but I am out of ideas (reasonable ideas i.e.)!
Regards,
Adeel
Do rate the reply, if it helps or even if it doesnot, because it helps the members to know, what solved the issue. Thanks.
|
|
|
|
|
Hi all,
Is there anyway to start Syschronizing process from SQL Query ?
I'm using SQL 2000 Merge replication and Synchronizing time is 11:00 AM (Once A Day).
I'm looking for some ways to use SQL query to do synchronizing.
Can somebody help me , please ?
Thanks
Richard
|
|
|
|
|
SQL Server has system stored procedures which allow you to script anything you need to in order to automate replication: http://msdn2.microsoft.com/en-us/library/ms147302.aspx[^]
It's all in BOL. The above link is a good place to start, it even has a script in it which starts merge replication. But I suggest you read through the docs to determine exactly the settings you want to use.
Mark's blog: developMENTALmadness.blogspot.com
Funniest variable name:
lLongDong - spotted in legacy code, was used to determine how long a beep should be. - Dave Bacher
|
|
|
|
|
Dear Mark,
Thanks a lot. That's exactly what I am looking for.
Thanks again.
Cheers !
Richard
|
|
|
|
|
Anybody who worked on sql server reporting, can you please tell me how to improve performance of any report.
Thanks & Regards
Mishra
|
|
|
|
|
Your question is a little too broad for anyone to be able to answer. The performance of your reports is directly coupled to the performance of the queries used to supply the data for your reports. So the improve the performance of any report you could tune your query.
If you've already tuned your query look into the administration options which allow you to schedule the report to run every so often and cache the output, for example you could have it run every night. As long as you don't need real-time data, which most reports don't absolutely need, then your users will not have to wait so long for the report and it won't bring down your database when you have multiple users running the same report.
Mark's blog: developMENTALmadness.blogspot.com
Funniest variable name:
lLongDong - spotted in legacy code, was used to determine how long a beep should be. - Dave Bacher
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
