Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Sybase query doubt

Posted on 2011-09-27
6
487 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Creating and Managing Databases with phpMyAdmin in cPanel.
Read about achieving the basic levels of HRIS security in the workplace.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to recover a database from a user managed backup

808 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