Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 2.0 > ASP.NET 2.0 Basics
Password Reminder
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old January 4th, 2008, 04:30 PM
Authorized User
Join Date: Jan 2004
Location: , , .
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
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!

Reply With Quote
  #2 (permalink)  
Old January 9th, 2008, 01:09 AM
Registered User
Join Date: Jan 2008
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts

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

Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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

All times are GMT -4. The time now is 05:37 AM.

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