Solved

Sybase query doubt

Posted on 2011-09-27
6
481 Views
Last Modified: 2012-05-12
Hi Experts...

I have a table which has 2 columns namely startdate and stucounts whose column types are datetime and the other column int.
For eg:
If want to to get the total sum of stucounts coulumn for a particular day i was using the below mentioned query.It returned no results.

If there are many occurances of a particular day i want the total sum of the counts.
The startdate column has values in format of 2011-09-27 16:42:33.0

Here only date should be considered and time is not relevent
How do i get the total sum.
Please help
The below queries yielded no results
select count(stucounts)
from student
where startdate='2011-09-27'

select count(stucounts)
from student
where startdate like '2011-09-27'

Open in new window

0
Comment
Question by:gaugeta
  • 3
  • 2
6 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 36714541
try this

select count(stucounts)
from student
where convert( char(8), startdate, 104 ) ='2011.09.27'
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 36714544
for all dates counts
try this

select convert( char(8), startdate, 104 ) ,count(stucounts)
from student
group by  convert( char(8), startdate, 104 )
0
 

Author Comment

by:gaugeta
ID: 36715056
@pratima_mcs:I tried the following query and its not returning any results.
select count(stucounts)
from student
where convert( char(8), startdate, 104 ) ='2011.09.27'

But in database there is an entry
startdate                             stucounts
2011-09-27 09:28:39.0      1564


And when i tried the second query i got:
Date              Counts
02.02.20      1
How do i fix this .
Please help...
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

Author Comment

by:gaugeta
ID: 36715086
@pratima_mcs:I noticed that the below query is working.
But the last part i e 27.09.20 where 20 is the first two characters of the year.
How do i specify the same as 27.09.2011 since this will consider even 2009 etc.
Please help...
select count(stucounts)
from student
where convert( char(8), startdate, 104 ) ='27.09.20'

Open in new window

0
 
LVL 2

Accepted Solution

by:
drittenh earned 250 total points
ID: 36715523
Actually the above should be implying the year 2020...
- the "104" date conversion is defined as dd.mm.yy[yy]        

- so just add a couple characters to the length of the char it is being converted to and specify the full year.

select count(stucounts)
from student
where convert( char(10), startdate, 104) = '27.09.2011'

HTH,

- David
0
 
LVL 39

Assisted Solution

by:Pratima Pharande
Pratima Pharande earned 250 total points
ID: 36715542
select count(stucounts)
from student
where convert( char(10), startdate, 104 ) ='27.09.20'
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question