• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1163
  • 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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

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