• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 678
  • Last Modified:

CONVERT DATETIME TO MINUTES

I am trying to convert a time entered in column (TIL_TAKEN) in my table as 00:00 format to Minutes  in order to calculate a new time  CALLED (TIME_LIEU) .Time in lieu would be the result of
 TIL_TAKEN - REMAINING_UNFORMATTED. REMAINING_UNFORMATTED is shown in Minutes so in order to do the calculation TIL_TAKEN must also be in Minutes.
I have tried doing DATEPART(MINUTE,til_taken) AS TIME_LIEU , this keeps giving me 0
Any ideas
My code is attached

SELECT     flexcel.*,  UNFORMATTED_REMAINING
FROM         (SELECT     CAST([total week] / 60 AS varchar(7)) + ':' + RIGHT('00' + CAST([total week] % 60 AS varchar(2)), 2) AS TimeInHours, empid, CONVERT(char(5), 
                                              START_MON, 108) AS START_MON, CONVERT(char(5), LUNCH_OUT_MON, 108) AS LUNCH_OUT_MON, CONVERT(char(5), 
                                              LUNCH_BACK_MON, 108) AS LUNCH_BACK_MON, CONVERT(char(5), END_MON, 108) AS END_MON, Mon_morning, Mon_Evening, 
                                              CONVERT(char(5), START_TUE, 108) AS START_TUE, CONVERT(char(5), LUNCH_OUT_TUE, 108) AS LUNCH_OUT_TUE, CONVERT(CHAR(5), 
                                              LUNCH_BACK_TUE, 108) AS LUNCH_BACK_TUE, CONVERT(CHAR(5), END_TUE, 108) AS END_TUE, TUE_morning, TUE_Evening, 
                                              CONVERT(CHAR(5), START_Wed, 108) AS START_WED, CONVERT(CHAR(5), LUNCH_OUT_Wed, 108) AS LUNCH_OUT_Wed, 
                                              CONVERT(CHAR(5), LUNCH_BACK_Wed, 108) AS LUNCH_BACK_WED, CONVERT(CHAR(5), END_Wed, 108) AS END_Wed, Wed_morning, 
                                              Wed_Evening, CONVERT(CHAR(5), START_Thu, 108) AS START_THU, CONVERT(CHAR(5), LUNCH_OUT_Thu, 108) AS LUNCH_OUT_THU, 
                                              CONVERT(CHAR(5), LUNCH_BACK_Thu, 108) AS LUNCH_BACK_THU, CONVERT(CHAR(5), END_Thu, 108) AS END_THU, Thu_morning, 
                                              Thu_Evening, CONVERT(CHAR(5), START_Fri, 108) AS START_FRI, CONVERT(CHAR(5), LUNCH_OUT_Fri, 108) AS LUNCH_OUT_FRI, 
                                              CONVERT(CHAR(5), LUNCH_BACK_Fri, 108) AS LUNCH_BACK_FRI, CONVERT(CHAR(5), END_Fri, 108) AS END_FRI, Fri_morning,CONVERT(CHAR(5), TIL_TAKEN, 108) AS TIL, 
                                              Fri_Evening, CAST(tot_Monday / 60 AS varchar(7)) + ':' + RIGHT('00' + CAST(tot_Monday % 60 AS varchar(2)), 2) AS TOT_MONDAY, 
                                             CAST(tot_Tueday / 60 AS varchar(7)) + ':' + RIGHT('00' + CAST(tot_Tueday % 60 AS varchar(2)), 2) AS TOT_TUEDAY, 
                                              CAST(tot_Wedday / 60 AS varchar(7)) + ':' + RIGHT('00' + CAST(tot_Wedday % 60 AS varchar(2)), 2) AS TOT_WEDDAY, 
                                              CAST(tot_Thuday / 60 AS varchar(7)) + ':' + RIGHT('00' + CAST(tot_Thuday % 60 AS varchar(2)), 2) AS TOT_THUDAY, 
                                              CAST(tot_Friday / 60 AS varchar(7)) + ':' + RIGHT('00' + CAST(tot_Friday % 60 AS varchar(2)), 2) AS TOT_FRIDAY, [total week], 
                                              [total week] - WEEK_HRS AS unformatted_remaining, WEEK_HRS, Month_No, Monday_dates, flex_id AS flex_id, Comment, Confirmed, 
                                              Week_No, Submit,  Manager_Cert, TIME_LIEU
                       FROM          (SELECT     time2.*, tot_Monday + tot_Tueday + tot_Wedday + tot_Thuday + tot_Friday AS [total week]
                                               FROM          (SELECT     time1.*, mon_morning + mon_evening AS tot_Monday, tue_morning + tue_evening AS tot_Tueday, 
                                                                                              wed_morning + wed_evening AS tot_Wedday, thu_morning + thu_evening AS tot_Thuday, 
                                                                                              fri_morning + fri_evening AS tot_Friday
                                                                       FROM          (SELECT     empid, START_MON, LUNCH_OUT_MON, LUNCH_BACK_MON, END_MON, DATEDIFF(n, START_MON, 
                                                                                                                       LUNCH_OUT_MON) AS Mon_morning, DATEDIFF(n, LUNCH_BACK_MON, END_MON) AS Mon_Evening, 
                                                                                                                      START_TUE, LUNCH_OUT_TUE, LUNCH_BACK_TUE, END_TUE, DATEDIFF(n, START_TUE, 
                                                                                                                      LUNCH_OUT_TUE) AS TUE_morning, DATEDIFF(n, LUNCH_BACK_TUE, END_TUE) AS TUE_Evening, 
                                                                                                                      START_Wed, LUNCH_OUT_Wed, LUNCH_BACK_Wed, END_Wed, DATEDIFF(n, START_Wed, 
                                                                                                                      LUNCH_OUT_Wed) AS Wed_morning, DATEDIFF(n, LUNCH_BACK_Wed, END_Wed) AS Wed_Evening, 
                                                                                                                      START_Thu, LUNCH_OUT_Thu, LUNCH_BACK_Thu, END_Thu, DATEDIFF(n, START_Thu, LUNCH_OUT_Thu)
                                                                                                                       AS Thu_morning, DATEDIFF(n, LUNCH_BACK_Thu, END_Thu) AS Thu_Evening, START_Fri, 
                                                                                                                      LUNCH_OUT_Fri, LUNCH_BACK_Fri, END_Fri, DATEDIFF(n, START_Fri, LUNCH_OUT_Fri) AS Fri_morning, 
                                                                                                                      DATEDIFF(n, LUNCH_BACK_Fri, END_Fri) AS Fri_Evening, CONVERT(VARCHAR, '2085', 3) AS WEEK_HRS, 
                                                                                                                      Month_No, Monday_dates, flex_id, Comment, Confirmed, Week_No, Submit, til_taken, DATEPART(MINUTE,til_taken) AS TIME_LIEU, 
                                                                                                                      Manager_Cert
                                                                                               FROM          dbo.EMPLOYEE_TIMESHEET) Time1) time2) FinalTime) flexcel

Open in new window

0
Camnoc
Asked:
Camnoc
7 Solutions
 
Kevin CrossChief Technology OfficerCommented:
Something like this:
(CAST(LEFT(TIL_TAKEN, CHARINDEX(':',TIL_TAKEN)-1) AS INT) * 60 + CAST(RIGHT(TIL_TAKEN, CHARINDEX(':', REVERSE(TIL_TAKEN))-1) AS INT) - REMAINING_UNFORMATTED) AS TIME_LIEU

Open in new window

0
 
folderolCommented:
You can use datediff(), I saw this here on EE last week.

Try

Select datediff(minute, 0, cast('12:30' as datetime))

to see the effect.  This will return 750, which is the number of minutes since midnight.

0
 
Kevin CrossChief Technology OfficerCommented:
Yes, folderol is correct that will work as well as it basically creates a datetime stamp at the beginning date a 12:30.  For example on my SQL Server this is 1/1/1900 12:30:00 PM.
0
Industry Leaders: 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!

 
Kevin CrossChief Technology OfficerCommented:
I like that method!
0
 
kerwinsiyCommented:
hi what is the datatype of til_taken
0
 
CamnocAuthor Commented:
TIL_TAKEN IS DATATYPE  Datetime
0
 
Kevin CrossChief Technology OfficerCommented:
Is it stored with same date returned by CAST(0 as DateTime) or is each entry stored with datetime when value was saved?

i.e.

1/1/1900 12:30:00.000 OR 11/1/2008 12:30:00.000
0
 
Kevin CrossChief Technology OfficerCommented:
If the former (1/1/1900), then use folderol suggestion - http:#22882211 - like this:
SELECT DATEDIFF(minute, 0, TIL_TAKEN)

If the latter, then use something like this to find midnight of the day entered and then do the date difference in minutes:
SELECT DATEDIFF(minute, DateDiff(dd, 0, TIL_TAKEN), TIL_TAKEN)

DateDiff(dd, 0, TIL_TAKEN) --> this is what gets you midnight of date specified by TIL_TAKEN
0
 
CamnocAuthor Commented:
Thank you all so much.
Excellent
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now