Fairfield
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
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)))
In PZEIT column, you have data like 51328. That means, 5:13:28 ?
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
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
ASKER
Daniel,
How would I use your code if my table is DRAP?
How would I use your code if my table is DRAP?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
try
Open in new window