Convert Varchar(10) to datetime

Dear Friends,

I'm having some problems to convert a varchar(10) to datetime. The problem is:

I have on table with some fileds, and I will talk only about 2. I have the field 1 that is a datetime with the format 2004-02-16 00:00:00.000 and filed 2 that is a varchar(10) with the format 16/02/2004.

I need to convert the field 2 to datetime to use the condition Field1>Field2

At the moment I have this:

SELECT RKFolder, RKData,CONVERT(DATETIME,CONVERT(VARCHAR(10), Maturity, 121), 121)AS Campo2

FROM FL_BondsSynthetic

And the error is:

Msg 242, Level 16, State 3, Line 1

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Could you help me?

Hopefully, this will give you the clue you seek:

DECLARE @.MyDate varchar(10)
SET @.MyDate = '16/02/2004'

SET dateformat dmy
SELECT convert( datetime, @.MyDate )

If you get an 'Out of Range' error, there is some non-conforming data that will have to be found and corrected.

|||I am moving this to the Transact-SQL forum, which can provide further insights.|||don't change the dateformat

specify the style instead

DECLARE @.MyDate varchar(10)
SET @.MyDate = '16/02/2004'
SELECT convert( datetime, @.MyDate, 103)sqlsql

Convert Varchar(10) to datetime

Dear Friends,

I'm having some problems to convert a varchar(10) to datetime. The problem is:

I have on table with some fileds, and I will talk only about 2. I have the field 1 that is a datetime with the format 2004-02-16 00:00:00.000 and filed 2 that is a varchar(10) with the format 16/02/2004.

I need to convert the field 2 to datetime to use the condition Field1>Field2

At the moment I have this:

SELECT RKFolder, RKData, CONVERT(DATETIME, CONVERT(VARCHAR(10), Maturity, 121), 121) AS Campo2

FROM FL_BondsSynthetic

And the error is:

Msg 242, Level 16, State 3, Line 1

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Could you help me?

Hopefully, this will give you the clue you seek:

DECLARE @.MyDate varchar(10)
SET @.MyDate = '16/02/2004'

SET dateformat dmy
SELECT convert( datetime, @.MyDate )

If you get an 'Out of Range' error, there is some non-conforming data that will have to be found and corrected.

|||I am moving this to the Transact-SQL forum, which can provide further insights.|||don't change the dateformat

specify the style instead

DECLARE @.MyDate varchar(10)
SET @.MyDate = '16/02/2004'
SELECT convert( datetime, @.MyDate, 103)

Convert string to datetime - Performance

Dear Friends,

I have a doubt relation when converting a field…

I ‘m converting a string to datetime inside a SQL command in an OLE DB Source using this:

convert(datetime,[Maturity],103) As MaturityDate

It’s better to use inside the OLE DB Source or is better to convert it with data conversion transformation?

Regards!!

Thanks

Probably just a matter of opinion. SSIS can do datatype conversions, but has to create a new column in the pipeline to hold the new type, which is a little bit inefficient. I generally don't like things "hidden" in the OLE DB Source, but in this case I think that's what I'd do.
|||

But witch of these options is more fast?!

Regards!

|||

PedroCGD wrote:

But witch of these options is more fast?!

Regards!

I think SQL would be faster. And because I hate being wrong, I tested it before I said that. I averaged the times for three runs of 20,000 rows and SQL appears 12% faster than SSIS.
|||

PedroCGD wrote:

But witch of these options is more fast?!

Regards!

Why don't you try it and tell us. You're in the best position to judge because its your data.

-Jamie

|||

I'm agree with you jamie!! :-)

What a question I made... eheheh... I can see the time in the logs of the cube!!!

Sorry...

Tomorrow I will give you the response!! thanks both!!

|||Someone help me?!|||

PedroCGD wrote:

Someone help me?!

Umm, with what? Call 911? |||

sorry was teh wrong post!!

And relating to this post, the better choice is converting the date in OLE DB SOurce.

Thanks!

|||

PedroCGD wrote:

sorry was teh wrong post!!

And relating to this post, the better choice is converting the date in OLE DB SOurce.

Thanks!

Good to knw (and as expected). Thanks Pedro.

convert seconds to datetime

Hi friends,
I want to convert datetime that is stored as seconds to the actual datetime.
Example
Expiredate is stored as seconds since 1/1/1970 (ctime)
Expiredate = 1200373200
convert this to actual datetime
Please help me to solve this. It is very urgent.
thanks
vanithavanitha try
declare @.Expiredate bigint
select @.Expiredate = 1200373200
select dateadd(s,@.Expiredate ,01/01/1970)
Regards
R.D
"vanitha" wrote:

> Hi friends,
> I want to convert datetime that is stored as seconds to the actual datetim
e.
> Example
> Expiredate is stored as seconds since 1/1/1970 (ctime)
> Expiredate = 1200373200
> convert this to actual datetime
> Please help me to solve this. It is very urgent.
> thanks
> vanitha
>|||or
declare @.Expiredate bigint
declare @.Ctime datetime
select @.Ctime ='01/01/1970'
select @.Expiredate = 1200373200
select dateadd(s,@.Expiredate ,@.Ctime)
"vanitha" wrote:

> Hi friends,
> I want to convert datetime that is stored as seconds to the actual datetim
e.
> Example
> Expiredate is stored as seconds since 1/1/1970 (ctime)
> Expiredate = 1200373200
> convert this to actual datetime
> Please help me to solve this. It is very urgent.
> thanks
> vanitha
>|||Thanks R.D
we shd put date inside the quotes otherwise its giving us wrong solution. I
don't know y.
example
declare @.Expiredate bigint
select @.Expiredate = 1200373200
select dateadd(s,@.Expiredate ,01/01/1970)
result : 1938-01-15 05:00:00.000
declare @.Expiredate bigint
select @.Expiredate = 1200373200
select dateadd(s,@.Expiredate ,'01/01/1970')
result : 2008-01-15 05:00:00.000
thanks
vanitha
"R.D" wrote:
> vanitha try
> declare @.Expiredate bigint
> select @.Expiredate = 1200373200
> select dateadd(s,@.Expiredate ,01/01/1970)
> Regards
> R.D
> "vanitha" wrote:
>|||you r right vanitha.
if you dont put inside quotes then it treats it as date out of range in sql
server. so starts from base date of sql server.
Regards
R.D
"vanitha" wrote:
> Thanks R.D
> we shd put date inside the quotes otherwise its giving us wrong solution.
I
> don't know y.
> example
> declare @.Expiredate bigint
> select @.Expiredate = 1200373200
> select dateadd(s,@.Expiredate ,01/01/1970)
> result : 1938-01-15 05:00:00.000
> declare @.Expiredate bigint
> select @.Expiredate = 1200373200
> select dateadd(s,@.Expiredate ,'01/01/1970')
> result : 2008-01-15 05:00:00.000
> thanks
> vanitha
>
> "R.D" wrote:
>|||vanitha
when you get help from news groups, It is your duty to check it as answer.
This will encourage people to answer more quetions. do read help availbe top
right corner.
Regards
R.D
"vanitha" wrote:

> Hi friends,
> I want to convert datetime that is stored as seconds to the actual datetim
e.
> Example
> Expiredate is stored as seconds since 1/1/1970 (ctime)
> Expiredate = 1200373200
> convert this to actual datetime
> Please help me to solve this. It is very urgent.
> thanks
> vanitha
>|||sure R.D.
I used to do it. this time i just replied back.
thnks
"R.D" wrote:
> vanitha try
> declare @.Expiredate bigint
> select @.Expiredate = 1200373200
> select dateadd(s,@.Expiredate ,01/01/1970)
> Regards
> R.D
> "vanitha" wrote:
>|||Vanithaji
I was talking generally, not for my post alone. Any way thanks for that.
Regards
R.D
"R.D" wrote:
> vanitha
> when you get help from news groups, It is your duty to check it as answer.
> This will encourage people to answer more quetions. do read help availbe t
op
> right corner.
> Regards
> R.D
> "vanitha" wrote:
>|||The date should really be expressed in "yyyymmdd hh:nn:ss.000" format as
it is unambiguous regardless of locale. For example:
dateadd(s, @.Expiredate, '19700101 00:00:00.000')
or
dateadd(s, @.Expiredate, '19700101')
*mike hodgson*
blog: http://sqlnerd.blogspot.com
vanitha wrote:
>Thanks R.D
>we shd put date inside the quotes otherwise its giving us wrong solution. I
>don't know y.
>example
>declare @.Expiredate bigint
> select @.Expiredate = 1200373200
> select dateadd(s,@.Expiredate ,01/01/1970)
>result : 1938-01-15 05:00:00.000
>declare @.Expiredate bigint
> select @.Expiredate = 1200373200
> select dateadd(s,@.Expiredate ,'01/01/1970')
>result : 2008-01-15 05:00:00.000
>thanks
>vanitha
>
>"R.D" wrote:
>
>|||Help in the top right corner? Only if you're using a web-based
newsreader such as
http://www.microsoft.com/technet/co...r&lang=en&cr=US
(Many of us use a real NNTP newsreader, like Mozilla Thunderbird or
Microsoft Outlook Express for example.) ;)
*mike hodgson*
blog: http://sqlnerd.blogspot.com
R.D wrote:
>vanitha
>when you get help from news groups, It is your duty to check it as answer.
>This will encourage people to answer more quetions. do read help availbe to
p
>right corner.
>Regards
>R.D
>"vanitha" wrote:
>
>

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?