convert datetime field to int in sql

Question

my date column value looks like:

2023-04-23 00:00:00
2023-04-23 00:50:00
''
NULL

I want to convert it to

20230423000000
20230423005000
''
NULL

When I used

Select FORMAT (DAT,'yyyyMMddHHmmss' from table give error as
Argument datatype nvarchar is invalid for argument 1 of format function.

Does this answer your question? [How to convert DateTime to a number in MySQL?](https://stackoverflow.com/questions/1921574/how-to-convert-datetime-to-a-number-in-mysql), oops it's a duplicate of https://stackoverflow.com/questions/38804214/convert-yyyy-mm-dd-to-yyyymmdd-in-mysql, not on the other link..... — Luuk, Apr 25 '23 at 11:52
Why are people voting to close as duplicate of a MySQL question? — Martin Smith, Apr 25 '23 at 11:56
Regarding the issue here `DAT` is clearly a string data type. So you can just `replace` the dashes, spaces, and colons with empty string to get the desired result. But really you should fix the datatype and not store date times as strings — Martin Smith, Apr 25 '23 at 11:59
While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). — Yitzhak Khabinsky, Apr 25 '23 at 12:03
@MartinSmith: Because of the poor formatting of the "I used" section, I must have overlooked the correct database. But, when I did note the correct database, it's still a duplicate of: https://stackoverflow.com/questions/23133860/how-to-convert-datetime-value-to-yyyymmddhhmmss-in-sql-server — Luuk, Apr 25 '23 at 13:21
Not really because this is basically what they are already trying. The issue they are having here is that the column is a string datatype — Martin Smith, Apr 25 '23 at 13:41
Basically the lack of interest to READ, and try to understand, an error message,... — Luuk, Apr 25 '23 at 13:56

score 1 · Answer 1 · answered Apr 25 '23 at 12:01

From the error message, your column is of a string-like datatype, not datetime-like. If so, you probably want to use string functions:

select 
    concat(
        substring(dat, 1, 4), 
        substring(dat, 6, 2),
        substring(dat, 9, 2),
        substring(dat, 12, 2),
        substring(dat, 15, 2),
        substring(dat, 18, 2)
    ) as new_dat
from mytable

Or using replace() multiple times:

select replace(replace(replace(dat, ' ', ''); ':' ''), '-', '') as new_dat
from mytable

score 1 · Accepted Answer · answered Apr 25 '23 at 12:12

You couldn't use FORMAT because datatype filed date is nvarchar

first you must cast with datetime and you must check empty and null field dates

Select case when dates='' or dates is null then ''
else FORMAT (try_cast( dates as datetime),'yyyyMMddHHmmss') end from Da

You can insert Base data with the following statements:


drop table Da
create table Da(dates nvarchar(100)  )
insert into Da (dates) values('2023-04-23 00:00:00')
insert into Da (dates) values('2023-04-23 00:50:00')
insert into Da (dates) values('')
insert into Da (dates) values(null)

score -1 · Answer 3 · edited Apr 25 '23 at 16:35

The integer value returned in this conversion refers to the number of days since Jan 01, 1900. In your second query, the date you have entered is stored as a varchar, thus the conversion error you're seeing. You can cast or convert this to a datetime and the query will succeed.

Ex:

SELECT CONVERT(INT, CONVERT(DATETIME,'2013-08-05 09:23:30'))
SELECT CONVERT(INT, CAST ('2013-08-05 09:23:30' as DATETIME))

convert datetime field to int in sql

3 Answers3