how to cast datetime2 as datetime

Question

I'm trying to convert datetime2 to datetime in order to create a standard between different sources using only SQL or SSIS Take the following SQL query as example:

SELECT CAST(offer_start_date AS timestamp)
FROM [ODS].[macaclient_offers]

I get the following error: 'Explicit conversion from data type datetime2 to timestamp is not allowed.'

Furthermore, I did managed to convert datetime2 into date using a simple cast.

What is the right way to convert datetime2 to datetime using SQL Server 2008 or SSIS?

gilibi

Answer 1

You are casting to timestamp in your code. Change to datetime.

SELECT CAST(offer_start_date AS datetime) FROM [ODS].[macaclient_offers]

Answer 2

Your sample select statement is trying to cast offer_start_date to timestamp not datetime.

If you do want a timestamp value from your datetime2 column you could the DatePart function to retrieve parts of the date and build it up yourself.

For example:

declare @date datetime2
set @date = GETUTCDATE()

select @date,
       DATEPART(hour, @date),
       DATEPART(minute, @date),
       DATEPART(second, @date)

MSDN reference to DatePart function.

Not sure why you're getting that error, I've not had the same issue. Example below works fine in my 2008 Management Studio.

create table #temp
(
    OrderId int,
    OrderDate datetime2
)

insert into #temp
(OrderId, OrderDate)
values
(1, GetUTCDate())

select *, CAST(OrderDate as datetime)
from #temp

drop table #temp

Answer 3

In my case the value was a varchar.

If the value is a varchar of datetime2, like '2018-10-24 12:06:29.6112233', then first cast it to a datetime2 and then to a datetime:

select cast(cast('2018-10-24 12:06:29.6112233' as datetime2) as datetime)