Date conversion problem

Question

Date conversion problem

KumarVelayutham

SSCrazy

Points: 2204
More actions
September 14, 2012 at 10:53 am

#261671

I am trying to convert varchar data '27/04/2012'( dd/mm/yyyy) to date column as like below conversion
cast (Processing_Date as date).
But it giving error like 'Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string'
Seems to be varchar data is changing into yyyy/mm/dd(2012/27/04) after conversion.thats is the problem. i.e month and day value changed.
Please help to resolve the issue by correct format before loading data?
Any help will be appreciated!!!!!!!!!
Regards,
Kumar

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

KumarSQLDBA (9/14/2012)
I am trying to convert varchar data '27/04/2012'( dd/mm/yyyy) to date column as like below conversion
cast (Processing_Date as date).
But it giving error like 'Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string'
Seems to be varchar data is changing into yyyy/mm/dd(2012/27/04) after conversion.thats is the problem. i.e month and day value changed.
Please help to resolve the issue by correct format before loading data?
Any help will be appreciated!!!!!!!!!

The default string format for converting a character string to date is YYYY-MM-DD. try this:

select convert(date, Processing_Date, 103)

Brandie Tarvin SSC Guru Points: 173068 More actions · Answer 2

Read this link for reasons why Lynn chose 103 and what all those numbers mean:

http://msdn.microsoft.com/en-us/library/ms187928.aspx

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

SET DATEFORMAT DMY;

--SET DATEFORMAT MDY

DECLARE @processing_date AS varchar( 10 );

SET @processing_date = '27/04/2012';

SELECT CAST( @Processing_Date AS date );

this should work as well, but I think it is better to use previous solution and understand the options available

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

ScottPletcher SSC Guru Points: 101101 More actions · Answer 4

Better still, just always use 'YYYYMMDD[ 24h:mm:ss.sss]', which always works, regardless of SQL settings.

'20120427'

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

ScottPletcher (9/14/2012)
Better still, just always use 'YYYYMMDD[ 24h:mm:ss.sss]', which always works, regardless of SQL settings.
'20120427'

Problem is you don't always have that choice.