String to Date without time-Sql server 2012

Question

Post reply

String to Date without time-Sql server 2012

sqlinterset

Ten Centuries

Points: 1341
More actions
September 15, 2015 at 7:10 am

#303086

I have string as '1-12-2012', i wanted to convert it into date as format '2012-12-01'. I used the function as cast('1-12-2012' as Date) it's working but for a set of values where Null occurs it gives error as conversion failed when casting date and/or time from character string.
How can i get rid of this situation.

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

That code shouldn't fail with a NULL value, or are you saying you have the string value 'NULL' present in your data?

DECLARE @DateAsString varchar(20) = NULL;

SELECT CAST(@DateAsString AS DATE)

Returns NULL, no error

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Umer Akram SSC-Insane Points: 22209 More actions · Answer 2

Use Try_Convert instead because it will not give you any error of type casting.

Below will give you understanding

Declare @T table ( stringDate varchar(20))

insert into @T

select '2-12-2012' union all

select '11-12-2015' union all

select '12-12-2012' union all

select 'NULL' union all

select '112-12-2012'

select TRY_CONVERT(date, stringDate)

from @T

hope it helps

drew.allen SSC Guru Points: 76987 More actions · Answer 3

Dates do not have formats. Strings that represent dates have formats, but dates themselves do not have formats.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 4

sqlinterset (9/15/2015)
I used the function as cast('1-12-2012' as Date)

That format is "ambiguous" and will depend on your Locale setting (which is both server-dependent and depends on the language of the currently connected users etc.

Only unambiguous formats for DATE datatype is 'yyyymmdd' or 'yyyy-mm-dd' (please note that for DATETIME datatype only 'yyyymmdd' is unambiguous, so you might prefer to use that for DATE too to avoid the risk of accidental assignment to a DATETIME.

You can force the parsing of a date using SET DATEFORMAT

Here's an example:

SET LANGUAGE 'British'

GO

select cast('2012-12-31' as Date) AS [GBUnambiguous]

GO

select cast('12-31-2012' as Date) AS [GBAmbiguous] -- Error!

GO

SET DATEFORMAT MDY

select cast('12-31-2012' as Date) AS [GBAmbiguous]

GO

SET LANGUAGE 'English'

GO

select cast('2012-12-31' as Date) AS [USUnambiguous]

GO

select cast('12-31-2012' as Date) AS [USAmbiguous]

GO

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 5

sqlinterset (9/15/2015)
I have string as '1-12-2012', i wanted to convert it into date as format '2012-12-01'. I used the function as cast('1-12-2012' as Date) it's working but for a set of values where Null occurs it gives error as conversion failed when casting date and/or time from character string.
How can i get rid of this situation.

What format are the incoming dates in? mm-dd-yyyy or dd-mm-yyyy? I ask because casting a real NULL to DATE does NOT cause an error. If you're getting errors, something else is wrong.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)