Welcome to EMC Consulting Blogs

Interesting read! I've a follow up on using datetime within SSIS expressions. Carefully consider your approach when converting from Datetime to String: Option 1
(DT_STR, 4, 1252) DATEPART("yyyy", @[System::StartTime]) + "-" +
(DT_STR, 4, 1252) DATEPART("mm", @[System::StartTime]) + "-" +
(DT_STR, 4, 1252) DATEPART("dd", @[System::StartTime]) + " " +
(DT_STR, 4, 1252) DATEPART("hh", @[System::StartTime]) + ":" +
(DT_STR, 4, 1252) DATEPART("mi", @[System::StartTime]) + ":" +
(DT_STR, 4, 1252) DATEPART("ss", @[System::StartTime])

Option 2
SUBSTRING((DT_STR, 30, 1252)GETDATE(), 1, 19 )

Option 3
SUBSTRING((DT_STR, 30, 1252)@[System::StartTime], 1, 19 )


Option 1 returns 2006-1-5 9:6:5
Option 2 returns 2006-01-05 09:06:05
Option 3 returns 1/5/2006 9:06:05 AM

You can just change the date format will do:

SET DATEFORMAT dmy;

declare @date datetime

set @date =  '12/03/2005'

print @date

http://msdn2.microsoft.com/en-us/library/ms189491.aspx

Oh...sorry guys, seems like it doesn't work in SSIS.

um you guys may have worked this out already as this is a pretty old thread.

my solution was to pass in the dates as 'date' variables under 'Parameter mapping' and then use the '?' parameter in your SqlStatement Expression.

simple example:

"SELECT <somecolumnname> from <sometable>

WHERE (MyDate >= ?)"

Hope this makes sense

Additionally if you're tempted to use this generated expression as a filename you have to replace "/" & ":" with another character as neither are permitted in the name of a file.  Using Joe's Option 2 as a starting point you may want to try this:

REPLACE(SUBSTRING((DT_STR, 30, 1252)GETDATE(), 1, 19 ),":","-")

trying to build a filename from todays date and can't figure out how to change GETDATE() to a string 'YYYYMMDD'

It can't be this hard..........

thx

joeb

Oh yeah,

this is in expression builder. Want the filename to be

'YYYYMMDD_835.txt'

for the destination property in a file system task

thx

joeb

Just had some fun with this when implementing custom logging. Basically I'm constructing an XML string and including in there both @[System::StartTime] and GETDATE() and I get the following:

   <StartTime>11/07/2008 18:06:45</StartTime>

   <EndTime>2008-07-11 18:06:53.452000000</EndTime>

As both are of different types i.e. one is a DB datetime and the other is a .NET DateTime, I thought I should first normalise them to the same type so they convert consistently. So I added another type conversion to my expression e.g.

(DT_WSTR, 50) (DT_DBTIMESTAMP) @[System::StartTime]

(DT_WSTR, 50) (DT_DBTIMESTAMP) GETDATE()

* I tend to use DT_WSTR as I don't like explicitly defining the codepage

Now I get:

   <StartTime>2008-07-11 18:06:45</StartTime>

   <EndTime>2008-07-11 18:06:53.452000000</EndTime>

As I'm not interested too much in the milliseconds I use SUBSTRING(,1,19) and hey presto, they look the same:

   <StartTime>2008-07-11 18:06:45</StartTime>

   <EndTime>2008-07-11 18:06:53</EndTime>

Hope this helps.

Adam Tappis.

Found this on my quest for the perfect formatting, and similar to Adam's formatting, I have found that for my use, the following works best for my email notifications

using the following casts...

(DT_WSTR,30)(DT_DATE)@[System::StartTime]  

(DT_WSTR, 30) (DT_DATE) GETDATE()

generates something like...

Start Time: 8/13/2008 11:14:06 AM

End Time: 8/13/2008 11:14:07 AM

which ends up being very easy to read.

I have a problem with my SSIS package.

I am trying to convert a unicode String (sometimes empty or null) to Datetime while importing an excel file into my SQL server database.

I tried derived column and date conversion transformations but in vain.

Any help is appreciated. Thanks in advance.

Sri

hi,

i find the validation of dates in SSIS to be very poor.

so i am trying to find a way validating dates in a script task but i got no success.

maybe one you the gurus here could try and see where the error lies.

regards

Nicolas

===================================================

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Imports System.Text.RegularExpressions

Public Class ScriptMain

   Inherits UserComponent

   Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

       validateStr2DateIsoFromEE(Row.myDt, _

       Row.dtValidated, Row.dtValidated_IsNull)

   End Sub

   Sub validateStr2DateIsoFromEE( ByVal aStr As String, _

       ByRef outDate As Date, ByRef outDate_isNull As Boolean )

       Dim provider As IFormatProvider

       Dim dt As Date

       Dim pattern As String = "\b(\d{8})\b"

       Dim rgx As Regex = New Regex(pattern)

       Dim m As Match = rgx.Match(aStr)

       If m.Success Then

           If Date.TryParseExact(aStr, "yyyyMMdd", Nothing, Globalization.DateTimeStyles.None, dt) Then

               outMsg = "valid date"

               outDate = dt

           Else

               outMsg = "only numbers - but not valid date"

               outDate_isNull = True

           End If

       Else

           outMsg = "other chars besides numbers; or a length different then 8 chars"

           outDate_isNull = True

       End If

msgbox("the validation was: "+ outMsg )

   End Sub

End Class

===================================================