February 22, 2008 at 1:27 pm
hi i jus had a column name date but the data are like
date
100083
100080
100097
so now i jus need to convert this to date format in this the logic is the first three digits plus 1900 is the yr
like 100+1900 so the yr is 2000 now
and the last three difgits 083 is the day of the yr may be like 083 as march 23 or smthng like that so even ineed to calculate according to the leap yr too plz could u suggest how can i convert this value to date in ssis plz help me out urgent......
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
February 22, 2008 at 4:46 pm
This should work.
select dateadd(day, cast(right(100083,3) as integer)-1,'01/01/' + cast(left(100083,3)+1900 as varchar(4)))
February 23, 2008 at 12:14 pm
WHERE SHOULD I JUS APPLY THESE ...I M NEW TO THIS SO WHERE SHOULD I WRITE THIS ......I HAVE SOURCE IN WHICH THE COLUMN IS DATA IN THAT FORMAT SO WHAT IS THE TRANSFROMATION WHICH I SHOULD USE .....PLZ LET ME KNOW ITS URGENT THKZ IN ADVANCE....
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
February 25, 2008 at 12:28 am
Create a column which has old and new data for the date . First import the data in to an old column and then update the new column with the old column along with Date format.
February 25, 2008 at 8:33 am
i m getting eror when i m usinf in expression can i use this in expression od derived column ....
i get the expression is incomplete or invalid token or incomplete elemnet..it might not be well formed or might me missing part of required elemnnt such as paranthesis....
my expression is
select dateadd(day, cast(right( 'Invoice Date',3) as integer)-1,'01/01/' + cast(left( 'Invoice Date ',3)+1900 as varchar(4)))
my date column name is incoce date so i used that plz do reply urgenttttttt
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
February 25, 2008 at 8:40 am
Take out the quotes in your column name. If there is a space between Invoice and Date use brackets. Try this:
select dateadd(day, cast(right([Invoice Date],3) as integer)-1,'01/01/' + cast(left([Invoice Date],3)+1900 as varchar(4)))
February 25, 2008 at 8:51 am
i tried that but it shows me the same error can i use the select statement in expression or do i jus need to start from dateadd and sometime it shows me the error could not find 'day' in the input column ...plz help me out urgent
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
February 25, 2008 at 8:54 am
i have an other error too it says expression is not valid ir out of memory error what does that mean
failed to set property expression on input column invoice date
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
February 25, 2008 at 9:05 am
It help out greatly if you gave us your process and what you're trying to accomplish. Can you also tell us what tool you are using to transform the data. The code I provided earlier is something that you would use in a select statement pulling data from a table. From the error messages, it sounds like you are trying to use the statement in side of an evaluation expression editor with SSIS.
select dateadd(day, cast(right([Invoice Date],3) as integer)-1,'01/01/' +
cast(left([Invoice Date],3)+
1900 as varchar(4)))
from myTable
When you get the error what application is showing the error. Is is SQL Server Management Studio? What do you mean by expression?
February 25, 2008 at 9:16 am
hello im importing data from excel to sql server in which i m using ssis so to manipulate the column named invoce date with that i need to make changes so i addded derived column to source in which we have expression in which i need to give the exp which gives me this error ......
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
February 25, 2008 at 9:21 am
I see. My personal opinion is to load the data is its raw format on the DB and using SQL to do any transformations. I would suggest that you load the data is it appears on to SQL Server on to a staging table and once it's there use the SQL I provided earlier to load it onto your production database.
February 25, 2008 at 9:28 am
yeah this sounds good i could load the data as it is as i dnt have any problem in dng that but how should i change all the column value of that '100083' to 2000-03-23 how could i do that to update the column what exact syntax and statement i need to use....plz thkz in advance
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
February 25, 2008 at 9:40 am
i m new to this so i dnt know much abt all this stuff what is staging table how should i mention that i have imported directly into database is that not which i should do plz let me know.......
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
February 25, 2008 at 9:59 am
Import the "date" fields (i.e. 100083) as an integer. The following SQL code when run in SSMS will convert the value to a date. You can incorporate this in a SQL task in SSIS or you may need to wait a little longer for someone to provide a similiar function for use in an expression in a transformation task.
This is code I used to test my idea:
declare @inDate int
set @inDate = 100083
select @inDate, dateadd(yy, @inDte/1000, 0) + (@inDate - ((@inDate/1000) * 1000) - 1)
hth
😎
February 25, 2008 at 10:06 am
let me make it clear i had imported the data into sql server table 'nydrive' which has the invoice date as 100083 format now how should i convert all the rows in this column to date ...i jus need to update the value of 100083 to 2000-03-23 fromat how can i do that if would be gr8 if u help me dng that ...i didnt understand what u said where should i use sql task and whre should i enter the script u gave me thkz in adavance.....
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply