0

Here is my question: This is a table named HB06,the data type of "WRTime" is datatime.I want to convert all WRTime to int. For example 2012-11-09 10:52:38.000 will be converted to 20121109105238.

Image

Thank you!

Nisarg Shah
  • 14,151
  • 6
  • 34
  • 55
Haohao Qi
  • 3
  • 1
  • 3
  • 1
    Possible duplicate of [How to convert DateTime to VarChar](https://stackoverflow.com/questions/74385/how-to-convert-datetime-to-varchar) – Nisarg Shah Sep 06 '18 at 07:18
  • The question I have linked to shows how to convert the date to varchar. You can then cast the varchar to int, if required. – Nisarg Shah Sep 06 '18 at 07:19
  • 1
    If you're going to convert it to an integer, it would need to be `bigint` not `int` since `int`s can only be up to 10 digits long. But really, why are you changing the data type at all? – Damien_The_Unbeliever Sep 06 '18 at 07:26
  • why do you want to do this? I'm not sure if you will save any space, and the integer will not be suitable for easy date arithmetic. – Cato Sep 06 '18 at 09:00

5 Answers5

2

You can't. The value is too large for the int data type. But it does fit into a bigint.

What you can do is to convert it to a string with the desired format, and then cast that ti the bigint type. Using the FORMAT function is IMO more straight forward:

DECLARE @a datetime = '20120304 23:34:12'
SELECT @a
SELECT CAST(FORMAT(@a, 'yyyyMMddhhmmss') AS bigint)

The alternative is to use CONVERT function, which uses less CPU. But there's not direct style that matches that format, so you would then REPLACE() various "litter" characters with nothing. I wouldn't bother with the CONVERT() option unless you work over large data sets.

Tibor Karaszi
  • 379
  • 1
  • 7
  • This will change the hours to a 12 hour format and remove milliseconds. Use this: `CAST(FORMAT(@a, 'yyyyMMddHHmmssfff') AS bigint)` – Aaron Dietz Sep 06 '18 at 14:27
1

You can try with below one

select concat(convert(varchar,WRTime,112),datepart(HH,WRTime),
datepart(MINUTE,WRTime),datepart(SS,WRTime)) from HB06
Tharunkumar Reddy
  • 2,773
  • 18
  • 32
1
select  
cast(replace(replace(replace(convert(varchar(19), WRTime, 121),':',''),'-',''),' ','') as bigint)
FROM HB06
Yuriy Tsarkov
  • 2,461
  • 2
  • 14
  • 28
  • 1
    @HaohaoQi Then you should mark the answer as accepted answer so that others see it worked for you! – MBT Sep 06 '18 at 08:41
0

Use below Convert function:

SELECT CONVERT(VARCHAR(100),WRTime,112)+REPLACE(CONVERT(VARCHAR(100),WRTime,108),':','')
FROM HB06
Prahalad Gaggar
  • 11,389
  • 16
  • 53
  • 71
0

SQL Version 2012 or higher you can use the FORMAT function to get just year and month, then cast it as an int.

On versions prior to 2012 you can do the formatting with the convert function, then cast as int.

declare @WRTime datetime
set @WRTime = '2012-11-09 10:52:38.000'

select cast(format(@WRTime,'yyyyMM') as int) --2012 or higher

OR You can use: 

SELECT YEAR(@WRTime)*100 + MONTH(@WRTime);

2012-11-09 10:52:38.000 will be converted to 20121109105238??

Int can't convert this so use "BIGINT"

declare @WRTime datetime
set @WRTime = '2012-11-09 10:52:38.000'

select cast(format(@WRTime,'yyyyMMddHHmmssfff') as bigint) --2012 or higher
CR241
  • 2,293
  • 1
  • 12
  • 30