Geek asks and answers

How to use datetime2 as datetime

I am trying to convert datetime2 to datetime to create a standard between different sources using only SQL or SSIS. Take the following SQL query as an example:

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

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

In addition, I was able to convert datetime2 to a date using a simple listing.

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

gilibi

+5
source share
3 answers

OK, I was able to do this using the SSIS data transformation component. I found out that I can use DT_DBTIME2 or DT_DBTIME

Thanks to everyone who helped

0
source

timestamp . datetime.

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

The statement uses the throw_start_date function for timestamp to select a sample, not a datetime.

If you need the timestamp value from the datetime2 column, you can use the DatePart function to extract the parts of the date and create it yourself.

For instance:

declare @date datetime2
set @date = GETUTCDATE()

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

MSDN reference to the DatePart function .

Not sure why you are getting this error, I did not have the same problem. The example below works great in my Management Studio 2008.

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
source

More articles:


All Articles