0

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'.

2

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           |
| improve this answer | |
1

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);
| improve this answer | |
  • 1
    Please accept the answer you wish for which you wish to do so! Also, you can upvote other answers. – Vérace Sep 21 '17 at 14:29
0

I think this will be help to you.

SELECT CAST (CONVERT(VARCHAR(5),DATEPART(YY,GETDATE())) +
             CONVERT(VARCHAR(5),DATEPART(MM,GETDATE())) AS FLOAT)
| improve this answer | |

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.