4

I need to take a DATETIME column (Column A) that contains only a date, then add a VARCHAR(5) column (Column B) which contains a 24-hour representation of a time (i.e. 1330) and combine them to get a valid DATETIME to be used in a DATEDIFF command.

So for example, Column A contains 2013-07-01 00:00:00.000 and Column B contains 1330. The result should be 2013-07-01 13:30:00.000.

Edit: just to claify things, this is an existing DB that stores the data this way, it is out of scope of my project to completely change how things work to make it store the data in a proper way. If I could I would but I can't so I am stuck with the multiple columns. Also the data in Column B will always be a 4 digit time as the input page has checks to make sure time is a valid military time format.

  • 1
    Do you also want to check for inconsistent (with time) data, like having '2430' or '1289' or '33300' or just trash like '2ab77'? – ypercubeᵀᴹ Jul 8 '13 at 15:09
  • To avoid the problem @ypercube mentions, why not store this as a proper datetime value in the first place? What are you gaining by storing the date without time and then time as a separate, unverifiable string? – Aaron Bertrand Jul 8 '13 at 16:00
  • There is code on the ASPX page that prevents them from entering incorrect military times. So hat will not be a factor. @AaronBertrand It is not my call on how to store them, I am doing a small modification to a proc not creating a new DB. This is how the system stores the data and it is out of scope to change multiple tables, procs, and ASP.Net pages. – user11512 Jul 8 '13 at 16:16
  • So your validation is on the front end? What if someone runs an update statement in Management Studio? – Aaron Bertrand Jul 8 '13 at 16:17
  • 1
    I'm sorry, when did you explicitly state that changing the entire design was out of scope? Certainly not at the time you posted the question. Nobody is suggesting that you need to explain your entire business practice on every question, just that you shouldn't get all bent out of shape when people suggest things that you didn't state up front that you couldn't do (and that you apparently could have predicted). – Aaron Bertrand Jul 8 '13 at 16:58
6

This will also deal with values < 10 AM that don't have all 4 digits. (Also that column should probably be CHAR(4) instead of VARCHAR(5) - or better yet, stop separating these and store a proper datetime value in the first place).

DECLARE @t TABLE(ColumnA DATETIME, ColumnB VARCHAR(5));

INSERT @t VALUES('20130701', '1330'),('20130701', '930');

SELECT CONVERT(DATETIME, CONVERT(CHAR(8), ColumnA, 112) 
    + ' ' + LEFT(RIGHT('0' + ColumnB, 4),2) 
    + ':' + RIGHT(ColumnB, 2))
FROM @t;
  • It is not my call on how to store them, I am doing a small modification to a proc not creating a new DB. This is how the system stores the data and it is out of scope to change multiple tables, procs, and ASP.Net pages – user11512 Jul 8 '13 at 16:17
  • 1
    +1. That LEFT(RIGHT('0'+ construct is cunning. – Greenstone Walker Jul 8 '13 at 21:10
0
declare @ColumnA datetime
declare @ColumnB varchar(5)

set @ColumnA = '01Jan1980'
set @ColumnB = '1300'

select DATEADD(MI, cast(substring(@ColumnB,1,2) as int) *60 + CAST(substring(@ColumnB,3,2) as int),@ColumnA)
  • This ends up with the wrong time if 930 is stored in ColumnB (I get 21:00). – Aaron Bertrand Jul 8 '13 at 15:32
0

If you're using SQL 2012 you could use this function:

DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )

It returns a datetime.

  • But then you'd have to extract all those parts using YEAR, MONTH, and DAY against the first column, and then string manipulation to get hours and seconds from the second column. This function is mostly useful when those parts are already broken out. – Aaron Bertrand Jul 8 '13 at 15:29
0

You can maybe do this in less steps, but you get the idea.

-- Declare some variables for casts, makes the example a little clearer
declare @new_date date;
declare @new_time time;
declare @new_datetime datetime;

-- Set our starting values
declare @original_date datetime = cast('2013-07-01 00:00:00.000' as datetime);;
declare @original_time varchar(5) = '1330';

-- Truncate time part of datetime, just in case
set @new_date = cast(@original_date as date);

-- Create time variable from varchar
set @new_time = cast(left(@original_time,2) + ':' + right(@original_time,2) as time)

-- Combine date and time variables
set @new_datetime = @new_date + cast(@new_time as datetime);
  • All these steps are quite problematic if you want to deal with more than one row at a time. Also shorthand datetime math is nice but be aware that it does not work with newer types. Finally, we don't know if the OP is on SQL Server 2008 or better, so date/time may be ineligible. – Aaron Bertrand Jul 8 '13 at 15:45
0

I'm making the assumption that there does not exist a : already in ColumnB, although you could use a replace to strip them at need. And a second stuff if your ColumnB ever contains seconds.

declare @ColumnA datetime
declare @ColumnB varchar(5)

set @ColumnA = '2013-07-01 00:00:00.000'
set @ColumnB = '1330'

SELECT CAST(CONVERT(varchar,@ColumnA,101) + ' ' + STUFF(RIGHT('0'+@ColumnB,4),3,0, ':') AS DateTime)
  • @AaronBertrand Good point. Fixed. – Kenneth Fisher Jul 8 '13 at 16:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy