Geek Answers Handbook

How to disable datetime string date in SQL SSIS?

I am working on a data warehouse project and would like to know how (preferably in the Derived Column component in a data stream) to separate a piece of date from a SQL datetime record.

As soon as I convert the time converted to some time, I am going to do a time search to find the related time record in the time dimension table.

Can someone give me a simple function to do this inside a derived column transform?

Example. Convert the date-time, for example, "12/02/2008 11:32:21 AM", to simply "11:32:21 AM".

+4
source share
5 answers

I would just cast to the DT_DBTIME type (using a Derived Column transform or a Convert cast). DT_DBTIME contains only (hours, minutes, seconds) a part of the date / time, so you will get rid of the part of the date.

+6
source

If you need to do this in a variable expression, Michael's solution will not work, but you can use the following expression:

 (DT_DATE)(DT_DBDATE)GETDATE()

(DT_DBDATE) converts the current date and time to date only. But the new data type is not compatible with SSIS date and time. Therefore, you will have to use (DT_DATE) to convert to a compatible type.

The courtesy of this decision belongs to Russell Loski, who posted it on his blog: http://www.bidn.com/blogs/RussLoski/ssas/1458/converting-datetime-to-date-in-ssis

+2
source

Actually, if you cancel the first 2 expressions as follows: (DT_DBDATE)(DT_DATE)GETDATE() instead of (DT_DATE)(DT_DBDATE)GETDATE() , then you TRUNCATE enter the time from the date field.

If the DT_DATE is before the DT_DBDATE , you will still have some time on your output, but it will reset for all zeros.

+2
source

In this case, with the recording of the report for the planning application, you need the time that was saved as part of the date and time data type. I formed datetime as 0, which gives you this mon dd yyyy hh: miAM (or PM), and just made a substring of what returned the time only in AM / PM format.

An example is below.

 DECLARE @S DATETIME = GETDATE() SELECT SUBSTRING(CONVERT(NVARCHAR(30), @S , 0) , 13 , 10) AS ApptTime , CONVERT(NVARCHAR(30), @S , 0) AS ApptDate
+1
source

I personally use a number of functions for this. For instance:.

 ALTER FUNCTION [dbo].[TIMEVALUE] ( @Datetime datetime ) RETURNS datetime AS BEGIN RETURN (@Datetime - CAST(ROUND(CAST(@Datetime AS float), 0, 1) AS datetime)) END

I would like to get all the loans, but he really has to go for this guy .

-2
source

More articles:


All Articles