Re: datetime problem - Database Discussion Boards

Re: Create Table statement

soni uma31-Oct-07 2:04

soni uma

31-Oct-07 2:04

Prithaa This Work fine Try it Again;

Laugh | :laugh:

Re: Create Table statement

neeraj_indianic31-Oct-07 2:09

neeraj_indianic

31-Oct-07 2:09

Actually you are not giving the size of varchar so by default it will take varchar(1) and you will not be able to insert the record which is greater than 1 character. You have to do like this:-

Create Table Purchase (Name varchar(50),Amount numeric(10,2))

datetime problem

niki_nilu31-Oct-07 0:38

niki_nilu

31-Oct-07 0:38

hello
i am gayatri patil
i have two table tAnalyst and tUser
tAnalyst:-

UserId varchar(20) notnull
CreateDate datetime(8) notnull
UpdateDate datetime(8) allow Null

tUser table:-
UserId varchar(20) notnull
firstname varchar(20) notnull
lastname varchar(20) notnull

i want sql query like
join the two tables and search data date wise.
my query is--------
select tU.UserID,tU.FirstName,tU.LastName,
tU.LastName+''+ tU.FirstName'UserId',
tA.UserID,tA.CreateDate,
tA.UpdateDate
from tAnalyst tA,
tUser tU
where tU.UserID=tA.UserID
and
tA.CreateDate = '7/25/2007'
in this query i haven't error but data is not display.
so pls guide me writeway.
regards
gayatri

Gayatri

Re: datetime problem

soni uma31-Oct-07 0:44

soni uma

31-Oct-07 0:44

1 . Gaytri it may possible that racords are not matche
2. Or Pass the Date as 07/25/2007 not 7/25/2007
Old tA.CreateDate = '7/25/2007' i think it is problem
New tA.CreateDate = '07/25/2007'

Re: datetime problem

niki_nilu31-Oct-07 1:11

niki_nilu

31-Oct-07 1:11

sir i am write query like you told me.
but its not working
gayatri

Gayatri

Re: datetime problem

soni uma31-Oct-07 1:15

soni uma

31-Oct-07 1:15

Ok then doing This way

convert(varchar,tA.CreateDate,101)='07/25/2007'

Re: datetime problem

r a j u u31-Oct-07 0:51

r a j u u

31-Oct-07 0:51

in tA.CreateDate column you can make '07/25/2007'..i think this'7/25/2007'time format wrong...plz try this

Re: datetime problem

Krish - KP31-Oct-07 1:06

Krish - KP

31-Oct-07 1:06

Data stored in CreatedDate colum is DateTime i.e. the column has both date and Time.

but while retrieving you are using only Date. so Automatically system takes time as "00:00:00"

change you condition to ...
ta.CreateDate BETWEEN '25-Jul-2007' AND '26-Jul-2007'

Regards
KP

Re: datetime problem

neeraj_indianic31-Oct-07 1:10

neeraj_indianic

31-Oct-07 1:10

This is because of format of date is different in your table column field(i.e. CreateDate and you are comparing this date with another format which is mm/dd/yyyy. If you want to do it then you have to convert your format and you will sure get result. Now use this query to get the result.

select tU.UserID,tU.FirstName,tU.LastName,<br />
tU.LastName+''+ tU.FirstName'UserId',<br />
tA.UserID,tA.CreateDate,<br />
tA.UpdateDate<br />
from tAnalyst tA,<br />
tUser tU<br />
where tU.UserID=tA.UserID<br />
and<br />
convert(varchar,tA.CreateDate,101)='07/25/2007'

Re: datetime problem

niki_nilu31-Oct-07 1:13

niki_nilu

31-Oct-07 1:13

Thanku very much sir
your query is running.
thanku
gayatri

Gayatri

Re: datetime problem

joemonvarghese1-Nov-07 23:29

joemonvarghese

1-Nov-07 23:29

hi
whenever you are dealing with dates..convert the dates to a common format...
you can use convert function for this.

regards

Joe

Numeric Column

banker_kiran30-Oct-07 23:38

banker_kiran

30-Oct-07 23:38

Hi,
I have one column with numeric data type.
It is auto number.
i have given size numeric(9,0)
now i want to pad 0.

eg. if it is 1 in auto number then i want to display
000000001
if 2 then 000000002
if 10 then 000000010

kiran banker

Re: Numeric Column

Krish - KP31-Oct-07 0:35

Krish - KP

31-Oct-07 0:35

check is this what you are looking for

<br />
SELECT <br />
CASE WHEN Len('00000000' + LTrim(str(myid))) > 9 THEN <br />
	SubString(<br />
		'00000000' + LTrim(Str(myid)), <br />
		Len('00000000' + LTrim(Str(myid))) - 9, <br />
		Len('00000000' + LTrim(Str(myid)))<br />
	) END<br />
FROM tableA<br />

Regards
KP

Re: Numeric Column [modified]

GuyThiebaut31-Oct-07 3:23

GuyThiebaut

31-Oct-07 3:23

Hi Kiran,

The code below will insert '15' into ten zeroes and keep it right justified.
You will just need to jiggle things a bit substituting your number for '15'

<br />
select stuff('0000000000',11-len('15'),len('15'),'15')<br />

Or even better

<br />
select stuff(replicate('0',10),11-len(cast(15 as varchar(100))),len(cast(15 as varchar(100))),cast(15 as varchar(100)))<br />

I hope this is of help.

Guy

You always pass failure on the way to success.

Re: Numeric Column

ianhoc31-Oct-07 7:04

ianhoc

31-Oct-07 7:04

I think this is easier:

SELECT RIGHT('00000000' + Ltrim(str(MyKey)), 8)
From Table

Ian

Need an Article about Replication.

hdv21230-Oct-07 23:22

hdv212

30-Oct-07 23:22

hi i'm looking for an article that explain replication good, can u give me a link for that article ?
thanks

.Net

amsathu30-Oct-07 23:20

amsathu

30-Oct-07 23:20

please give me asp.net sample program with ado.net

send through mail

amsathu

Re: .Net

il_masacratore30-Oct-07 23:36

il_masacratore

30-Oct-07 23:36

... Why don't you search google??

Visit my blog at http://dotnetforeveryone.blogspot.com

Re: .Net

Giorgi Dalakishvili30-Oct-07 23:40

Giorgi Dalakishvili

30-Oct-07 23:40

...Or even CodeProject??

#region signature
my articles
#endregion

Re: .Net

Paul Conrad31-Oct-07 14:12

Paul Conrad

31-Oct-07 14:12

How about doing a search here on CP? There are plenty of examples Big Grin | :-D

"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus

Getting the columns

M. J. Jaya Chitra30-Oct-07 22:31

M. J. Jaya Chitra

30-Oct-07 22:31

Can anyone tell me how to retrieve the column names in a table?

Best Regards,
M. J. Jaya Chitra

Re: Getting the columns

Giorgi Dalakishvili30-Oct-07 22:47

Giorgi Dalakishvili

30-Oct-07 22:47

If you are using sql server then you can use the following query:
SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'name_of_the_table'

#region signature
my articles
#endregion

Re: Getting the columns

r a j u u30-Oct-07 23:04

r a j u u

30-Oct-07 23:04

ist working? i tried this query but its saying Invalid columnname...

Rajendran

Re: Getting the columns

Giorgi Dalakishvili30-Oct-07 23:09

Giorgi Dalakishvili

30-Oct-07 23:09

Yes, it is. Just make sure you are performing the query against the database that contains your table