Querry-How to get date from datetime format

Question

0.00/5 (No votes)

See more:

Hi, i want to know total numbers of hours in a working day.

columns are-

employeeid,startdate,totalhours

there are multiple entries on a single day with different time.
problem is that startdate is in format of- yyyy-mm-dd hh-mm-dd 000
and user is giving date in - dd-mm-yyyy
i am converting this date to convert(varchar,getdate(),111) it gives yyyy-mm-dd format

when i select employee id and startdate i want sum of totalhours of that particular date.

Thanks

Posted 1-Mar-12 19:15pm

soothingshruti

Updated 1-Mar-12 19:18pm

v2

Add a Solution

Comments

The Doer 2-Mar-12 1:55am

how you are storing totalhours in ur table??
I mean whether you have created any function or user only has to enter the 'totalhours' in the table

soothingshruti 2-Mar-12 2:11am

when i am inserting the data ,
i wrote this code-

HourTaken = EnDate.Subtract(StrDate);
Int32 hour = HourTaken.Hours;
Int32 Min = HourTaken.Minutes;
TotalTimeTaken = hour + "." + Min;

soothingshruti 2-Mar-12 2:17am

user just enter start date and end date with time.
an insert querry Convert.ToDecimal(TotalTimeTaken)

The Doer 2-Mar-12 2:22am

are you using SQL server?? or some other ??you tagged question as sql server, but syntax seems to differ..
so are you displaying hour in hh.mm format?? if it is so then the query has to be modifies, because here two fields are there i:e hh & mm

soothingshruti 2-Mar-12 2:30am

i am using sql server

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

The Doer · Answer 1 · 2012-03-01T20:16:00

Solution 1

Try this once-->

select employeeid,startdate,sum(totalhours) from tableName group by startDate,employeeid

it will display the employeeid,startdate and total no of hours employee worked on respective dates.

Posted 1-Mar-12 20:16pm

The Doer

Comments

soothingshruti 2-Mar-12 2:28am

thanks for reply,i think you didn't understand my question.
when user is select particular date then i want totalhours on that date.
there are multiple entries from different time.
i am giving you a exam.-

employeeid startdate enddate totalhours
100 2012-02-20 12:00:00.000 2012-02-20 13:00:00.000 1.0
100 2012-02-20 14:00.000 2012-02-20 18:00:00.000 2.0
100 2012-02-20 10:45:00.000 2012-02-20 12:00:00.000 1.5

user is give the date 20/02/2012 .

rajdeep kumar · Answer 2 · 2012-03-01T21:45:00

Solution 2

hi there.....

try this, u will exact total hours in hh:mm format

SQL

SELECT employeeid,startdate,enddate,RIGHT('0' + CAST(DATEDIFF(HH,StartDate,EndDate) AS VARCHAR),2) + ':' +
RIGHT('0' + CAST(DATEDIFF(MI, StartDate,EndDate) % 60 AS VARCHAR),2) AS 'Total Hours'
FROM tableName

hope this helps...
revert back with ur comments...

Posted 1-Mar-12 21:45pm

rajdeep kumar

Updated 1-Mar-12 22:03pm

v3

Comments

soothingshruti 2-Mar-12 7:56am

Thanks for reply Rajdeep,
your sum of hours is gives result only in HH i want also in HH:MM and
i want sum of total hours for a particular start date and end date