How to Strip the Date Off of a Datetime String in SQL Ssis

How do I strip the date off of a datetime string in SQL SSIS?

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

How to remove from DateTime variable hours, minutes, seconds and other parts in SSIS

You can achieve that using a derived column:

(DT_DBTIMESTAMP)(DT_DBDATE)@[User::DateTimeVariable]

Casting to DT_DBDATE will remove the time part, then recasting to DT_DBTIMESTAMP will re-add a time part but with 12:00 AM value = 00:00:00

Example:

Sample Image

Removing time from datetime in SQL Server

If just want store date part try altering that column data type and make it as just date

ALTER TABLE dbo.Time ALTER COLUMN PK_Date Date

DateTime Conversion to Date

Stripping the time was discussed here. Could you possibly use a similar method?

Also...

Refer BOL --> Convert

print convert(varchar(10),getdate(),101)

print convert(varchar(10),DateTimeCol,101)

courtesy of Srinika

SSIS expression: convert date to string

For SSIS you could go with:

RIGHT("0" + (DT_STR, 2, 1252) DATEPART("dd" , GETDATE()), 2) + "-" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("mm" , GETDATE()), 2) + "-" +  (DT_STR, 4, 1252) DATEPART("yy" , GETDATE())

Expression builder screen:

Expression builder screen

A way to extract from a DateTime value data without seconds

For a solution that truncates using strings try this:

SELECT CAST(CONVERT(CHAR(16), GetDate(),20) AS datetime)

CHAR(16) works only if our variable is converted to ODBC canonical format, as shown above by using 20 as the format specifier.

DECLARE @date DateTime = '2011 Nov 22 12:14:55';
SELECT CONVERT(Char(16), @date ,20) AS datetime

Results:

| datetime         |
|------------------|
| 2011-11-22 12:14 |

Then you simply cast back to a DateTime type to continue using the value.

NOTE: This is only viable for data types that do not carry TimeZone info.
Also type conversions to VarChar and back are usually LESS performant than using DateTime functions that use numeric operations internally.

Consider other solutions posted if performance is a concern or if you must retain timezone information.

T-SQL SSIS Export DateTime type to Date Time

It appears you need dates and times exported in a particular format. While you're coercing them to the correct representation in your select statement, it is crucial that the metadata the pipeline receives is that the data is typed as a string DT_STR/DT_WSTR.

If the first time those columns were presented to the pipeline they were a date or time type, then SSIS may have kept the metadata and is implicitly converting back to a datetime type. Double clicking the pipeline between the source and destination will show what the types are.

The same holds true for your Flat File Format connection manager. Here, you want to have the date and time columns specified as string because while it contains date and time data, you have a specific format in mind so leave it as a string of the correct length (and unicode-ness).

SSIS change datetime variable to other format

The safest bet is to convert your DateTime variable into the ISO date format:

"'" + (DT_WSTR,4)YEAR(@[User::InputDateV]) 
+ RIGHT("0" + (DT_WSTR,2)MONTH(@[User::InputDateV]),2)
+ RIGHT("0" + (DT_WSTR,2)DAY(@[User::InputDateV]),2)
+ "'"

This evaluates to '20121024' and as such is a perfect, unambiguous date string for SQL Server.

SQL Server remove milliseconds from datetime

You just have to figure out the millisecond part of the date and subtract it out before comparison, like this:

select * 
from table
where DATEADD(ms, -DATEPART(ms, date), date) > '2010-07-20 03:21:52'


Related Topics



Leave a reply



Submit