Solved

Sybase query doubt

Posted on 2011-09-27
6
494 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

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.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

749 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