Solved

Sybase query doubt

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle - Create Procedure with Paramater 16 57
How to free up undo space? 3 31
Can't Access My Database 57 35
Service Catalogue and Data Migration doc 3 18
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

863 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now