converting varchar date from dd/mm/yy to mm/dd/yy

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]

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.

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!!

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources