how to cast datetime2 as datetime

7
votes

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

5
sqldatetimessis
Have you read stackoverflow.com/questions/1331779/…? – Andrius Naruševičius
I have, but I can't use C# in the case – gilibi
You've edited the query, but obviously the error message is no longer correct - do you still have an error? – Damien_The_Unbeliever

5 Answers

8
votes

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

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

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
2
votes

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)
1
votes

OK, I managed to accomplish that using a SSIS data conversion component. I found out that I can use either DT_DBTIME2 or DT_DBTIME

Thanks for everyone who helped

0
votes

convert(datetime, convert(varchar(4),datepart(year,table_field ))+'-'+ convert(varchar(2),datepart(month,table_field ))+'-'+ convert(varchar(2),datepart(dd,table_field )) ) when table_field is datetime2 type