Convert Function

Dear Friends,

I have a OLEDB Source and on it I have a convert(datetime,GetDate() ,102) AS RKData.

The field RKDATA returns me the value 2007-05-02 11:23:00...

How can I return only the date without time? Like:

2007-05-02 00:00:00...

Hi PedroCGD,

Either of these should work,

SELECT CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE()))) as RKData

or

SELECT CAST(CAST(YEAR(GetDate()) AS VARCHAR(4)) + '/' + CAST(MONTH(GetDate()) AS VARCHAR(2)) + '/' + CAST(DAY(GetDate()) AS VARCHAR(2)) AS DATETIME) as RKData

|||Add a derived column transformation to the data flow and use the following expression:

(DT_DBTIMESTAMP)(DT_DBDATE)([RKDATA])|||

Subhash,

It works. And How can I do If I want to increment 15 days to the getdate?

Thanks!

|||

PedroCGD wrote:

Subhash,

It works. And How can I do If I want to increment 15 days to the getdate?

Thanks!

Use the dateadd() function either in your query or in a derived column.

Just so you know, technically, your question should be in the Transact-SQL forum. That is the reason I've given you SSIS methods of solving your question -- to tie it to SSIS.|||

SELECT CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE()+15))) as RKData

resolve my question... dont need derivated column... I can include in SQL Statment of OLEDBSource

Thanks!

|||

PedroCGD wrote:

SELECT CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE()+15))) as RKData

resolve my question... dont need derivated column... I can include in SQL Statment of OLEDBSource

Thanks!

This is a more appropriate statement because it works with date routines and is, frankly, more readable:

SELECT CONVERT(DATETIME, CONVERT(VARCHAR(20), DATEADD("d",15,GETDATE()),101))
|||

in the datareader source... it works if the database is SQL Server, but in my case the database is SYBASE and I use ODBC in datareader...

The statment:

SELECT Folders.Folders_ShortName, Bonds.Bonds_Name, Currencies.Currencies_ShortName, BondsDeals.CaptureDate,
BondsDeals.ValueDate, BondsDeals.Quantity, BondsDeals.DealType

FROM (((Bonds INNER JOIN BondsDeals ON Bonds.Bonds_Id = BondsDeals.Bonds_Id)
INNER JOIN Folders ON BondsDeals.Folders_Id = Folders.Folders_Id)
INNER JOIN Currencies ON Bonds.Currencies_Id = Currencies.Currencies_Id)

WHERE BondsDeals.CaptureDate<CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE()+1)))
AND Folders.Folders_ShortName Not Like 'FID%'
AND Folders.Folders_ShortName Not Like 'FPS%'
AND Folders.Folders_ShortName Not Like 'VDR%'

The error:

"...An error occurred executing the provider SQL command."

"... INT is incompatible with DATETIME..."

|||

Yes, I think your tip is better... but my datareader (use to link via ODBC to SYBASE) doesnt work... if the database is SQL works, but in link to SYBASE, teh DataReader Source doesnt recognize DateAdd...

Do you have tips?