|
The CONVERT function has a number of options for different date formats. Read the documentation and choose the most appropriate format for your requirements.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
hi paul...
thank u for ur response .but i found answer for every formate....
but i need to convert dates like o4 th july 2005 or
3rd august 1997
31st december 2000
22nd july 1997....i.e th,nd,rd,....
to default formate..... 04/07/2005,
03/08/1997
helpe with a function.....
i am trying this ,but it convert without th,rd,nd,...
if not (@Date IS NULL)
BEGIN
SET @Date = LEFT(@strPhotoDate,patindex('%|%',@strPhotoDate )-1)
SET @Date = replace(@strPhotoDate,'th','')
SET @Date = replace(@strPhotoDate,'nd','')
SET @Date = replace(@strPhotoDate,'rd','')
SET @Date = replace(@strPhotoDate,'st','')
SET @Date = replace(@strPhotoDate,',','')
set @GetPhotoDate = CONVERT(datetime, @Date)
--cast(@GetPhotoDate as DATETIME)
END
END
like that...
thanks in advance
Thanks
Subbu.
|
|
|
|
|
hi paul...
thank u for ur response .but i found answer for every formate....
but i need to convert dates like o4 th july 2005 or
3rd august 1997
31st december 2000
22nd july 1997....i.e th,nd,rd,....
to default formate..... 04/07/2005,
03/08/1997
helpe with a function.....
i am trying this ,but it convert without th,rd,nd,...
if not (@Date IS NULL)
BEGIN
SET @Date = LEFT(@strPhotoDate,patindex('%|%',@strPhotoDate )-1)
SET @Date = replace(@strPhotoDate,'th','')
SET @Date = replace(@strPhotoDate,'nd','')
SET @Date = replace(@strPhotoDate,'rd','')
SET @Date = replace(@strPhotoDate,'st','')
SET @Date = replace(@strPhotoDate,',','')
set @Date1 = CONVERT(datetime, @Date)
--cast(@GetPhotoDate as DATETIME)
END
END
like that...
thanks in advance
Thanks
Subbu.
|
|
|
|
|
hi paul...
thank u for ur response .but i found answer for every formate....
but i need to convert dates like o4 th july 2005 or
3rd august 1997
31st december 2000
22nd july 1997....i.e th,nd,rd,....
to default formate..... 04/07/2005,
03/08/1997
helpe with a function.....
i am trying this ,but it convert without th,rd,nd,...
if not (@Date IS NULL)
BEGIN
SET @Date = LEFT(@strPhotoDate,patindex('%|%',@strPhotoDate )-1)
SET @Date = replace(@strPhotoDate,'th','')
SET @Date = replace(@strPhotoDate,'nd','')
SET @Date = replace(@strPhotoDate,'rd','')
SET @Date = replace(@strPhotoDate,'st','')
SET @Date = replace(@strPhotoDate,',','')
set @Date1 = CONVERT(datetime, @Date)
--cast(@Date1 as DATETIME)
END
END
like that...
thanks in advance
Thanks
Subbu.
|
|
|
|
|
If you are converting these dates for display purposes, you are much better to do it in your UI code.
|
|
|
|
|
Hi there,
I want to know if there is an executing speed difference between a View and a Table-Valued function.
Tnanks!
Regards,
Werries
A programmer's life is good... or is it?? Ek dink nie so nie!
|
|
|
|
|
The simple answer is yes, there are performance differences, but it all depends on what you are trying to acheive. Without further details I cannot say more. The best way to find out id to try it.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi Friends,
Let me Show u my table first and explain the problem later
CategoryID CategoryName
1 MSDN
2 Google
3 Project
4 Code
5 Google
6 Project
7 MSDN
8 Google
9 Google
10 MSDN
11 Project
12 Project
Now i need the top 2 categorynames from categorynames.
I need the result as below
CategoryID CategoryName
1 MSDN
7 MSDN
2 Google
5 Google
3 Project
6 Project
4 Code
Please some one help me in getting this Query............
Thanks in Advance 
<b></b>
|
|
|
|
|
Top 2 on what basis? What is the rule to define which way the records are sorted?
|
|
|
|
|
Top2 based on each categoryname..........
|
|
|
|
|
Then the result you have shown does not match the data in the table.
|
|
|
|
|
I m facing a problem in this query
select a.eventid,b.name,convert(nvarchar,c.assigndate,106)as assigndate,<br />
convert(nvarchar,a.eventdate,106)as eventdate,a.event,a.details,a.eventtype from event as a left outer join emp <br />
as c on a.eventid=c.eventid left outer join user_info as b on b.userid=c.userid <br />
where(a.eventtype='Single Month' or(a.eventtype='All Month' and <br />
(select substring(convert(nvarchar,edate,102),9,4) from event where eventtype='All Month')>( select substring(convert(nvarchar,getdate(),102),9,4)) ))order by a.eventdateselect * from event<br />
<br />
In event table there are some fields like eventid,eventdate,eventtype n etc
In user_info there are userid, name n etc fields
i wanna get all record when
1> eventtype is Single Month and the eventdate is in current month n eventdate is heigher than currentdate
2> eventype is All Month and eventdate is heigher than current date
eventid -----eventdate ------- eventtype
1 -----2008-04-15 00:00:00 ----- All Month
2 -----2008-04-20 00:00:00 ----- Single Month
3 -----2008-04-16 00:00:00 ----- Single Month
4 -----2008-04-16 00:00:00 ----- All Month
The answer shd be eventid 2,3,4
The Great Pleasure In Doing That Things That Other People Say U Can't By Doing This U Can Shut Their Mouth
|
|
|
|
|
Try this
select a.eventid,b.name,convert(nvarchar,c.assigndate,106)as assigndate,<br />
convert(nvarchar,a.eventdate,106)as eventdate,a.event,a.details,a.eventtype from event as a left outer join emp <br />
as c on a.eventid=c.eventid left outer join user_info as b on b.userid=c.userid <br />
WHERE a.eventdate > getdate()<br />
AND (<br />
(a.eventtype = 'Single Month' and left(convert(varchar,getdate(),102),7) = left(convert(varchar,edate,102),7))<br />
OR (a.eventtype = 'All Month')<br />
)
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi All,
I need to query the DB2 database tables and populate the Excel work sheets (one sheet for single table) using VB Macro.
Also, I need the validation in the Excel sheet itself. (DB2 database credetials)
Can you pls help me to get start?
Thanks,
Jeyakumar
Jey
modified on Wednesday, April 16, 2008 6:11 AM
|
|
|
|
|
Jay_se wrote: I need to query the DB2 database tables and populate the Excel work sheets (one sheet for single table) using VB Macro.
Also, I need the validation in the Excel sheet itself. (DB2 database credetials)
I believe Excel VBA does ADO, so you should be able to connect to the DB2 database and do your stuff. For connection information, look up www.connectionstrings.com[^].
|
|
|
|
|
Thanks ChandraRam, It is helpful.
Jey
|
|
|
|
|
Hi ,
im using two tables , tbl1,tbl2 . im multiplying tb1.column1 ,tbl2.column and displaying as result . i want to display sum(column1.column2) as total (another column).
can any body tell me the query for this ,
Thank u.
C.S.n
|
|
|
|
|
I feel I have the wrong handle on your problem but...
If you want the additional of col1 + col2 then it is the same method as the multiply
If you are after the sum of all the records preceding the current record then you need to look into running totals (depends on your DB)
if you want the sum of the 2 cols included in the result set you need to do the query 2 times, 1 to get the total into a var and the seconed to include it in your result set
You really need to define your problem a little clearer. Remember we have not gone through the process of getting where you are stuck and do not have the code in front of us.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi ,
i will explain where i stuck ..
i'm having two tables transaction and pricing . in transaction table i'm
having number of seats column and price/seat in pricing . here i want a
column which displays (no. of seats * price/ seat ) and sum of all these
rows as next row or separate column.i'm getting(no. of seats * price/seat)
using join but im not getting how to do sum of all these rows .
can u tell me this pls ..
thanks
csn
|
|
|
|
|
Depending on whether you are trying to just add the two columns together, or add the sum of the columns together, you could either do:
select tbl1.column1 + tbl2.column as summation or
select sum(tbl.column1 + tbl2.column) as total
|
|
|
|
|
mrsuman wrote: im using two tables , tbl1,tbl2 . im multiplying tb1.column1 ,tbl2.column and displaying as result . i want to display sum(column1.column2) as total (another column).
can any body tell me the query for this ,
Is this what you are looking for:
select tbl1.column1,tbl2.column2,tbl1.column1*tbl2.column2 as Result, sum(tb1.column1*tbl2.column2) as Total
This query will have the Total column as the same value for all the rows.
|
|
|
|
|
Hi,
Sorry for late reply .
here im having price of seat in one table and no. of seats in another table.
i want to get result(Price * No. Of Seats ) as amount to pay as one cloumn and total of these values as another column.
i think now u can understand my problem ..
can u tell me the query how can i do this .
thanks .
|
|
|
|
|
i will explain where i stuck ..
i'm having two tables transaction and pricing . in transaction table i'm
having number of seats column and price/seat in pricing . here i want a
column which displays (no. of seats * price/ seat ) and sum of all these
rows as next row or separate column.i'm getting(no. of seats * price/seat)
using join but im not getting how to do sum of all these rows .
can u tell me this pls ..
thanks
csn
|
|
|
|
|
Can you give an example output you want from this query?
|
|
|
|
|
Thanks for u r reply ..
ex :
result
------
200
350
500
400
250
in next row or separate column i want sum of these rows .
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
