[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 299
  • Last Modified:

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
0
Fairfield
Asked:
Fairfield
  • 4
  • 2
  • 2
  • +2
1 Solution
 
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
 
SharathData EngineerCommented:
In PZEIT column, you have data like 51328. That means, 5:13:28 ?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
wdosanjosCommented:
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
 
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now