Database

Re: deference between two columns in two Sequenced rows

6-Jan-09 0:20

Ahmed El-Badry wrote:
i want the sql queries to create stoprte value

Could you be more specific what you're trying to achieve? You want to calculate stoprate value with what kind of formula?

The need to optimize rises from a bad design.My articles[^]

Ahmed El-Badry6-Jan-09 0:27

Ahmed El-Badry

6-Jan-09 0:27

i want calculate stoprate value

table name trips

starttime endtime triptime Stoprate
14/08/2008 10:39:00 PM 14/08/2008 10:56:00 PM 16 3
14/08/2008 10:59:00 PM 14/08/2008 11:1:00 PM 2

stop value = 14/08/2008 10:56:00 PM - 14/08/2008 10:59:00 PM = 3
i want the sql queries to calculate stoprte value

Ahmed El-Badry

Re: deference between two columns in two Sequenced rows

Wendelius6-Jan-09 0:40

Re: deference between two columns in two Sequenced rows

6-Jan-09 0:40

So the stoprate is endtime from row 1 - starttime from row 2 in minutes? If that's correct, you could use something like:

select datediff(minute,
                trips.endtime, 
                (select min(t2.starttime)
                 from   trips t2
                 where  t2.starttime > trips.endtime))
from trips...

The need to optimize rises from a bad design.My articles[^]

Ahmed El-Badry6-Jan-09 0:45

Ahmed El-Badry

6-Jan-09 0:45

thnx i will tray it Smile | :)

Ahmed El-Badry

Re: deference between two columns in two Sequenced rows

Wendelius6-Jan-09 3:11

6-Jan-09 3:11

You're welcome. Smile | :)

The need to optimize rises from a bad design.My articles[^]

Unicode in SQL query

Varghese Paul M5-Jan-09 23:09

Varghese Paul M

5-Jan-09 23:09

Hi friends,

I am working with SQL Server 2005 database with Collation selected as Chinese_PRC_CI_AS

I have a table DMASTER in the database with a field called DNAME of type VARCHAR(254) which contains some Chinese characters.

When i run a query like
select * from dmaster where DNAME = N'翻译文字或网页翻译文字或网页';
from the SQL Server 2005 client, it works fine.

But if I try the same query programmatically using API "SQLExecDirect", it returns no records.

May I know the problem and how to solve it.

Thank you

cheers
Varghese Paul

Re: Unicode in SQL query

Wendelius5-Jan-09 23:32

5-Jan-09 23:32

Have you tried using SQLExecDirectW which is unicode version of the function. See: Unicode Function Arguments[^]

The need to optimize rises from a bad design.My articles[^]

Re: Unicode in SQL query

Varghese Paul M6-Jan-09 0:46

Varghese Paul M

6-Jan-09 0:46

Thank you. It worked fine.

cheers
Varghese Paul

Re: Unicode in SQL query

Wendelius6-Jan-09 0:48

How can i do this query?? [modified]

6-Jan-09 0:48

You're welcome.

The need to optimize rises from a bad design.My articles[^]

foryou5-Jan-09 22:39

5-Jan-09 22:39

HI!!
I have a table (sqlserver2005)

ID-------date1--------date2
1--------1/12/2008-----30/5/2009
2--------1/12/2009-----30/6/2010
...
I typed a date in a textbox.
I need to select ID as that date will be between date1 and date2.
How can i do that thanks.
exemple whene date=30/2/2009 id is 1.
Thanks!

modified on Tuesday, January 6, 2009 4:57 AM

Wendelius5-Jan-09 23:03

5-Jan-09 23:03

You can use between operator like:

SELECT ...
FROM ...
WHERE DateFromTextBox BETWEEN date1 AND date2

The need to optimize rises from a bad design.My articles[^]

foryou7-Jan-09 1:10

Re: How can i do this query?? [modified]

7-Jan-09 1:10

HI!!
I did this :

String rqtr2 = "select ID as [p1] from Table where ('" + TextBox2.Text + "' between date1 and date2)";

and

TextBox2.Text = Convert.ToString(DateTime.Now) ;

but it is not right .
Help me please!
thanks

Wendelius7-Jan-09 1:21

7-Jan-09 1:21

foryou wrote:
but it is not right

What do you mean by that? Could you post an example row and what you entered in the textbox. That would clarify your problem. The forst guess would be that when you convert the date to a string, it isn't in a format that the db understands.

Little off-topic: it's a bad habit to use literals in the sql statement. Use parameters instead.

The need to optimize rises from a bad design.My articles[^]

modified on Wednesday, January 7, 2009 7:27 AM

foryou7-Jan-09 1:41

7-Jan-09 1:41

Hi!
Merci Mika Wendelius
sorry I dont speak English well.
what I am in the textbox is the system date (detetime)
example :
07/01/2009 11:59:16
Little off-topic: it's a bad habit to use literals in the sql statement. Use parameters instead.

I do not know how to use parameters in this case.
Thanks!!

Wendelius7-Jan-09 2:01

7-Jan-09 2:01

foryou wrote:
sorry I dont speak English well

No problem at all

foryou wrote:
what I am in the textbox is the system date (detetime)
example :
07/01/2009 11:59:16

ok, you may encounter a conversion problem. If you're using literals (which I do not encourage at all) you would have a query like

select ...
from   ...
where CONVERT(datetime, '07/01/2009 11:59:16', 101) between date1 and date2

You can test this for example like this:

select * 
from  sysobjects 
where CONVERT(datetime, '01/07/2009 11:59:16',101) -- now
between GETDATE()-1  -- yesterday
and     GETDATE()+1  -- tomorrow

Notice that the format is mm/dd/yyyy. if you want to use dd/mm/yyyy, you should use British/French format:

CONVERT(datetime, '07/01/2009 11:59:16', 103)

However you won't have these problems at all if you use parameters. Then your query would look like

select ...
from   ...
where @SomeDateParameter between date1 and date2

There are good examples in BOL: SqlParameter[^].

foryou wrote:
Merci Mika Wendelius

Ne rien. (I hope it was correct Smile | :)

)

The need to optimize rises from a bad design.My articles[^]

foryou8-Jan-09 22:19

8-Jan-09 22:19

Hi!!
Thank you very much Mika Wendelius! it works.

Mika Wendelius wrote
Ne rien. (I hope it was correct )
they say "De rien". Smile | :)

Wendelius8-Jan-09 23:26

How to get LSN detail of a Log backup in sql server

8-Jan-09 23:26

foryou wrote:
Thank you very much

You're welcome Smile | :)

foryou wrote:
they say "De rien".

I had a feeling it was wrong. Thanks for correcting! It's been over 20 years I studied french and never used it after that so I guess I have forgotten about everything Smile | :)

The need to optimize rises from a bad design.My articles[^]

PadmaPriya.S5-Jan-09 22:39

Re: How to get LSN detail of a Log backup in sql server

5-Jan-09 22:39

Hi all

How to get LSN detail of a Log backup in sql server?

Thanks.,
Willington

D.Willington

Wendelius5-Jan-09 22:57

Re: How to get LSN detail of a Log backup in sql server

5-Jan-09 22:57

You can use the Management Studio using restore window or using T-SQL: RESTORE HEADERONLY[^]

The need to optimize rises from a bad design.My articles[^]

PadmaPriya.S5-Jan-09 23:05

Re: How to get LSN detail of a Log backup in sql server

5-Jan-09 23:05

we dont want to restore the log backup..,

Please tell me, how to get LSN withot restoring..

Thanks.,
Willington

D.Willington

Wendelius5-Jan-09 23:15

Re: How to get LSN detail of a Log backup in sql server

5-Jan-09 23:15

It's been years since I last used that but I remember the command doesn't restore the log but only reads and returns the header information (same as the restore window does when you point out a backup file without actually restoring).

Of course for security reasons, you can take the file into another machine and test the command there.

The need to optimize rises from a bad design.My articles[^]

PadmaPriya.S5-Jan-09 23:20

Re: How to get LSN detail of a Log backup in sql server

5-Jan-09 23:20

Thanks...

D.Willington

Wendelius5-Jan-09 23:33

Re: How to get LSN detail of a Log backup in sql server

5-Jan-09 23:33

You're welcome.

The need to optimize rises from a bad design.My articles[^]

PadmaPriya.S5-Jan-09 23:41

Re: How to get LSN detail of a Log backup in sql server

5-Jan-09 23:41

Another Question.., Can u tell me what is

1. database-->Security-->SCHEMA and USER
2. server-->Security-->Logins, Credentials

Thanks.,
Willington

D.Willington

Wendelius5-Jan-09 23:53