1

I want to convert my datetime2 column to int for each record. How do I do it without creating a variable?

Examples of my datetime2 field:

2020-03-03 10:17:26.0000000
2020-03-03 10:37:35.0000000
etc...
ZygD
  • 22,092
  • 39
  • 79
  • 102
  • 1
    (1) What integer do you want? (2) What database are you using? – Gordon Linoff Feb 24 '21 at 13:07
  • Can you show us your desired result from this two dates you have in your question ? – VBoka Feb 24 '21 at 13:09
  • Did you solve your problem? – SevC_10 Feb 26 '21 at 07:35
  • hi folks, apologies, i am using SSMS, the int i would want would be a whole number in asc order based off of the earliest timedate field. so for example, the earliest date in my query = '2020-03-03 10:37:35.0000000' i would want the int to have any value as logn as it is the lowest value, so it would be 1 or it could be 50, as long as there is no int lower than that value, –  Feb 26 '21 at 09:58
  • i had tried using SecV's approach, but i get repeating int values –  Feb 26 '21 at 09:58

1 Answers1

1

First of all, you should specify the sql variant you are using. Assuming Microsoft T-SQL, you can use the builtin function CAST.

CAST ( expression AS data_type [ ( length ) ] )

In your case, to convert from date to integer, you can simply do:

CAST ( [field to convert] AS int )

Microsoft reference documentation: https://learn.microsoft.com/it-it/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15

EDIT:

As pointed out by @HoneyBadger, the direct conversion is not allowed. Since the data doesn't have fractional seconds, you can first try to convert to datetime type, and then to int (this is allowed):

    CAST (CAST ([field to convert] AS datetime) AS int)
SevC_10
  • 266
  • 2
  • 9
  • 2
    `Explicit conversion from data type datetime2 to int is not allowed.` – HoneyBadger Feb 24 '21 at 13:27
  • thanks for your response, i have tried that but i would have repeating int values. is there a way to have the int value unique and in a asc order based off of my datetime2 field? –  Feb 26 '21 at 09:52
  • example of my line of results: 2020-03-03 10:37:35.0000000 43891, 2020-03-03 11:03:40.0000000, 43891. as you can see, two different times but they both have the same int value –  Feb 26 '21 at 09:54
  • That's right, because the int part is corresponding to the days, and the decimal part corresponds to the fraction of a day (hours, minutes, seconds ...). If you want different values for different times, you can cast to a `float`, and then you can anyway sort on that float value – SevC_10 Feb 26 '21 at 10:07