SQL server 2008
nchar col that holds whole numbers representing time: 0800 or 0900
Datetime col 2015-02-17 11:12:34.000
I need to convert each to a numeric for comparison using only HH:mm from the DateTime Column.
WHERE nchar is BETWEEN starttime and endtime
I am new to development, only 2wks, any advice appreciated. I am not sure how to get an accurate comparison.
here's to convert into same format--
DECLARE @TBL TABLE (WholeNos CHAR(4),dtTime Datetime)
INSERT INTO @TBL(WholeNos,dtTime)
SELECT '0800','2015-02-17 11:12:34.000'
UNION ALL
SELECT '0900','2015-02-17 11:14:34.000'
SELECT WholeNos,REPLACE(CONVERT(varchar(5), dtTime, 108), ':', '') as dtTime from @TBL
I wouldn't use VARCHAR(5) for this because the size of the result string will never vary. Use CHAR(5) and avoid the extra two bytes that VARCHAR uses to keep track of the length of the string. And I'm pretty sure I wouldn't go anywhere near NCHAR for a represent a time. That's a doubling of bytes and trying to represent digits and a common symbol and NCHAR just seems like a waste.
I was thinking it would be better to convert the CHAR to TIME and compare (as time) [integer maths] ... but, I suppose IF Time is stored as CHAR then probability exists for invalid entries, and convert TIME to CHAR and then compare would solve that.
Better** to store the Time as a TIME datatype in the first place, rather than CHAR ... let alone NChar, as Jeff has said.
** Validated to be a real time and no goofy data, sort in chronological order, use DIFF and ADD etc. functions on the Time values, Format on output in any style as necessary.