how to convert datetime to int datatype

Question

0.00/5 (No votes)

See more:

SQL-server-2000

SQL-Server-2005

, +

I am getting this error while converting to int datatype

I am using query this to convert

CAST(CONVERT(VARCHAR(8),GETDATE(),112)AS INT)

but getting this error .. ?

Error:\'Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.'

Posted 19-Jan-14 21:38pm

ankush1989

Add a Solution

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sergey Vaselenko · Answer 1 · 2014-01-19T23:51:00

Solution 2

Use DATEDIFF function like this:

SQL

SELECT CAST(41657 AS datetime)
SELECT DATEDIFF(DAY, 0, CAST(41657 AS datetime))

Posted 19-Jan-14 23:51pm

Sergey Vaselenko

Comments

Christian Graus 20-Jan-14 22:55pm

OK, that makes zero sense. You're not using today's date anywhere in there. Nor do you end up with yyyymmdd as the final result.

Sergey Vaselenko 21-Jan-14 5:35am

The first line shows how to convert an integer value to a datetime value.
Further the datetime value can be converted to a varchar, for example.

The second line shows how to convert a datetime value to an integer one.
The function result is 41657 that confirms that conversions work in both ways correctly.

Christian Graus 21-Jan-14 13:35pm

But if you read my answer, the code he posted was correct, and working, and it turned a datetime in to an int in the format yyyymmdd. Yours turns a date time in to an int that, to a human reader, is arbitrary

Sergey Vaselenko 21-Jan-14 15:17pm

You are right. DATEDIFF is just another solution.

Christian Graus · Answer 2 · 2014-01-19T21:41:00

You are converting a date to a string, then trying to cast to int. That makes zero sense.

convert(int, getdate())

will do it, if it's possible.

What do you expect to get ? How do you expect the int to relate back to the date ? Are you sure you don't want the day, month or year functions ?

select convert(int, getdate()) returns 41658 for me, every time, so it seems to ignore the time, however it's working.

funny enough, select CAST(CONVERT(VARCHAR(8),GETDATE(),112)AS INT) works in SS2008.

Actually select CONVERT(VARCHAR(8),GETDATE(),112) returns yyyymmdd, so in fact that does make sense, although I'm not sure why you want it as an int.

so, it does work. You marked your question as four versions of SQL Server. I can tell you it works in SS2008 R2. Try selecting just the string part and see if that works.

how to convert datetime to int datatype

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

how to convert datetime to int datatype

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us