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

  • 1
    Compare in what way? What are your expected results? Do you have any code written so far? – Erik Darling Sep 21 '17 at 12:56
  • I have to compare if year and month in column EventID is equal to the year and month in column EventStartDate.I have not written any code so far-@sp_BlitzErik – l.lijith Sep 21 '17 at 13:00
  • 2
    Okay, great, edit your question to add that, and show us what results you expect from the sample data. – Erik Darling Sep 21 '17 at 13:01
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           |
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);
  • 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)

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.