SQL DW Convert [datetime2](7) to [bigint]

Question

In SQL DataWahouse Database, I want to write an query to convert [datetime2](7) to bigint

Table :1

[StartTime] [datetime2](7) NULL

Table :2

[StartTime] [bigint] NULL


Select * FROM  Table1 INNER JOIN Table2 
ON Table1.StartTime = Table2.StartTime  -- Geting error

score 0 · Accepted Answer · answered Feb 14 '19 at 06:28

0

This won't work.

datetime2 is not an integer. The following reference discusses its internal data structure: https://sqlfascination.com/2009/10/11/what-is-the-sql-server-2008-datetime2-internal-structure/

What is the representation of your T2.StartTime? Is it milliseconds from a starting point? The following reference may help: MSSQL - Convert milliseconds since 1970 to datetime2

That said, please don't join like this, performance will be terrible. Further, the logic of a join between a datetime2 and a datetime2 created from an integer is very scarey ... if these are both supposed to be DATES, then store them as dates. Otherwise, be prepared for a very high number of missed join candidates due to the precision issues you're going to encounter.

answered Feb 14 '19 at 06:28

Ron Dunn

2,971
20
27

Ok, in this case i will use both the columns as date. – Raj Feb 14 '19 at 11:32
I want to find the difference between dates for specific hours.. say data join for same date but hours can be + or - 6 hours in difference.. – Raj Feb 14 '19 at 11:33
I want to join the values of 2 tables dates for specific minutes .. say data join for same date but hours can be + or - 10 minutes in difference.. – Raj Feb 14 '19 at 11:44

score 0 · Answer 2 · answered Feb 14 '19 at 06:45

Table1.StartTime and Table2.StartTime are different data types, so when you run your query, it gets error.

Azure SQL DataWahouse Database supports datetime2 and bigint.

You can reference this blob, it shows you convert datetime to bigint.

See the demo SQL:

update Table1 set StartTime=REPLACE(REPLACE(REPLACE (CONVERT(VARCHAR,x,20) , '-' , '' ),':',''),' ','')

Reference 2:Sql datatype conversion

Hope this helps.

SQL DW Convert [datetime2](7) to [bigint]

2 Answers2