10774: PARSE or TRY_PARSE

Hi experts:

i am reading about PARSE and TRY_PARSE
http://www.mssqltips.com/sqlservertip/2564/new-data-type-conversion-functions-in-sql-server-2012/

I do not understand in what cases should use either
enrique_aeoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

vr6rCommented:
If Parse fails, it will error out and your sql statement will fail.

Try_Parse on the other hand will return Null if it is not able to successfully perform the parse, but will not error out.

In practice you might use it in an example like this.  Say you have a script that attempts to parse a string to a datetime but your source doesn't always provide a string.  Using Try_Parse, you could provide a default date in the event the Try_Parse call returns NULL.
0
enrique_aeoAuthor Commented:
can you give T-SQL please
0
vr6rCommented:
Sure, for this example say we want to parse a 'last active' date string, but in the event that fails we want to fall back to an 'account creation' datetime that we can always expect to be populated.

This will try to parse the string but will return NULL if that fails, which the COALESCE expression will catch and will instead use the account_creation field, so we can count on our resulting query to always have a "lastactive" column populated.

SELECT
   users.firstname,
   users.lastname,
   COALESCE(TRY_PARSE(users.last_active AS datetime), users.account_creation) as lastactive
FROM users

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
enrique_aeoAuthor Commented:
I have this table
CREATE TABLE [HumanResources].[Employee_Parse](
      [EmployeeID] [int] IDENTITY(1,1) NOT NULL,
      [NationalIDNumber] [nvarchar](15) NOT NULL,
      [ContactID] [int] NOT NULL,
      [LoginID] [nvarchar](256) NOT NULL,
      [ManagerID] [int] NULL,
      [Title] [nvarchar](50) NOT NULL,
      [BirthDate] [datetime] NULL,
      [MaritalStatus] [nchar](1) NOT NULL,
      [Gender] [nchar](1) NOT NULL,
      [HireDate] [datetime] NULL,
      [SalariedFlag] [dbo].[Flag] NOT NULL,
      [VacationHours] [smallint] NOT NULL,
      [SickLeaveHours] [smallint] NOT NULL,
      [CurrentFlag] [dbo].[Flag] NOT NULL,
      [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
      [ModifiedDate] [datetime] NULL)

I am try this
Use AdventureWorks
GO                                          
SELECT
   [NationalIDNumber],
   [LoginID],
   COALESCE(TRY_PARSE([BirthDate] AS datetime),[ModifiedDate] ) as [Modified]
FROM [HumanResources].[Employee_Parse]

but i have this error
Msg 8116, Level 16, State 1, Line 1
Argument data type datetime is invalid for argument 1 of parse function.
0
enrique_aeoAuthor Commented:
it is OK
CREATE TABLE [HumanResources].[Employee_Parse](
      [EmployeeID] [int]  NOT NULL,
      [NationalIDNumber] [nvarchar](15) NOT NULL,
      [ContactID] [int] NOT NULL,
      [LoginID] [nvarchar](256) NOT NULL,
      [ManagerID] [int] NULL,
      [Title] [nvarchar](50) NOT NULL,
      [BirthDate] [nvarchar](50) NULL,
      [MaritalStatus] [nchar](1) NOT NULL,
      [Gender] [nchar](1) NOT NULL,
      [HireDate] [nvarchar](50) NULL,
      [SalariedFlag] [dbo].[Flag] NOT NULL,
      [VacationHours] [smallint] NOT NULL,
      [SickLeaveHours] [smallint] NOT NULL,
      [CurrentFlag] [dbo].[Flag] NOT NULL,
      [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
      [ModifiedDate] [nvarchar](50) NULL)


INSERT INTO [HumanResources].[Employee_Parse]
               SELECT *
               FROM [HumanResources].[Employee]

update [HumanResources].[Employee_Parse]
set [ModifiedDate] = GETDATE()

update [HumanResources].[Employee_Parse]
set [BirthDate] = ' '
where [EmployeeID] > 200

SELECT
   [NationalIDNumber],
   [LoginID],
   [BirthDate],
   TRY_PARSE([BirthDate] AS datetime) TRY_PARSE_BirthDate,
   COALESCE(TRY_PARSE([BirthDate] AS datetime),[ModifiedDate] ) as [Modified]
FROM [HumanResources].[Employee_Parse]
order by [EmployeeID] desc
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.