Database

9-Jul-08 20:30

Dear All,
My application stores two date values as Decimal in the SQL server 2005, one for the 'DayTransDate' like (2008/08/09) which is stored in the Data base as a Decimal= 20080709, and the other is for 'DayTransTime' like (12:08:44) which is stored in the Data base as a Decimal= 120844.
Now, I need to read these values and compare them with some date...
I wrote a query to convert with some help from my friend, but the query did not work well because of a syntax error (Incorrect syntax near ',') which I could not figure it out. The following is the query:

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());

First: Is the previous query have the correct functions to convert the Decimal values into date, If yes, where is the syntax error??
Second: If you know any other way to convert, please tell me how to convert from Decimal into date.

With my Best Regards to you...

Kind Regards
OBarahmeh

Paddy Boyd9-Jul-08 20:56

Paddy Boyd

9-Jul-08 20:56

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.

Ashfield9-Jul-08 21:07

9-Jul-08 21:07

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 Smile | :)

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

obarahmeh9-Jul-08 22:35

Re: Convert Decimal into Date Query?? [modified]

9-Jul-08 22:35

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 Sniff | :^)

Kind Regards
OBarahmeh

Ashfield10-Jul-08 1:27

10-Jul-08 1:27

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 Hmmm | :|

Bob
Ashfield Consultants Ltd

modified on Thursday, July 10, 2008 7:36 AM

obarahmeh10-Jul-08 2:02

10-Jul-08 2:02

I am sorry Ashfield... It did not work D'Oh! | :doh:

I used As char(8)...the same error

Please notice that the two columns are designed as the following:
DayTransDate: Decimal(8,0)
DayTransTime: Decimal(6,0)

Any one please join and help.

Thanks again Ashfield

Kind Regards
OBarahmeh

Ashfield10-Jul-08 9:22

Re: Convert Decimal into Date Query?? [modified]

10-Jul-08 9:22

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

leoinfo10-Jul-08 2:44

leoinfo

10-Jul-08 2:44

/* Hi

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

Re: Convert Decimal into Date Query?? [modified]

obarahmeh12-Jul-08 21:46

how to use index? very simple i want some basic information...........

12-Jul-08 21:46

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

Shaik Haneef9-Jul-08 20:27

Shaik Haneef

9-Jul-08 20:27

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.............................................................

Re: how to use index? very simple i want some basic information...........

Paddy Boyd9-Jul-08 20:54

Paddy Boyd

9-Jul-08 20:54

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.

Re: how to use index? very simple i want some basic information...........

Ashfield9-Jul-08 21:13

Transaction (Process ID 156) was deadlocked on lock

9-Jul-08 21:13

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

sujithkumarsl9-Jul-08 18:23

sujithkumarsl

9-Jul-08 18:23

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...

Re: Transaction (Process ID 156) was deadlocked on lock

Ashfield9-Jul-08 21:22

database conection_string

9-Jul-08 21:22

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

nelsonpaixao9-Jul-08 13:56

nelsonpaixao

9-Jul-08 13:56

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 Big Grin | :-D

Re: database conection_string

Paddy Boyd9-Jul-08 20:59

Paddy Boyd

9-Jul-08 20:59

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.

Re: database conection_string

Ashfield9-Jul-08 21:17