Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 converting varchar date from dd/mm/yy to mm/dd/yy

Author  Topic 

vision.v1
Yak Posting Veteran

72 Posts

Posted - 2011-06-29 : 12:59:59
Hi,

In my application am getting data from client file for date field as '25/01/11' i.e., in 'dd/mm/yy' instead of 'mm/dd/yy' so when am trying to insert into my table am getting the error message like:

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.

I tried like below:

CREATE TABLE #temp
(
datevalue DATETIME
)

INSERT INTO #temp VALUES(convert(varchar(10),CAST('25/04/11' AS DATETIME),101))

How can i convert the data from '25/01/11' to '01/25/11'

please advise.....

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-29 : 13:04:44
[code]INSERT INTO #temp VALUES(convert(DATETIME,'25/04/11', 3))[/code]
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-29 : 13:05:31
convert(varchar(10),convert(datetime,@str,103),101)

Be careful - it sounds like you may be dependent on environment settings which may change at any time and you won't know about it until the 13th of the month when it fails.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-06-29 : 13:40:54
Just to be dumb...


Declare @a varchar(20)
Set @a = '25/01/11'

Select @a, stuff(stuff(@a,1,3,''),4,0,LEFT(@a,3))


Corey

I Has Returned!!
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-29 : 14:38:06
quote:
Originally posted by Seventhnight

Just to be dumb...


Declare @a varchar(20)
Set @a = '25/01/11'

Select @a, stuff(stuff(@a,1,3,''),4,0,LEFT(@a,3))


Corey

I Has Returned!!

Meh! I like mine better
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-06-30 : 09:21:13
In the application, you should format the date in YYYYMMDD format and pass it to a table.

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -