Convert Date

Hi !
I think I need your help... to convert the date (2006-09-09) to weekday, weeknumber, month number, month, year in OLE DB source editor ... created following sql, which is not working.
How about using derived Column transformation editor?

SELECT DATENAME (WEEKDAY, YYYYMMDD) AS weekday, DATEPART(WEEK,YYYYMMDD) AS weeknumber, MONTH(YYYYMMDD) AS month_number, DATENAME(MM,YYYYMMDD)AS Month, YEAR(YYYYMMDD) AS
year, DAY(YYYYMMDD) AS date FROM Purchase

thank you so much...

curiousss wrote:

Hi !
I think I need your help... to convert the date (2006-09-09) to weekday, weeknumber, month number, month, year in OLE DB source editor ... created following sql, which is not working.
How about using derived Column transformation editor?

SELECT DATENAME (WEEKDAY, YYYYMMDD) AS weekday, DATEPART(WEEK,YYYYMMDD) AS weeknumber, MONTH(YYYYMMDD) AS month_number, DATENAME(MM,YYYYMMDD)AS Month, YEAR(YYYYMMDD) AS
year, DAY(YYYYMMDD) AS date FROM Purchase

thank you so much...

YYYYMMDD isn't a date data type, which is a requirement for your date functions.|||

Phil is absolutely right.

In situation like these it might help to check the error message. And if you post something on this forum, post the error message as well.

-Jamie

|||Thank you! I only change the YYYYMMDD to date as follows
SELECT DISTINCT DATENAME (WEEKDAY, date) AS weekday... etc etc

and everything work fine until the error in Execution of task... OLE DB Destination is red.

[OLE DB Destination [608]] Error: An OLE DB error has occurred. Error code: 0x80040E2F. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F Description: "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F Description: "Cannot insert the value NULL into column 'weekday', table 'ElektronicsDW.dbo.Dim_Date'; column does not allow nulls. INSERT fails.". [OLE DB Destination [608]] Error: The "input "OLE DB Destination Input" (621)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (621)" specifies failure on error. An error occurred on the specified object of the specified component. [DTS.Pipeline] Error: The ProcessInput method on component "OLE DB Destination" (608) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. [DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0209029.|||

curiousss wrote:

Thank you! I only change the YYYYMMDD to date as follows
SELECT DISTINCT DATENAME (WEEKDAY, date) AS weekday... etc etc

and everything work fine until the error in Execution of task... OLE DB Destination is red.

[OLE DB Destination [608]] Error: An OLE DB error has occurred. Error code: 0x80040E2F. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F Description: "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F Description: "Cannot insert the value NULL into column 'weekday', table 'ElektronicsDW.dbo.Dim_Date'; column does not allow nulls. INSERT fails.". [OLE DB Destination [608]] Error: The "input "OLE DB Destination Input" (621)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (621)" specifies failure on error. An error occurred on the specified object of the specified component. [DTS.Pipeline] Error: The ProcessInput method on component "OLE DB Destination" (608) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. [DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0209029.

Is "date" a datetime field? Sounds like DATENAME is returning a NULL.|||Double chek the destination component to make sure the mappings are fine. And perhaps place a data viewer in the dataflow to actually see the value as the flow toward the destination...|||Thank you, I have a Derived column due to the (weekday unicode problem) and added following expression in derived column (ISNULL(weekday) ? "" : (weekday))

Moreover Destination Mapping Input column ignore and weekday in Destination column. Is this Ok?

Still error: [OLE DB Destination [608]] Error: An OLE DB error has occurred. Error code: 0x80040E2F. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F Description: "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F Description: "Cannot insert the value NULL into column 'weekday', table 'ElektronicsDW.dbo.Dim_Datum'; column does not allow nulls. INSERT fails.".
[OLE DB Destination [608]] Error: The "input "OLE DB Destination Input" (621)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (621)" specifies failure on error. An error occurred on the specified object of the specified component.
|||The error is clear, that column does not accept nulls. Either change the column constarint to accept nulls or change the expression to moved a default value when weekday is null.|||Thank you so much! solved!

How about dates... This command "convert(char(8),date,112)" converts to YYYYMMDD.

Still wondering how to convert date to format YYYYMM(200612)? and YYYYwk (year+week as "20073")?|||

curiousss wrote:

Thank you so much! solved!

How about dates... This command "convert(char(8),date,112)" converts to YYYYMMDD.

Still wondering how to convert date to format YYYYMM(200612)? and YYYYwk (year+week as "20073")?

To do what you want, you'll need to use datepart functions... All of the functions you need are in the derived column transformation under the Date section. You'll need to concatenate a few pieces to get what you want.|||it seems very easy, but just don't get it right.
using the columns 'year' and 'month', 'week' the function looks like this in derived transformation ...
DATEPART( [year] [month] , date) or DATEPART( [year] [week] , date)
but its red?|||

Did you look at the syntax of the DATEPART function? Obviously not.

DATEPART (SSIS)
(http://msdn2.microsoft.com/en-us/library/ms137586.aspx)

In your case you can probably achieve this with:

(DT_STR, 4, 1252)DATEPART(yyyy, [YourDateColumn]) + (DT_STR, 2, 1252)DATEPART(mm, [YourDateColumn]) + (DT_STR, 2, 1252)DATEPART(yydd, [YourDateColumn])

-Jamie

|||Actually this would work... returning 20072. I would prefer though 200702. Possible?

cast(datepart(year, datum) as varchar(4))+ cast(datepart(month, datum) as varchar(2))) as month,

This is also a mystery... returming without space BillGates.. how?
(ISNULL([name]) ? " " : [name]) + " " + (ISNULL(Lastname) ? " " : Lastname)|||Throw this around your month:

right('0' + (dt_str,2,1252)datepart('mm',[datefield]),2)|||CONVERT(varchar(6), DATE, 112) will give you YYYYMM