Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1642
  • Last Modified:

SSIS Derive column for converting bigint datetimekey

Hi,

I have a issue converting Datetime field to integer using derive column.
it could be a limitation on derive column.

my Derive column expression as follows:

YEAR(ValidLastAlertDate) * 100000000 + (MONTH(ValidLastAlertDate) * 1000000) + DAY(ValidLastAlertDate) * 10000 + DATEPART("hh",ValidLastAlertDate) * 100 + DATEPART("mi",ValidLastAlertDate)


Data Types:

four-byte signed integer [DT_I4]                                                

Can anybody has the solution to this?

Thanks in advance
0
keplan
Asked:
keplan
  • 3
  • 2
  • 2
1 Solution
 
Kent DyerIT Security Analyst SeniorCommented:
0
 
Kent DyerIT Security Analyst SeniorCommented:
Here is another way..

select ValidLastAlertDate
,(CONVERT(BIGINT,YEAR(ValidLastAlertDate)) * 100000000)+(CONVERT(BIGINT,MONTH(ValidLastAlertDate)) * 1000000)+(CONVERT(BIGINT,DAY(ValidLastAlertDate)) * 10000)+DATEPART("hh",ValidLastAlertDate) * 100+DATEPART("mi",ValidLastAlertDate)
 from YOUR_TABLE (nolock) where [id]=1

Open in new window


HTH,

Kent
0
 
keplanAuthor Commented:
Hi HTH,

I need this to be achieved through SSIS.
So Is this a limitation on SSIS
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
Jason Yousef, MSSr. BI DeveloperCommented:
Hi,
what's the expected output (result) ?  not data type...
0
 
keplanAuthor Commented:
Out come is the DateTimeKey as interger value:

Eg:

201305301230
0
 
Jason Yousef, MSSr. BI DeveloperCommented:
[DT_I4]   is INT and that's small for your expected output...
cast to BIG int instead [DT_I8]
(DT_I8)YEAR(ValidLastAlertDate) * 100000000 + (MONTH(ValidLastAlertDate) * 1000000) + DAY(ValidLastAlertDate) * 10000 + DATEPART("hh",ValidLastAlertDate) * 100 + DATEPART("mi",ValidLastAlertDate)

Open in new window


Please let me know if you got an error and what's the error.
0
 
keplanAuthor Commented:
Good
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now