Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SSIS Derive column for converting  bigint datetimekey

Posted on 2013-06-11
7
Medium Priority
?
1,581 Views
Last Modified: 2016-02-11
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
Comment
Question by:keplan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 17

Expert Comment

by:Kent Dyer
ID: 39239974
0
 
LVL 17

Expert Comment

by:Kent Dyer
ID: 39239985
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
 

Author Comment

by:keplan
ID: 39240803
Hi HTH,

I need this to be achieved through SSIS.
So Is this a limitation on SSIS
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 39240831
Hi,
what's the expected output (result) ?  not data type...
0
 

Author Comment

by:keplan
ID: 39243017
Out come is the DateTimeKey as interger value:

Eg:

201305301230
0
 
LVL 21

Accepted Solution

by:
Jason Yousef, MS earned 1480 total points
ID: 39248340
[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
 

Author Closing Comment

by:keplan
ID: 39249499
Good
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

My client has a dictionary table. They're defining a list of standard naming convention. Now, they are requiring my team to provide us a mechanism how to match new incoming data with existing data in their system.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question