Database

9-Aug-08 19:54

hi all
can i make TSQL statement like this

select * from test where date between like '%8/08/1429%' and like '%12/08/1429%'

i mean i want to use where clause with between and like conditions
& when i make the query like above its give me this error

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'like'.

can i make it or not

plz help me

Kareem Elhosseny

PIEBALDconsult9-Aug-08 20:03

PIEBALDconsult

9-Aug-08 20:03

No. And I hope you're not storing dates as strings.

kimo code9-Aug-08 20:17

9-Aug-08 20:17

no i'm trieng to store date as string but plz tell me which truple i'll meet when i use date as string

Kareem Elhosseny

Wendelius9-Aug-08 23:00

9-Aug-08 23:00

Hi,

I suppose that the date-column is defined as datetime so you must first use CONVERT-function to convert the text to datetime. For example:

SELECT * 
FROM  test 
WHERE date BETWEEN CONVERT(datetime, '8/08/1429', 131) AND CONVERT(datetime, '12/08/1429', 131)

Important! the conversion was made using Hijri calendar, based on your other other posts.

Hope this helps,

Mika

kimo code10-Aug-08 20:01

10-Aug-08 20:01

thanks for your help Rose | [Rose]

Kareem Elhosseny

Wendelius11-Aug-08 5:43

11-Aug-08 5:43

You're welcome Smile | :)

Mika

Peter Appleby19-Aug-08 7:08

Peter Appleby

19-Aug-08 7:08

Hi kareem,

Why do y0u need the like? Why not just :
Select * from test where date between '%8/08/1429%' and '%12/08/1429%'

Or possibly try:

Select * from test where date between (like '%8/08/1429%') and (like %12/08/1429%')

Help that helps or I have misunderstood the problem and there is a far deeper reason for your query than is obvious from the data you have given.

kimo code19-Aug-08 19:39

sql server 2000 to sql server 2005

19-Aug-08 19:39

thanks peter for your help really i appeciate your answer and it'll help me

here is m email to contact with me with my pleasure to add me in your contacts kareem_elhosseny@hotmail.com

Kareem Elhosseny

Rameez Raja9-Aug-08 16:55

Rameez Raja

9-Aug-08 16:55

Hi,

iam using sql server 2000 and now wants to move sql server 2005 where i have to start?

whcih version of sql server i can use like (SP2) or other . where to download ?

please give me suggestion because iam new in this.

Any buddy tell me name of sql server quaries book ?

Regards
Rameez

Re: sql server 2000 to sql server 2005

Paul Conrad9-Aug-08 19:27

Paul Conrad

9-Aug-08 19:27

rameez Raja wrote:
iam using sql server 2000 and now wants to move sql server 2005 where i have to start?

Be more specific? Are you trying to take a SQL 2000 database and move it to SQL 2005? Or, are you wanting to learn how to use SQL 2005 after using SQL 2000?

"The clue train passed his station without stopping." - John Simmons / outlaw programmer

"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

SQL Server 2000,Multivalued Attributes

amit20119-Aug-08 5:40

9-Aug-08 5:40

hi,i am a bit new to SQL Server 2000.
Using Enterprise Manager,
In my Database Tables,i need to set Multivalued Attributes to my Coloumns.

like
BusNo Source Destination
73 abc xyz
pqr stu
edf ghi

74 123 456
789 112
a12 b34
-------------------------
i need to set "Source" and "Destination" as "MultiValued"
So,please can any one help me out in it.
thanx

Wendelius9-Aug-08 5:53

9-Aug-08 5:53

Hi,

I think this is purely modeling question. You should store busses in one table and their routes in another. Optionally you can define possible sources and destinations in another table.

For example:

Table Bus
- busno int

Table BusStop
- BusStopKey int
- BusStopText varchar(50)

Table BusRoute
- busno int (reference to bus)
- SourceBusStopKey int (reference to BusStop)
- DestinationBusStopKey int (reference to BusStop)

Hope this helps,

Mika

amit20119-Aug-08 21:34

9-Aug-08 21:34

hey Mika.
dint getwat u wantd to explain,let me elaborate a bit more.
eg.

BusNo
73
36
457
----------
Stop73
73
abc
xyz
wer
----------
Stop36
36
qwe
abc
wer
---------
Stop457
457
abc
ghi
klm

In here when we select Source='abc' and Destinatio='wer'
i want BusNo 73 and 36 to be returned.hope u got what i intend to tell??

kindly explain in a bit detail,m really new to Databas Field.
Thanx..

Wendelius9-Aug-08 21:45

9-Aug-08 21:45

Hi,

I understood that you are designing tables and wondering how they should be modelled but is that data already stored in table? If so, is the problem in creating a select statement?

Mika

amit20119-Aug-08 21:48

9-Aug-08 21:48

ya mika,only in select command.
a very small problem as such,but a big one for me.
pl help...

Wendelius9-Aug-08 21:56

9-Aug-08 21:56

Ok, sorry about the misunderstanding.

I'm still having some trouble in understanding how the data is stored. Are they stored in two tables as I would imagine? Could you post the table names and their column names so we can go through creating a select on them.

Mika

amit20119-Aug-08 22:17

9-Aug-08 22:17

let me tell the whole proble.
i want to design a database.in it i have a Table which has all the BusRouteNo.

Bno(int).

in another Table i have
BusStops.

BNo(int)
Stops(char)

this Table BusStops is for every BusNo. running.

Now i have to have a Select Query such tha when i select two Stops(char) i get all the Bno(int) that pass through those two particular Stops(char).

pl tell me if i can refine my Table and tell me the Select Query for the Same.
Thanx.

Wendelius9-Aug-08 22:45

9-Aug-08 22:45

So your data for BusRouteNo table was (3 rows):

BNo
---
73
36 
457

And for BusStops table it was (9 rows):

BNo Stops
--- -----
73  abc 
73  xyz 
73  wer 
36  qwe 
36  abc 
36  wer
457 abc 
457 ghi 
457 klm

And you wanted to get busses 73 and 36 based on their stops so that if the bus stops on both 'abc' and 'wer' they are selected.

If you don't want any data from busstops I would use correlated exists-clause like following:

SELECT *
FROM bus
WHERE EXISTS (SELECT 1
              FROM   busstops bs1
              WHERE  bs1.BNo   = bus.BNo
              AND    bs1.Stops = 'abc')
AND   EXISTS (SELECT 1
              FROM   busstops bs2
              WHERE  bs2.BNo   = bus.BNo
              AND    bs2.Stops = 'wer')

Would this help you

Mika

amit201110-Aug-08 3:46

10-Aug-08 3:46

thanx Mika,seems this would help..
lukin forward for future help f needed.
thanx for the effort..
Regards...

Amit Smile | :)

Wendelius10-Aug-08 4:21

10-Aug-08 4:21

You're welcome Smile | :)

Mika

amit201112-Aug-08 5:49

12-Aug-08 5:49

hi mika,what u told did work.
one more help from u..

i am usin Visual Studio 2005.
here i am using DropDown List to select the Source and Destination for the Bus.

Now my problem is that in the Select Query u told me,how to pass the values of the Items Selected from the DropDownList.SelectedItems.Value.
the Selected Buses can be Idsplayed using a GridView.
the Select Query is working fine when i pass the values explicitly in the Sql Server 2000-Query Analyser.
i am not able to understand where to write the Sql Select Query with the
"DropDownList.SelectedItems.Value" in my Visual Sudio ".aspx" file.

m having tables exactly the way u told me.

hope u got what i want to say...

thanx...

Wendelius12-Aug-08 7:37

databasebackup & restore error

12-Aug-08 7:37

This can be done by using parameters. In this cas when user selects a bus, you should retrieve the selected value and place it in a parameter. The sql statement could look like following (just using a parameter for bus):

SELECT *
FROM bus
WHERE BusNo = @BusNo

Then you create a parameter named @BusNo (the name can be whatever you like as long as it's the same in parameter object and sql statement) using SqlParameter-class and set a value to the parameter.

For further details see SqlParameter Class[^]

Mika

vishnukamath9-Aug-08 1:36

vishnukamath

9-Aug-08 1:36

hi all

we are developing windows application ,for this we have to take database backup from our application using C#,we wrote code for this ,but it is not working ,it is showing below error message.

error Message:

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open backup device
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is terminating abnormally.

please help me.

Re: databasebackup & restore error

Wendelius9-Aug-08 2:03