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

Have you read http://stackoverflow.com/questions/1331779/c-sharp-conversion-of-a-datetime2-data-type-to-a-datetime-data-type? — Andrius Naruševičius, Jun 18 '12 at 11:12
You've edited the query, but obviously the error message is no longer correct - do you still *have* an error? — Damien_The_Unbeliever, Jun 18 '12 at 12:25

score 11 · Answer 1 · answered Jun 18 '12 at 11:25

11

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

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

answered Jun 18 '12 at 11:25

Mikael Eriksson

136,425
22
210
281

I copied the wrong query. it should be the same as you suggested but this doesn't work – gilibi Jun 18 '12 at 11:33
Strange. It works just fine in my tests. [Have a look here](http://sqlfiddle.com/#!3/e34ce/2). – Mikael Eriksson Jun 18 '12 at 12:10
What error do you get when you execute the query above?. The error message in your question refers to `timestamp`. – Mikael Eriksson Jun 18 '12 at 12:11

Mike · Answer 2 · 2012-06-18T12:20:32.387

3

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

edited Jun 18 '12 at 12:20

answered Jun 18 '12 at 11:24

Mike

661
5
10

+1 For more information about the infamous timestamp datatype, [see MSDN](http://msdn.microsoft.com/en-us/library/ms182776(v=sql.90).aspx) – Andomar Jun 18 '12 at 11:25
I'm sorry, I copied the wrong example. i tried: SELECT CAST(offer_start_date AS datetime) FROM [ODS].[macaclient_offers] – gilibi Jun 18 '12 at 11:31

score 2 · Answer 3 · answered Oct 24 '18 at 17:00

2

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)

answered Oct 24 '18 at 17:00

CodingYoshi

25,467
4
62
64

score 1 · Accepted Answer · answered Jun 18 '12 at 12:30

1

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

answered Jun 18 '12 at 12:30

gilibi

343
2
9
18

Jaime Raul Chavez Sierra · Answer 5 · 2019-02-07T22:43:14.130

0

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

edited Feb 07 '19 at 22:43

answered Feb 07 '19 at 22:36

Jaime Raul Chavez Sierra

11
1

how to cast datetime2 as datetime

5 Answers5

Linked