Wrox Programmer Forums
|
ASP.NET 2.0 Basics If you are new to ASP or ASP.NET programming with version 2.0, this is the forum to begin asking questions. Please also see the Visual Web Developer 2005 forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 2.0 Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 4th, 2008, 04:30 PM
Authorized User
 
Join Date: Jan 2004
Posts: 30
Thanks: 1
Thanked 0 Times in 0 Posts
Default Convert String To Time

I need to substract break and lunch times based on the following
Sql statement

CREATE VIEW dbo.vwu_ExcelReport
AS
select, logout, login, AMBreakOut, AMBreakIn, LunchOut, LunchIn, PMBreakOut, PMBreakIn
            , CASE
  WHEN ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) >= 0 AND ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) <= 19
    THEN '00:00'
  WHEN ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) >= 20 AND ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) <= 34
    THEN '00:15'
  WHEN ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) >= 35 AND ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) <= 49
    THEN '00:30'
  WHEN ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) > = 50 AND ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) <= 64
    THEN '00:45'
  ELSE ' 01:00'
END AS AMBreak <=
            , CASE
  WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) >= 0 AND ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) <= 66
    THEN '00:0'
  WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) >= 67 AND ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) <= 81
    THEN '00:15'
  WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) >= 82 AND ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) <= 96
    THEN '00:30'
  WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) >= 97 AND ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) <= 111
    THEN '00:45'
  WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) >= 112 AND ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) <= 126
    THEN '01:00'
  ELSE '01:15'
END AS LunchBreak <=
          , CASE
  WHEN ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) >= 0 AND ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) <= 19
    THEN '00:00'
  WHEN ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) >= 20 AND ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) <= 34
    THEN '00:15'
  WHEN ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) >= 35 AND ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) <= 49
    THEN '00:30'
  WHEN ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) >= 50 AND ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) <= 64
    THEN '00:45'
  ELSE '01:00'
END AS PMBreak <=
form timetable

When I use AMBreak, LunchBreak, PMBreak in my code behind file (VB) the variables are all string and do not add nor substract. TimeWorked shows up as a continous string of:
1/1/1900 6:28:50 AM00:0000:4500:00 .

    Protected Function getTimeWorked(ByVal dataitem As Object) As Object

        Dim strTimeLog As String = DataBinder.Eval(dataitem, "TimeLog")
        Dim strAMBreak As String = DataBinder.Eval(dataitem, "AMBreak")
        Dim strLunchBreak As String = DataBinder.Eval(dataitem, "LunchBreak")
        Dim strPMBreak As String = DataBinder.Eval(dataitem, "PMBreak")

        Dim TimeWorked As String = strTimeLog + strAMBreak + strLunchBreak + strPMBreak

        Return TimeWorked

    End Function

when my goal is to subtract the logout from login, minus AMBreak , LunchBreak, PMBreak where applicable.
SQL will not accept the variables using (logout-login) - (AMBreak) - (LunchBreack) - (PMBreak).
When writing the results to the browser the correct values for AMBreak, LunchBreak, PMBreak are written

A solution to this is so very much appreciated!

Gail
 
Old January 9th, 2008, 01:09 AM
Registered User
 
Join Date: Jan 2008
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Just curious, why are you doing this completely in SQL? I believe it could be done much easier and cleaner in C#.






Similar Threads
Thread Thread Starter Forum Replies Last Post
convert time field to number jobalistic Access 17 February 22nd, 2013 12:40 PM
convert time to minutes stolte XSLT 3 November 21st, 2008 04:12 AM
[Resolved] Convert Time to Integer snufse ASP.NET 2.0 Basics 1 October 7th, 2008 11:44 AM
convert to string prafullaborade XML 7 May 29th, 2008 02:59 PM
Time Convert in ASP ;Please help blueguy Classic ASP Components 6 August 5th, 2004 02:49 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.