Link to home
Start Free TrialLog in
Avatar of Fairfield
FairfieldFlag for United States of America

asked on

Combine two fields to create date and time

I have a table with two separate fields that I need to combine into one date/time field.  The two fields are DATUM and PZEIT.  I would like the resulting field to look like 5/1/2011 08:00:10.  I will be using this resulting field to perform a comparison to find a date difference. result.csv
Avatar of Ephraim Wangoya
Ephraim Wangoya
Flag of United States of America image


try
select  DATUM + ' ' + substring(PZEIT, 1, 2) + ':' + substring(PZEIT, 3, 2) + ':' + substring(PZEIT, 5, 2)
from yourtable

Open in new window

You wanted datetime type?

DECLARE @PZEIT VARCHAR(6)
DECLARE @DATUM VARCHAR(10)
SET @DATUM='5/1/2011'
SET @PZEIT='051329'
SELECT CONVERT(DATETIME,@DATUM+' '+LTRIM(SUBSTRING(@PZEIT,1,2))+':'+LTRIM(SUBSTRING(@PZEIT,3,2))+':'+LTRIM(SUBSTRING(@PZEIT,5,2)))

Open in new window

In PZEIT column, you have data like 51328. That means, 5:13:28 ?
Avatar of Fairfield

ASKER

Shrath, yes that is correct
the long way

select  DATUM + ' ' +
           substring(stuff(PZEIT, 1, 0, REPLICATE(0, 6 - len(PZEIT))), 1, 2) + ':' +
            substring(stuff(PZEIT, 1, 0, REPLICATE(0, 6 - len(PZEIT))), 3, 2) + ':' +
            substring(stuff(PZEIT, 1, 0, REPLICATE(0, 6 - len(PZEIT))), 5, 2)
from yourtable
Daniel,

How would I use your code if my table is DRAP?
ASKER CERTIFIED SOLUTION
Avatar of wdosanjos
wdosanjos
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you wdosanjos, good code...

@wdosanjos

That does not work, look again at the sample data given

51328
51329
etc

You have to left pad the data
@ewangoya look the provided csv file in NOTEPAD (not Excel). PZEIT does contain leading zeros, which also indicates it is a character column not a numeric column.

If thats the case then all is good, similar to what I provided in the first post :)

I did use excel so I missesd the leading zeros