?
Solved

Sybase query doubt

Posted on 2011-09-27
6
Medium Priority
?
507 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
[X]
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
  • 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

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 1000 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 1000 total points
ID: 36715542
select count(stucounts)
from student
where convert( char(10), startdate, 104 ) ='27.09.20'
0

Featured Post

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

771 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