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

Converting a VARCHAR into DATETIME format

Stumped on this...I am trying to do some reporting off of a badly designed database.

I have a column called 'Hours'.  The column is VARCHAR (10) and the data is formatted 00:00

Since it is VARCHAR I am unable to sum up the total grouped by employee.

I am looking to take the column 'Hours' and convert from VARCHAR to DATETIME.  I have tried REPLACE, CONVERT, SUBSTRING and I guess I am just not getting the right combination of syntax.  I have tried to strip the column into two columns and sum hours and minutes but then I would need some time of calculation to rollover 60 minutes as an hour...too confusing.

So visually I need this

0
PROJHOPE
Asked:
PROJHOPE
  • 3
  • 3
1 Solution
 
Patrick MatthewsCommented:
Hello PROJHOPE,

Try this:

SELECT EmpID, SUM(24 * CONVERT(decimal(19, 6), CONVERT(datetime, Hours))) AS DecimalWorkHours
FROM YourTable
GROUP BY EmpID

For example, if the total working time were 20 hours, 15 minutes, should return 20.25.

Regards,

Patrick
0
 
PROJHOPEAuthor Commented:
Hi Patrick,

Tried your script but didn't get the desired results:

Using a real example:

Hours column

07:30

using the convert script brought back

15.000000 in the DecimalWorkHours calculated column

If an employee has this:

Day 1 07:30
Day 2 07:45
Day 3 08:53

It would be nice to first convert to decimal then I can sum the column

something like a case statement

Case :30 THEN .15
:45 THEN .75
but then you have those that have :53, :42, :33 and so forth that could make this difficult.
0
 
Patrick MatthewsCommented:
OK, trying again :)


SELECT EmpID, SUM(CONVERT(decimal(9, 4), LEFT(Hours, 2)) + (CONVERT(decimal(9, 4), RIGHT(Hours, 2)) / 60)) AS TotHrs
FROM YourTable
GROUP BY EmpID
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
DotNetBuddhaCommented:
[code]
declare @test varchar(10)
declare @date datetime,
      @newdate datetime

set @date = getdate()
set @test = '07:45'

select cast(convert(varchar(10),@date,101) + ' ' + @test as datetime)  as ConvertedDate
select @newdate = cast(convert(varchar(10),@date,101) + ' ' + @test as datetime)

select datediff(hh,getdate(),@newdate)

[/code]

I would make the above into a function to make your life easier since you will be combining the date field and the text field often to make a datetime.
0
 
PROJHOPEAuthor Commented:
matthewspatrick,

Using the new code...same results:

the TotHrs calculated column shows at 15.0000 for 07:30

DotNetBuddha, One question, the query is a SELECT statements so if I put the DECLARE above it how will I be able to put the column 'hours' as the @test field parameter?

I cannot put set @test = A.hours because the column nor its table has yet to be defined.



0
 
PROJHOPEAuthor Commented:
I stand corrected...matthewspatrick your code works sorry for doubting you.

If I create a simple script:

select *,SUM(CONVERT(decimal(9, 4), LEFT(Hours, 2)) + (CONVERT(decimal(9, 4), RIGHT(Hours, 2)) / 60)) AS TotHrs from timecard_raw
where serial_number = '000002074'
and pay_period = '2006/08/31'
group by serial_number,pay_period,sequence,detail_date,timecode_id,ld_code1,ld_code2,ld_code3,ld_code4,
ld_code5,start_time,stop_time,lunch_start,lunch_stop,hours,modified_serial_number,delete_row,line_comment,
display_date,pay_period_sys_id,approval_status,approval_sys_id,approval_timestamp,auto_id

It works!!!

But when I put it in my Select statement it doesn't...perhaps you can see where my statement is off...thanks again...

SELECT    
A.serial_number,
CONVERT(VARCHAR(10),CONVERT(datetime,A.pay_period),101) AS 'Pay Period',
A.detail_date,
A.sequence,
A.timecode_id,
A.ld_code3 AS 'Project Code',
A.hours AS 'Hours Non-Formatted',
SUM(CONVERT(decimal(9, 4), LEFT(A.hours, 2)) + (CONVERT(decimal(9, 4), RIGHT(A.hours, 2)) / 60)) AS 'Hours Formatted',
B.last_name,
B.middle_name,
B.first_name,
MAX(DATEADD (mi,B.audit_timestamp,'01/01/1930')) AS 'Employee Timestamp'
FROM
Ceridian.dbo.timecard_raw AS A INNER JOIN
Ceridian.dbo.employee AS B ON A.serial_number = B.serial_number
WHERE    
(A.serial_number = '000002074' AND B.last_name IS NOT NULL AND
B.middle_name IS NOT NULL AND
B.first_name IS NOT NULL)
GROUP BY A.serial_number, A.pay_period, A.detail_date,A.sequence, A.timecode_id,
A.ld_code3, A.hours, B.last_name,B.middle_name, B.first_name
0
 
Patrick MatthewsCommented:
PROJHOPE said:
>>I stand corrected...matthewspatrick your code works sorry for doubting you.

That's a relief.  I had just gone and created a test scenario in SQL Server, and could not
understand why it was not working for you when it worked for me every time  :)

I do not see anything obviously wrong with your SQL.  My guess is that either you need
to refine the join, and/or refine the WHERE conditions.
0

Featured Post

Technology Partners: 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!

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