Comparing the values of two columns with different datatypes

Question

I have two columns in a table which need to be compared against the data.The problem that I am facing is that each of the columns have different datatypes: float and datetime2.

EventID EventStartDate
------- ---------------------------
201605  2017-09-21 12:40:29.9800000
201504  2017-09-21 12:40:46.8430000
201606  2017-09-21 12:41:39.3370000
201609  2017-09-21 12:41:57.3100000

Now, I have to compare the values in the column named EventID with the values in the column named EventStartDate; e.g., I need to compare the year and month in '201605' with the year and month parts of '2017-09-21 12:40:29.9800000'.

Answer 1

Here's an example of how you could use a combination of CONVERT, REPLACE and SUBSTRING to 'convert' your DateTime2 column to a float.

You can 'convert' the DateTime2 column to a varchar data type, then use SUBSTRING to retrieve the CCYY-MM part and then use REPLACE to eliminate the '-' in the date.

I added an extra row to the sample data so you could see that the compare works.

SET NOCOUNT ON
Declare @T TABLE (EventID float, EventStartDate Datetime2)
insert into @T VALUES
(201605, '2017-09-21 12:40:29.9800000'),
(201504, '2017-09-21 12:40:46.8430000'),
(201606, '2017-09-21 12:41:39.3370000'),
(201609, '2017-09-21 12:41:57.3100000'),
(201709, '2017-09-21 12:41:57.3100000')

SELECT *
    ,CONVERT(FLOAT, REPLACE(SUBSTRING(CONVERT(VARCHAR(7), EventStartDate), 1, 7), '-', ''))
FROM @T
WHERE EventID = CONVERT(FLOAT, REPLACE(SUBSTRING(CONVERT(VARCHAR(7), EventStartDate), 1, 7), '-', ''))

Results:

| EventID | EventStartDate              | (No column name) |
|---------|-----------------------------|------------------|
| 201709  | 2017-09-21 12:41:57.3100000 | 201709           |

Answer 2

Assuming that your EventID will always be in a format: first 4 digits for a year and the last 2 for a month:

SELECT * FROM `table` WHERE LEFT(EventID, 4) = YEAR(EventStartDate) AND RIGHT(EventID, 2) = MONTH(EventStartDate);

Answer 3

I think this will be help to you.

SELECT CAST (CONVERT(VARCHAR(5),DATEPART(YY,GETDATE())) +
             CONVERT(VARCHAR(5),DATEPART(MM,GETDATE())) AS FLOAT)