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
FairfieldAsked:
Who is Participating?
 
wdosanjosConnect With a Mentor Commented:
Please try the following:

Select CONVERT(DATETIME, DATUM + ' ' + SUBSTRING(PZEIT,1,2) + ':' + SUBSTRING(PZEIT,3,2) + ':' + SUBSTRING(PZEIT,5,2)) DATUMPZEIT
From YourTable

Open in new window

0
 
Ephraim WangoyaCommented:

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

Open in new window

0
 
Daniel_PLDB Expert/ArchitectCommented:
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

0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
SharathData EngineerCommented:
In PZEIT column, you have data like 51328. That means, 5:13:28 ?
0
 
FairfieldAuthor Commented:
Shrath, yes that is correct
0
 
Ephraim WangoyaCommented:
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
0
 
FairfieldAuthor Commented:
Daniel,

How would I use your code if my table is DRAP?
0
 
Daniel_PLDB Expert/ArchitectCommented:
Thank you wdosanjos, good code...
0
 
Ephraim WangoyaCommented:

@wdosanjos

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

51328
51329
etc

You have to left pad the data
0
 
wdosanjosCommented:
@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.
0
 
Ephraim WangoyaCommented:

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
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.

All Courses

From novice to tech pro — start learning today.