Cant Do View Date
Nov 23, 2006
hi am am very new to sql and im finding it really hard to do parts of my assignment.
some of the questions i am struggeling on are
Which employees were hired in March?
Which employees were hired on a Tuesday?
Show details of employee hiredates and the date of their first payday.
(Paydays occur on the last Friday of each month) (plus their names)
Refine your answer to 7 such that it works even if an employee is hired after the last Friday of the month (cf Martin)
can anyone help?
View 19 Replies
ADVERTISEMENT
Jul 29, 2015
My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax).In other words, I want to see
Select
Jan 1- 23rd
feb 1-23rd
march 1-23rd
april 1-23rd
,value
from
table
View 9 Replies
View Related
Aug 29, 2006
I want to add custom view so that it show the records that their dateis less than a specific field like "2007/12/25". Dates are saved in DB like "2006/08/29 12:00:00 A.M" nad smalldatetime format in MSSQL.what should I do?
View 4 Replies
View Related
Aug 10, 2006
plese help
I want to view date dimentions like DD/MM/YY, doen't want to include time.
Thank you
View 1 Replies
View Related
Jun 6, 2008
quote:Run the view for orders shipped from January 1, 2003 and June 30, 2003, formatting the shipped date as MON DD YYYY.
creating the view:
CREATE VIEW vw_orders
AS
SELECT
o.order_id,
c.customer_id,
c.name AS 'customer_name',
c.city,
c.country,
o.shipped_date
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
running the view:
SELECT *
FROM vw_orders
WHERE shipped_date between '01/01/2003' and '06/30/2003'
ORDER BY 'customer_name', 'country'
I tried:
in creating view,
shipped_date = CONVERT (char(12) , o.shipped_date, 107 )
then run a view as result:
quote:order_id customer_id customer_name city country shipped_date
---------- ---------------------------------------------------------- ---------------- -------------------- ------------
what should i do?
View 7 Replies
View Related
Oct 1, 2007
I am trying to incorporate a few columns into a view that each shows a certain value based on a logged datetime specific to the value. Unfortunately, the logtimes are accurate down to milliseconds, and each value has it's own logtime. (They're suppsoed to log at midnight, but sometimes log a few seconds early or late).
I want to be able to round up to 00:00:00 if it's 23:59:59 and down to the same time if it's 00:00:01. I can't very well just drop the time component because if a device logged at 00:00:01 on Aug 4 for the Aug 3rd average, and 23:59:59 for the Aug. 4rd data, then I'd have two Aug 4th values and zero Aug. 3rd values.
Additionally, I need to keep this in a datetime format for reporting purposed in Crystal Reports.
Am I asking too much?
View 18 Replies
View Related
Jul 20, 2005
How to get the date a view was last modified? (As opposed to created)
View 2 Replies
View Related
May 2, 2008
Hi
i have created 1 view and in my view i have datetime column adn i have converted it with my custom format by mere convert(column_name,6) and i am done then after when i tried to compare it with my textbox it is taking as string and when i tried to convert my date format to original format it is not allowing me so i have to put 2 duplicate column in my view i am sure there muct be some thing whcih i am not aware
First i have donbe this--> select convert(varchar,columnname,6)-- > 02 May 08
then try to compare it as date with View --> select * from ?<some table> where column_name='TEXT_BOX not desired output
select * from ?<some table> where column_name which in base table='TEXT_BOX i am done
please give me some ideas
Thanks
Parth
View 1 Replies
View Related
May 24, 2007
I have a SQL Server View. The problem is that the DateTime field has many Null values which is causing a problem with my parsing of the data in MSAccess.
How would I use CAST (or CONVERT) to handle Null Date values in my SQL Server view?
I remember there was a way to use CAST or CONVERT similar to the nz type function in MSAccess to handle null date values but I can't remember the syntax. What is happening now is that I get a data mismatch in my MSAccess function when it hits a Null Date value. Can I somehow use the ISNULL or ISDate function? I believe I need to somehow return "" instead of Null.
View 6 Replies
View Related
Mar 19, 2007
I am using BETWEEN '02/01/2007' AND '2/01/2006' in the criteria of a VIEW and have tried <= '02/01/2007' AND >='2/01/2006' but both are not filtering the dates correctly. Is there another way? What am I missing?
View 4 Replies
View Related
Feb 28, 2008
I need to control DOF (date of order) which data type is datetime for today's date.
I use 1) or 2) but got null.
1) = getdate(),
2) = DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
I use
between '2/28/2008' and '2/28/2008'
will get result.
How to get today's date?
View 7 Replies
View Related
Sep 20, 2006
i have created a view to a table (90,000+ records) that i want to use to filter the table by the current year and month. the code looks like this:
CREATE VIEW [billy.bhuj].[bo current month]
AS SELECT [dbo].[bo].[recnum], [dbo].[bo].[queue],
[dbo].[bo].[queue_name], [dbo].[bo].[node],
[dbo].[bo].[interval], [dbo].[bo].[tot_calls],
[dbo].[bo].[calls_less_20_sec], [dbo].[bo].[calls_more_20_sec],
[dbo].[bo].[calls_abandon], [dbo].[bo].[abandon_before_20_sec],
[dbo].[bo].[abandon_after_20_sec],
[dbo].[bo].[queue_date],
month (convert (datetime,[dbo].[bo].[queue_date], 103)) as QDate,
year (convert (datetime,[dbo].[bo].[queue_date], 103)) as QDate1,
convert (datetime,[dbo].[bo].[queue_date], 103) as QDate2,
year (convert (datetime,(getdate()), 104)) as year1,
[dbo].[bo].[region], [dbo].[bo].[queue_type],
[dbo].[bo].[month],
[dbo].[bo].[unit],
[dbo].[bo].[service], [dbo].[bo].[reportable], [dbo].[bo].[source_dest],
[dbo].[bo].[file_name]
FROM [dbo].[bo]
Where (year (convert (datetime,[dbo].[bo].[queue_date], 104)))
= (year (convert (datetime,(getdate()), 104)))
the syntax checks fine, and without the "where" clause , i get all the original data returned no problem, so the "month", "year", and "convert" functions work fine. however, when i try to filter the data with the "where" clause above, i get about 15-20 lines of data returned and an error message referring to "Arithmetic overflow...". on their own in the "select" area the statements do what they should, but in the "where" statement they don't. sorry, i'm a big time newbie in sql, so any help would be appreciated.
View 3 Replies
View Related
Mar 14, 2008
I'm trying to clean up our report server and prune reports that are no longer in use by our users. Is there a way to find out the last time a report was accessed?
Thanks.
View 1 Replies
View Related
Jul 31, 2015
I want to view all data that have a date of today, but my query is not returning them? Is it due to the actual data being a datetime and I am not accounting for time? How should I set this query up so that the data returns?
Create Table DateTest(ID int,Date1 datetime)
Insert Into DateTest Values(1, GetDate()), (2, GetDate()), (3, GetDate()), (4, GetDate())
Select * from DateTest
ORDER BY Date1 DESC
Select * from DateTest
where Date1 = convert(varchar(10), GetDate(), 126)
View 9 Replies
View Related
Jan 22, 2008
I created a data model for report builder. And since it wont let me use views, i tried to put all the tables im using in one of my already made views, to create a data source view like my sql view. But when i connect everything the same, my report model still doesnt narrow my search. I only want to see Breed information, but since i have a Breeder table,and Customer table, its showing me all customers, and not limiting it to the ones that are breeder customers.
heres the tables:
Code Snippet
FROM dbo.Tbl_Customer INNER JOIN
dbo.Tbl_Customer_Breeder ON dbo.Tbl_Customer.Customer_Code = dbo.Tbl_Customer_Breeder.Customer_Code INNER JOIN
dbo.Tbl_Customer_Detail ON dbo.Tbl_Customer.Customer_Code = dbo.Tbl_Customer_Detail.Customer_Code INNER JOIN
dbo.Tbl_Customer_Category ON dbo.Tbl_Customer.Customer_Category_Id = dbo.Tbl_Customer_Category.Customer_Category_Id INNER JOIN
dbo.Tbl_Customer_Classification ON
dbo.Tbl_Customer.Customer_Classification_Id = dbo.Tbl_Customer_Classification.Customer_Classification_Id INNER JOIN
dbo.Tbl_Customer_In_Breed ON dbo.Tbl_Customer.Customer_Code = dbo.Tbl_Customer_In_Breed.Customer_Code INNER JOIN
dbo.Tbl_Breed ON dbo.Tbl_Customer_In_Breed.Breed_Id = dbo.Tbl_Breed.Breed_Id
What am i doing wrong, and are there any suggestions.
View 6 Replies
View Related
Nov 28, 2007
Hello,
i've written the following query:
SELECT dbo.KALENDER.KALENDER_ID, dbo.KALENDER.JAHR_BEZ, dbo.KALENDER.JAHR_WERT, dbo.KALENDER.HALBJAHR_WERT,
dbo.KALENDER.HALBJAHR_BEZ1, dbo.KALENDER.HALBJAHR_BEZ2, dbo.KALENDER.QUARTAL_WERT, dbo.KALENDER.QUARTAL_BEZ1,
dbo.KALENDER.QUARTAL_BEZ2, dbo.KALENDER.MONAT_BEZ, dbo.KALENDER.MONAT_WERT, dbo.KALENDER.TAGE_IM_MONAT,
dbo.TAG.KALENDERWOCHE, dbo.TAG.WOCHENTAG, dbo.TAG.TAG, s.STUNDE_ID, s.DATUM_ZEIT
FROM dbo.KALENDER INNER JOIN
dbo.TAG ON dbo.KALENDER.KALENDER_ID = dbo.TAG.KALENDER_ID INNER JOIN
dbo.STUNDE AS s ON dbo.TAG.TAG_ID = s.TAG_ID
WHERE (SELECT MONTH(s.datum_zeit)) = ((SELECT MONTH(GETDATE()))-2)and
(SELECT year(s.datum_zeit)) = (SELECT year(GETDATE()))
order by s.stunde_id
when copying that query to the view editor and executing it, it trys to fix it somehow to:
SELECT TOP (100) PERCENT dbo.KALENDER.KALENDER_ID, dbo.KALENDER.JAHR_BEZ, dbo.KALENDER.JAHR_WERT, dbo.KALENDER.HALBJAHR_WERT,
dbo.KALENDER.HALBJAHR_BEZ1, dbo.KALENDER.HALBJAHR_BEZ2, dbo.KALENDER.QUARTAL_WERT, dbo.KALENDER.QUARTAL_BEZ1,
dbo.KALENDER.QUARTAL_BEZ2, dbo.KALENDER.MONAT_BEZ, dbo.KALENDER.MONAT_WERT, dbo.KALENDER.TAGE_IM_MONAT,
dbo.TAG.KALENDERWOCHE, dbo.TAG.WOCHENTAG, dbo.TAG.TAG, s.STUNDE_ID, s.DATUM_ZEIT
FROM dbo.KALENDER INNER JOIN
dbo.TAG ON dbo.KALENDER.KALENDER_ID = dbo.TAG.KALENDER_ID INNER JOIN
dbo.STUNDE AS s ON dbo.TAG.TAG_ID = s.TAG_ID
WHERE ((SELECT MONTH(s.datum_zeit) AS Expr1
FROM ) =
(SELECT MONTH(GETDATE()) AS Expr1) - 2) AND
((SELECT YEAR(s.datum_zeit) AS Expr1
FROM ) =
(SELECT YEAR(GETDATE()) AS Expr1))
ORDER BY s.STUNDE_ID
... but this causes syntax-errors. I don't understand why this query works fine in the query editor but then gets automatically "destroyed" by the view editor. Do i have to use more statements to get the working query to run inside a view?
Thanks alot for reading.
View 1 Replies
View Related
Jul 23, 2005
I am looking to create a constraint on a table that allows multiplenulls but all non-nulls must be unique.I found the following scripthttp://www.windowsitpro.com/Files/0.../Listing_01.txtthat works fine, but the following lineCREATE UNIQUE CLUSTERED INDEX idx1 ON v_multinulls(a)appears to use indexed views. I have run this on a version of SQLStandard edition and this line works fine. I was of the understandingthat you could only create indexed views on SQL Enterprise Edition?
View 3 Replies
View Related
Jul 24, 2012
Write a CREATE VIEW statement that defines a view named Invoice Basic that returns three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write a SELECT statement that returns all of the columns in the view, sorted by VendorName, where the first letter of the vendor name is N, O, or P.
This is what I have so far,
CREATE VIEW InvoiceBasic AS
SELECT VendorName, InvoiceNumber, InvoiceTotal
From Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID
[code]...
View 2 Replies
View Related
Aug 24, 2007
Hi guys 'n gals,
I created a query, which makes use of a temp table, and I need the results to be displayed in a View. Unfortunately, Views do not support temp tables, as far as I know, so I put my code in a stored procedure, with the hope I could call it from a View....
I tried:
CREATE VIEW [qryMyView]
AS
EXEC pr_MyProc
and unfortunately, it does not let this run.
Anybody able to help me out please?
Cheers!
View 3 Replies
View Related
Mar 9, 2006
I compared view query plan with query plan if I run the same statementfrom view definition and get different results. View plan is moreexpensive and runs longer. View contains 4 inner joins, statisticsupdated for all tables. Any ideas?
View 10 Replies
View Related
Sep 21, 2006
HiI am using SQL 2005, VB 2005I am trying to insert a record using parameters using the following code as per MotLey suggestion and it works finestring insertSQL; insertSQL = "INSERT INTO Issue(ProjectID, TypeofEntryID, PriorityID ,Title, Area) VALUES (@ProjectID, @TypeofEntryID, @PriorityID ,@Title, @Area)"; cmdInsert SqlCommand; cmdInsert=new SqlCommand(insertSQL,conn); cmdInsert.Parameters.Add("@ProjectID",SqlDbType.Varchar).Value=ProjectID.Text; My query is how to detail with dates my previous code wasinsertSQL += "convert(datetime,'" + DateTime.Now.ToString("dd/MM/yy") + "',3), '";I tried the code below but the record doesn't save?string date = DateTime.Now.ToString("dd/MM/yy"); insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@IDIssue, @IDTask, @TaskDone, convert(DateTime,@Date,3),@IDStaff)"; cmdInsert.Parameters.Add("IDIssue", SqlDbType.Int).Value = IDIssue.ToString();cmdInsert.Parameters.Add("IDTask",SqlDbType.Int).Value = IDTask.Text;cmdInsert.Parameters.Add("TaskDone",SqlDbType.VarChar).Value = TaskDoneTxtbox.Text;cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;cmdInsert.Parameters.Add("IDStaff",SqlDbType.Int).Value = IDStaff.Text;Could someone point to me in the right direction?Thanks in advance
View 3 Replies
View Related
Aug 14, 2000
I had given one of our developers create view permissions, but he wants to also modify views that are not owned by him, they are owned by dbo.
I ran a profiler trace and determined that when he tries to modify a view using query designer in SQLem or right clicks in SQLem on the view and goes to properties, it is performing a ALTER VIEW. It does the same for dbo in a trace (an ALTER View). He gets a call failed and a permission error that he doesn't have create view permissions, object is owned by dbo, using both methods.
If it is doing an alter view how can I set permissions for that and why does it give a create view error when its really doing an alter view? Very confusing.
View 1 Replies
View Related
Feb 17, 2006
I have this view in SQL server:
CREATE VIEW dbo.vwFeat
AS
SELECT dbo.Lk_Feat.Descr, dbo.Lk_Feat.Price, dbo.Lk_Feat.Code, dbo.SubFeat.SubNmbr
FROM dbo.Lk_Feat INNER JOIN
dbo.SubFeat ON dbo.Lk_Feat.Idf = dbo.SubFeat.Idt
When ever I open using SQL Entreprise manager to edit it by adding or removing a field i inserts Expr1,2.. and I don t want that. The result I get is:
SELECT dbo.Lk_Feat.Descr AS Expr1, dbo.Lk_Feat.Price AS Expr2, dbo.Lk_Feat.Code AS Expr3, dbo.SubFeat.SubNmbr AS Expr4
FROM dbo.Lk_Feat INNER JOIN
dbo.SubFeat ON dbo.Lk_Feat.Idf = dbo.SubFeat.Idt
I don t want Entreprise manager to generate the Expr fields since I use the real fields in my application.
Thanks for help
View 4 Replies
View Related
Jun 13, 2002
In SQL Server 2000:
How do I convert a Julian date to a Gregorian date?
How do I convert a Gregorian date to Julian?
Examples please.
Many thanks in advance.
Gary Andrews
View 2 Replies
View Related
Oct 27, 2006
I was looking through our vendors views, searching for something Ineeded for our Datawarehouse and I came across something I do notunderstand: I found a view that lists data when I use it in t-sql,however when I try to use the statement when I modified the view (viaMS SQL Server Management Studio) I can not execute the statement. I getThe column prefix 'dbo.tbl_5001_NumericAudit' does not match with atable name or alias name used in the query.Upon closer inspection, I found two ON for the inner join, which I dontthink is correct.So, how can the view work, but not the SQL that defines the view?SQL Server 2000, up to date patches:SELECT dbo.tbl_5001_NumericAudit.aEventID,dbo.tbl_5001_NumericAudit.nParentEventID,dbo.tbl_5001_NumericAudit.nUserID,dbo.tbl_5001_NumericAudit.nColumnID,dbo.tbl_5001_NumericAudit.nKeyID,dbo.tbl_5001_NumericAudit.dChangeTime,CAST(dbo.tbl_5001_NumericAudit.vToValue ASnVarchar(512)) AS vToValue, dbo.tbl_5001_NumericAudit.nChangeMode,dbo.tbl_5001_NumericAudit.tChildEventText, CASEWHEN nConstraintType = 3 THEN 5 ELSE tblColumnMain.nDataType END ASnDataType,dbo.tbl_5001_NumericAudit.nID,CAST(dbo.tbl_5001_NumericAudit.vFromValue AS nVarchar(512)) ASvFromValueFROM dbo.tbl_5001_NumericAudit WITH (NOLOCK) LEFT OUTER JOINdbo.tblColumnMain WITH (NoLock) INNER JOIN---- Posters comment: here is the double ON--dbo.tblCustomField WITH (NoLock) ONdbo.tblColumnMain.aColumnID = dbo.tbl_5001_NumericAudit.nColumnID ONdbo.tbl_5001_NumericAudit.nColumnID =dbo.tblCustomField.nColumnID LEFT OUTER JOINdbo.tblConstraint WITH (NOLOCK) ONdbo.tblCustomField.nConstraintID = dbo.tblConstraint.aConstraintID AND(dbo.tblConstraint.nConstraintType = 4 ORdbo.tblConstraint.nConstraintType = 9 ORdbo.tblConstraint.nConstraintType = 3)UNION ALLSELECT aEventID, nParentEventID, nUserID, nColumnID, nKeyID,dChangeTime, CAST(CAST(vToValue AS decimal(19, 6)) AS nVarchar(512)) ASvToValue,nChangeMode, tChildEventText, 5 AS nDataType,nID, CAST(CAST(vFromValue AS decimal(19, 6)) AS nVarchar(512)) ASvFromValueFROM dbo.tbl_5001_FloatAudit WITH (NOLOCK)UNION ALLSELECT aEventID, nParentEventID, nUserID, nColumnID, nKeyID,dChangeTime, CAST(vToValue AS nVarchar(512)) AS vToValue, nChangeMode,tChildEventText, 2 AS nDataType, nID,CAST(vFromValue AS nVarchar(512)) AS vFromValueFROM dbo.tbl_5001_StringAudit WITH (NOLOCK)UNION ALLSELECT aEventID, nParentEventID, nUserID, nColumnID, nKeyID,dChangeTime, CONVERT(nVarchar(512), vToValue, 121) AS vToValue,nChangeMode,tChildEventText, 3 AS nDataType, nID,CONVERT(nVarchar(512), vFromValue, 121) AS vFromValueFROM dbo.tbl_5001_DateAudit WITH (NOLOCK)
View 1 Replies
View Related
Mar 20, 2015
I am trying to find a beginning date from multiple date ranges, for example:
RowNumberidBegin dtEnd Dt
107933192014-09-022015-06-30
207933192013-09-032014-09-01
307933192012-09-042013-09-02
407933192011-09-062012-09-03
For this id: 0793319, my beginning date is 2011-09-06
108203492014-09-022015-06-30
208203492013-09-032014-09-01
308203492012-09-042013-09-02
408203492011-12-122012-07-03--not a continuous date range
For this id: 0793319, my beginning date is 2012-09-04
108203492014-09-022015-06-30
For this id: 0820349, my beginning date is 2014-09-02
To find continuous date, you look at the beginning date in row 1 and end date in row 2, then if no break in dates, row 2 beginning date to row 3 end date, if no break continue until last date There could multiple dates up to 12 which I have to check for "no break" in dates, if break, display beginning date of last continuous date.
View 9 Replies
View Related
May 30, 2015
I want to compare two columns in the same table called start date and end date for one clientId.if clientId is having continuous refenceid and sartdate and enddate of reference that I don't need any caseopendate but if clientID has new reference id and it's start date is not continuous to its previous reference id then I need to set that start date as caseopendate.
I have table containing 5 columns.
caseid
referenceid
startdate
enddate
caseopendate
[code]...
View 4 Replies
View Related
Mar 18, 2014
I have the following
Column Name : [Converted Date]
Data Type : varchar(50)
When I try and do month around the [Converted Date] I get the following error message
“Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.”
My Query is
SELECT
month([Created Date])
FROM [FDMS_PartnerReporting].[Staging].[Salesforce_MarketingReporting]
View 7 Replies
View Related
Jul 20, 2005
Hi,I have a problem with updating a datetime column,When I try to change the Column from VB I get "Incorrect syntax near'942'" returned from [Microsoft][ODBC SQL Server Driver][SQL Server]'942' is the unique key column valueHowever if I update any other column the syntax is fineThe same blanket update query makes the changes no matter what isupdatedThe problem only happens when I set a unique key on the date field inquestionKey is a composite of an ID, and 2 date fieldsIf I allow duplicates in the index it all works perfectlyI am trying to trap 'Duplicate value in index' (which is working onother non-date columns in other tables)This is driving me nutsAny help would be appreciated
View 5 Replies
View Related
Apr 30, 2015
So I have to build dynamic T-SQL because of a date parameter that will be provided. The Date Parameter will be provided in SSRS in normal MM/DD/CCYY format. So how do I then convert that date to my Oracle format
NUMERIC(8,0) CCYYMMDD?
I tried this...
SET@SQLQuery=@SQLQuery+'ANDMEMBER_SPAN.YMDEFF<='''''+CAST(@AsOfDateASVARCHAR)+''''''+@NewLineChar;
SET@SQLQuery=@SQLQuery+'ANDMEMBER_SPAN.YMDEFF>='''''+CAST(@AsOfDateASVARCHAR)+''''''+@NewLineChar;
but that put it in the format of...
AND
MEMBER_SPAN.YMDEFF<=''2015-04-01''
AND
MEMBER_SPAN.YMDEFF>=''2015-04-01''
Which is close...I think I just need to lose the "-"
View 5 Replies
View Related
Nov 16, 2015
SELECT * ,[Due]
FROM [Events]
Where Due >= getdate() +90
This returns the error: Conversion failed when converting date and/or time from character string
Why would this be? How to cast or convert this so that it will work?
View 24 Replies
View Related
Sep 28, 2007
Using DTS package in 2000 version, I am dumping TXT file contents into SQL Table,
I have one column having date in format YYYYMMDD(20070929) and corresponding column in SQL is datetime, but it fails on data type mismatch.
I have no choice of making date column in SQL to string or Varchar etc,
is there any way to make that date column in SQL to convert the value upon transformation from format (YYYYMMDD) to M/DD/YYYY (9/29/2007).
many many thanks,
View 2 Replies
View Related
Mar 12, 2008
Hi,
Basically the above is a very common requirement, please comment on my solution which I've arrived at by searching through the web; -
In summary I have used 3 SSIS components these are "Flat File Source", "Derived Column" and "SQL Server Destination".
1) File Connections Manager Editor
1.1) Within File Connections Manager Editor; -
Name the data type e.g. "INTERCHANGE_NET_APP_DATE_SRC"
and assign a type to the data type e.g. string[DT_STR]
1.2) Click on the Preview button to ensure the expected text is assigned to the expected data type.
2) Derived Column Transformation Editor
2.1) Assign Derived Column Name, e.g.
INTERCHANGE_NET_APP_DATE
2.2) Select <add as new column> within Derived Column.
2.3) Enter the conversion Expression, e.g. ; -
2.3.1)
(SUBSTRING(INTERCHANGE_NET_APP_DATE_SRC,8,2) + "/" + SUBSTRING(INTERCHANGE_NET_APP_DATE_SRC,5,2) + "/" + SUBSTRING(INTERCHANGE_NET_APP_DATE_SRC,1,4))
2.3.2)
Since the above conversion is such a common task I suggest that Service Pack 3 of SQL Server 2005 delivers the following functionality; -
STRINGTODATE ('YYYYMMDD',INTERCHANGE_NET_APP_DATE_SRC)
2.4) Select "database timestamp [DT_DBTIMESTAMP] " as Data Type.
2.5) Within the Mappings tab of the SQL Destination Editor have; -
Input Column as INTERCHANGE_NET_APP_DATE and
Destination Column as INTERCHANGE_NET_APP_DATE.
Please comment on the above, I will then pass on my suggestion to Microsoft.
Thanks in advance,
Kieran.
View 1 Replies
View Related
