Jeff Moden (6/15/2014)
Eirikur Eiriksson (6/15/2014)
Didn't see Lynn's code before I wrote the example, almost the same but without a string thingy😎
/*Converting INT date and time to datetime */DECLARE @INT_YYYYMMDD INT = 20140704;
DECLARE @INT_HHMMSS INT = 012056;
SELECT DATEADD(SECOND,
(((@INT_HHMMSS / 10000) * 3600) -- hours to seconds
+ (((@INT_HHMMSS / 100) % 100) * 60) -- minutes to seconds
+ (@INT_HHMMSS % 100)) -- seconds
, CONVERT(DATETIME2(0),CAST(@INT_YYYYMMDD AS VARCHAR(8)),112))
Results
2014-07-04 01:20:56Just curious... since the use of CONVERT kills the possibility of portability anyway (and I don't believe in portable code for anything but C.R.U.D.), why did you feel it necessary to use DATETIME2? Definitely not a challenge here... just curious.
Just being slightly stingy here;-) saving two bytes
😎
DECLARE @dt DATETIME = GETDATE();
DECLARE @dt2 DATETIME2(0) = GETDATE();
SELECT 'DATETIME' AS DATA_TYPE, DATALENGTH(@DT) AS DATA_LENGTH
UNION ALL
SELECT 'DATETIME2(0)', DATALENGTH(@DT2);
Results
DATA_TYPE DATA_LENGTH
------------ -----------
DATETIME 8
DATETIME2(0) 6
And of course I have been careful not to do funny stuff with datetime since someone told me off a while back 😀