|
Why are your dates saved like this? Even if you don't want to use the date time types (for which is see little reason not to) - dates in sql server will cast directly to a decimal value which could then be stored.
And you're missing a closing bracket after your second cast.
|
|
|
|
|
obarahmeh wrote: First: Is the previous query have the correct functions to convert the Decimal values into date, If yes, where is the syntax error??
Obviously not, else there wouldn't be a syntax error 
Your STUFF is wrong.
stuff(stuff(CAST(DayTransTime AS char(8)),5,0,':'),3,0,':')
should do it.
It does beg the question as to why you are storing dates and times as decimal when there is a perfectly good datetime data type.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Ashfield wrote: Your STUFF is wrong.
stuff(stuff(CAST(DayTransTime AS char(8)),5,0,':'),3,0,':')
should do it.
I used your query, but it did not work, and the following is my new query with its error:
SELECT * FROM MyTable
WHERE convert(datetime, cast(DayTransDate AS char(8))
+ ' ' + STUFF(STUFF(CAST(DayTransTime AS char(6)), 5, 0, ':'), 3, 0, ':'))
> DATEADD(minute, -30, getdate());
The error: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Do you have an idea to solve the previous error??
About your question of why do I use Decimal instead of datetime... I am connecting my application to read the data from another source where they are using Decimal data types in all cases instead of Date. So I just have no idea about the reason   
Kind Regards
OBarahmeh
|
|
|
|
|
obarahmeh wrote: I used your query, but it did not work
Thats because you did not make all the changes - you still have 'AS char(6)', it should be 'AS char(8)'
If it works when you copy if fully will you increase my score for my previous answer 
Bob
Ashfield Consultants Ltd
modified on Thursday, July 10, 2008 7:36 AM
|
|
|
|
|
|
I have just been running this
CREATE TABLE #MyTable ( DayTransDate Decimal(8,0) , DayTransTime Decimal(6,0) );
INSERT INTO #MyTable (DayTransDate, DayTransTime) SELECT 20080709, 120844 ;
INSERT INTO #MyTable (DayTransDate, DayTransTime) SELECT 20090709, 120844 ;
INSERT INTO #MyTable (DayTransDate, DayTransTime) SELECT 20080710, 150844 ;
INSERT INTO #MyTable (DayTransDate, DayTransTime) SELECT 20080710, 200844 ;
SELECT *,DATEADD(minute, -30, getdate()) as comparedate FROM #MyTable
WHERE convert(datetime, cast(DayTransDate AS char(8))
+ ' ' + stuff(stuff(CAST(DayTransTime AS char(8)),5,0,':'),3,0,':'))
> DATEADD(minute, -30, getdate());
which gives me
DayTransDate DayTransTime comparedate
20090709 120844 2008-07-10 19:49:16.030
20080710 200844 2008-07-10 19:49:16.030
exactly as expected, so what doesn't work?
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Play with this:
*/
CREATE TABLE #MyTable (
DayTransDate Decimal(8,0) ,
DayTransTime Decimal(6,0)
);
INSERT INTO #MyTable (DayTransDate, DayTransTime) SELECT 20080709, 120844 ;
INSERT INTO #MyTable (DayTransDate, DayTransTime) SELECT 20090709, 120844 ;
SELECT *
, DayTransDate * 1000000 + DayTransTime
, convert(datetime, cast(DayTransDate AS char(8))
+ ' '
+ STUFF(STUFF(CAST(DayTransTime AS char(6)), 5, 0, ':'), 3, 0, ':'))
, CONVERT(NVARCHAR, DATEADD(minute, -30, GETDATE()), 112) +
REPLACE(CONVERT(NVARCHAR, DATEADD(minute, -30, GETDATE()), 108) , ':', '')
, REPLACE(REPLACE(REPLACE(
CONVERT(NVARCHAR(19), DATEADD(minute, -30, GETDATE()), 120)
, '-', '') , ':', '') , ' ', '')
FROM #MyTable
WHERE
DayTransDate * 1000000+DayTransTime
>
REPLACE(REPLACE(REPLACE(
CONVERT(NVARCHAR(19), DATEADD(minute, -30, GETDATE()), 120)
, '-', '') , ':', '') , ' ', ''
)
;
SELECT * FROM #MyTable
WHERE
CONVERT(
NVARCHAR(19),
convert(datetime,
cast(DayTransDate AS char(8))
+ ' '
+ STUFF(STUFF(CAST(DayTransTime AS char(6)), 5, 0, ':'), 3, 0, ':')
)
, 120)
>
CONVERT(NVARCHAR(19), DATEADD(minute, -30, GETDATE()) , 120)
;
SELECT * FROM #MyTable
WHERE
DayTransDate * 1000000 + DayTransTime
>
CONVERT(NVARCHAR,
DATEADD(minute, -30, GETDATE()), 112
)
+
REPLACE(
CONVERT(NVARCHAR, DATEADD(minute, -30, GETDATE()), 108)
, ':', ''
)
;
SELECT * FROM #MyTable
WHERE
DayTransDate * 1000000 + DayTransTime
>
REPLACE(REPLACE(REPLACE(
CONVERT(NVARCHAR(19), DATEADD(minute, -30, GETDATE()), 120)
, '-', '') , ':', '') , ' ', ''
)
;
DROP TABLE #MyTable
/* Any of the following should work the same way for your case: */
SELECT * FROM MyTable
WHERE
CONVERT(
NVARCHAR(19)
, convert(
datetime,
cast(
DayTransDate AS char(8))
+ ' '
+ STUFF(STUFF(CAST(DayTransTime AS char(6)), 5, 0, ':'), 3, 0, ':')) ,
120
)
>
CONVERT(NVARCHAR(19), DATEADD(minute, -30, GETDATE()) , 120)
;
SELECT * FROM MyTable
WHERE DayTransDate * 1000000 + DayTransTime
> CONVERT(
NVARCHAR,
DATEADD(minute, -30, GETDATE()),
112
)
+ REPLACE(CONVERT(NVARCHAR, DATEADD(minute, -30, GETDATE()), 108) , ':', '')
;
SELECT * FROM MyTable
WHERE
DayTransDate * 1000000 + DayTransTime
>
REPLACE(REPLACE(REPLACE(
CONVERT(NVARCHAR(19), DATEADD(minute, -30, GETDATE()), 120)
, '-', '') , ':', '') , ' ', ''
)
;
modified on Thursday, July 10, 2008 9:00 AM
|
|
|
|
|
Deal All,
Thanks for all ur replies.
I found out the error cause, it is not an error in the query. I suddenly found some values in the column DayTransTime (Decimal(6,0)) that its length is 5 (not 6), i.e: it is entered as not in length of 6, so an error appear in this case. I think I need now to convert for both cases of length 6 and length 5.
So I modified the query with adding a new condition to be in the final:
SELECT DayTransDate* 1000000 + DayTransTime,
convert(datetime, cast(DayTransDate AS char(8)) + ' ' +
STUFF(STUFF(CAST(DayTransTime AS char(6)), 5, 0, ':'), 3, 0, ':')),
CONVERT(NVARCHAR, DATEADD(minute, -30, GETDATE()), 112) +
REPLACE(CONVERT(NVARCHAR, DATEADD(minute, -30, GETDATE()), 108) , ':', ''),
REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(19), DATEADD(minute, -30, GETDATE()), 120), '-', '') , ':', '') , ' ', '')
FROM MyTable
WHERE DayTransDate* 1000000 + DayTransTime
< REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(19), DATEADD(minute, -30, GETDATE()), 120), '-', '') , ':', '') , ' ', '')
and len(MESSAGE_TIME)=6;
The question now: How can I retrieve the rows that their DayTransTime length is not 6???
Kind Regards
OBarahmeh
modified on Sunday, July 13, 2008 4:15 AM
|
|
|
|
|
i know therotic about index for retrieve data fast.
how it work ?
for example i have table EMP(eno int ,ename varchar,sal money)
if i create index like below
create index ExIndexOnEmp on EMP(ename)
then what is the use above ? how can i retrieve data fast by using above index, Is SQL SERVER TAKE CARE OF THIS INDEX OR WE NEED TO DO ANY THING MORE AFTER CREATING INDEX
PLEASE GIVE ME A DETAIL ANSWER PLEASE , please avoid giving reference to other website..........
This is haneef.............................................................
|
|
|
|
|
Detailed but concise answer: You don't need to do anything further with your selects - SQL server will decide when best to use the index.
|
|
|
|
|
Paddy has given you a good answer, but I must say your statement
haneef wrote: i know therotic about index for retrieve data fast.
kind of contradicts the next one
how it work ?.
If you don't understand how and why indexing is used then you can cause significant performance problems. I suggest you need to revise the topic further before doing any serious development.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
HI i am using SQL Server 2005. i have two applications to access my database among, first application is running in 8 threads and another is a single threaded. i am frequently getting an error "Transaction (Process ID 66) was deadlocked on lock"....
and makes my server not responding...
if i run my first application with single thread i wont get this error... i got the reason..
sql server has any technique to solve this?
My small attempt...
|
|
|
|
|
Ah, the joys of multi-threading!
Its difficult to totally eliminate deadlocks when multi-threading, but a few things that can help are
- Always run stored procedures for updates - it ensures updates across multiple tables are always performed in the same order
- Use transactions wisely - take locks late and release early
- Consider checking for deadlock error and retrying.
There is no silver bullet for the problem, its just a case of working through the possible solutions.
Hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
|
hi,
i have no problems on connecting to a database, i´m also ok on coding the user´s acess to the database (using store procedures).
But, when i copy a database from my computer and attach to sql server in other computer they can see what is in it!
i dont know how to get a connection_string that ask for the user and password since i enter my sql server by windows validation.
the connection_strings for my databases don´t require passwords!!!
do i have to make other logins in my sql server? and how?
thanks 
|
|
|
|
|
A better practice would be to create a SQL login to your database (see books online for the details) and then use this login/password in your querystring in the application. This way you can limit what that one SQL user can see in the database.
The idea then is that you just manage 1 SQL user for your application instead of many windows user ids.
|
|
|
|
|
The best option in my opinion is to use windows groups to limit user access. It still uses windows authentication so no password verification etc, and you can then grant explicit permissions to the group. You can deny permissions as well as grant them, so deny select, insert, update, delete on your tables and grant exec on the stored procs.
Its a standard way of doing it and easy to maintain by scripts.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Im trying to write a SQL query to populate a XML data feed. I have the query working but they need nodes with prefixes like "media:description" and "live:publocation"
I tried
SELECT RTRIM(Title) AS Title, [Description], Url AS link, DateCreated AS pubDate,
thumbnail AS 'media:thumbnail'
FROM dbo.Videos FOR XML PATH('item'), ROOT('media'))
but that does not work. Anyone done this before?
modified on Wednesday, July 9, 2008 2:06 PM
|
|
|
|
|
I'm not sure I understood what you need... Try this:
WITH XMLNAMESPACES ('uri1' as media, 'uri2' as live)
SELECT RTRIM(Title) AS 'media:title'
, [Description] AS 'media:description'
, Url AS 'live:link'
, DateCreated AS 'media:pubdate'
, thumbnail AS 'media:thumbnail'
FROM dbo.Videos FOR XML PATH('item'), ROOT('root')
|
|
|
|
|
I am somewhat of a novice, so please forgive me if the phrasing of this question isn't clear.
I am working with an SSEE 2005 db and have imported the first of many tables into my Visual C# 2008 EE data sources. I have based a control on this datasource and everything works great.
The table in the db has, along with a primary key, a unique index. I went into the IDE and updated the proper field's Unique property to True and once again it works great. I get a message when I try to duplicate a value in that field.
My question, though, is how do I change the message? Basically I'm looking the for the event code that fires when a DataSet validation rule is broken. Are they all the same (meaning the same event no matter what the error) or is there a unique error event that happens when a Unique constraint is broken? Also, is it handled by the individual tables or from the master dataset?
Any help would be much appreciated.
|
|
|
|
|
You mean something like "violation of index idx_unq_foo cannot insert duplicate key" you want to change that? Its a global you can find by running SELECT * FROM master.dbo.sysmessages WHERE description like '%error%'
The best method to make a customized message is the use a try catch in c# and catch SQLException. customize the message based on the exception type.
|
|
|
|
|
I am creating a new record in the database.
The key is an identity field and the database is set to not allow inserts of assigned values.
I need to return the results of the INSERT to the user.
Is there anything I can do in my SQL that will let me return that key value? Is there something like a @PrimaryKey?
Thanks
|
|
|
|
|
MySQL
Select last_insert_id()<br />
MSSQL
Select SCOPE_IDENTITY()<br />
|
|
|
|
|
Hi,
I need to work with SSRS & SSIS. Thinking that this will be available along with Visual Studio .Net 2008, I have installed the same. But to my surprise, I couldn't find SSRS or SSIS under Reporting Services other than Crystal Reports. How can I install the same? Is it available along with VS 2008 or coming seperately? Please provide any help.
Thanks in advance
meeram395
Success is the good fortune that comes from aspiration, desperation, perspiration and inspiration.
|
|
|
|
|
You might want to read this: http://technet.microsoft.com/en-us/sqlserver/cc314973.aspx[^]
“If we are all in agreement on the decision - then I propose we postpone further discussion of this matter until our next meeting to give ourselves time to develop disagreement and perhaps gain some understanding of what the decision is all about.”-Alfred P. Sloan
|
|
|
|
|
project:Visual Studio 2005 ASP.NET with C#
i want to increment my database values with button.
EXAMPLE:
i have sql database in my project.
column.....gold stone place
madde: 10 10 10
when U clicked button
every column inc+10 or +20.
button is in default.aspx page.
which code can i use in button. i did not do it.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
