Mryntu

How to verbalise code in Mathematica?

What happened to Captain America in Endgame?

Is there any limitation with Arduino Nano serial communication distance?

Does this extra sentence in the description of the warlock's Eyes of the Rune Keeper eldritch invocation appear in any official reference?

How can Republicans who favour free markets, consistently express anger when they don't like the outcome of that choice?

Killing undead fish underwater

How to make a pipeline wait for end-of-file or stop after an error?

Was there a Viking Exchange as well as a Columbian one?

Will a top journal at least read my introduction?

Why was Germany not as successful as other Europeans in establishing overseas colonies?

Why does nature favour the Laplacian?

Is the 5 MB static resource size limit 5,242,880 bytes or 5,000,000 bytes?

Do I have to worry about players making “bad” choices on level up?

What is the difference between `command a[bc]d` and `command `ab,cd`

A ​Note ​on ​N!

Why do Computer Science majors learn Calculus?

Why does processed meat contain preservatives, while canned fish needs not?

Examples of non trivial equivalence relations , I mean equivalence relations without the expression " same ... as" in their definition?

Can solid acids and bases have pH values? If not, how are they classified as acids or bases?

how to interpret this t result?

How would one muzzle a full grown polar bear in the 13th century?

Is there really no use for MD5 anymore?

How do we know that ממחרת השבת means from the first day of pesach and not the seventh?

function to receive a character input and return date format (with incorrect input)

function to receive a character input and return date format (with incorrect input)

stored procedure returns ERROR 1305 (42000): FUNCTION does not existIn PostgreSQL, is there a type-safe first() aggregate function?Conversion of a varchar data type to a datetime data type resulted in an out-of-range valueSQL Server : implicit calculation of precision/scale for datetime scalarDoes the function inside the Select clause evaluate the passed in columns before the where clause?how to convert this varchar to datetime format?Excel Import ID, Data Type DoubleHow do I set a SQL Server Unicode / NVARCHAR string to an emoji or Supplementary Character?Datetime to formatted Date Implicit ConversionMS SQL - Convert string mm/dd to date

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

3

I need to write a function to receive a string character and return the date format . For example the input is 20120101 and i need this 2012-01-01. The problem is that there might be some incorrect inputs like this "2012ABCD". In that case, I want the function to return a fixed date such as 2020-01-01. What I've written so far is:

Create Function ReturnDate
(@date varchar(8))

Returns date

 as

 begin
 declare @result date

 set @result = (select convert(date , @date,111))
 if(@@ROWCOUNT>0) return @result
 else return '2020-01-01'
 return @result
 end

This doesn't work and I just don't know how to handle the second part (when the input is incorrect).

sql-server t-sql functions

share|improve this question

edited 14 mins ago

Tony Hinkle

3,1151725

asked 1 hour ago

Pantea TourangPantea Tourang

547

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I might recommend that you read "Querying Data with Transact-SQL" If you will be doing a lot of SQL programming, this book will teach you the basics of how to code things like this. amazon.com/Exam-70-761-Querying-Data-Transact-SQL-ebook/dp/…
  
  – Tony Hinkle
  20 mins ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Do you want strict parsing for yyyymmdd format?
  
  – Dan Guzman
  18 mins ago
  

  
  
  
  

add a comment | 

3

I need to write a function to receive a string character and return the date format . For example the input is 20120101 and i need this 2012-01-01. The problem is that there might be some incorrect inputs like this "2012ABCD". In that case, I want the function to return a fixed date such as 2020-01-01. What I've written so far is:

Create Function ReturnDate
(@date varchar(8))

Returns date

 as

 begin
 declare @result date

 set @result = (select convert(date , @date,111))
 if(@@ROWCOUNT>0) return @result
 else return '2020-01-01'
 return @result
 end

This doesn't work and I just don't know how to handle the second part (when the input is incorrect).

sql-server t-sql functions

share|improve this question

edited 14 mins ago

Tony Hinkle

3,1151725

asked 1 hour ago

Pantea TourangPantea Tourang

547

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I might recommend that you read "Querying Data with Transact-SQL" If you will be doing a lot of SQL programming, this book will teach you the basics of how to code things like this. amazon.com/Exam-70-761-Querying-Data-Transact-SQL-ebook/dp/…
  
  – Tony Hinkle
  20 mins ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Do you want strict parsing for yyyymmdd format?
  
  – Dan Guzman
  18 mins ago
  

  
  
  
  

add a comment | 

I need to write a function to receive a string character and return the date format . For example the input is 20120101 and i need this 2012-01-01. The problem is that there might be some incorrect inputs like this "2012ABCD". In that case, I want the function to return a fixed date such as 2020-01-01. What I've written so far is:

Create Function ReturnDate
(@date varchar(8))

Returns date

 as

 begin
 declare @result date

 set @result = (select convert(date , @date,111))
 if(@@ROWCOUNT>0) return @result
 else return '2020-01-01'
 return @result
 end

This doesn't work and I just don't know how to handle the second part (when the input is incorrect).

sql-server t-sql functions

share|improve this question

edited 14 mins ago

Tony Hinkle

3,1151725

asked 1 hour ago

Pantea TourangPantea Tourang

547

Create Function ReturnDate
(@date varchar(8))

Returns date

 as

 begin
 declare @result date

 set @result = (select convert(date , @date,111))
 if(@@ROWCOUNT>0) return @result
 else return '2020-01-01'
 return @result
 end

share|improve this question

edited 14 mins ago

Tony Hinkle

3,1151725

asked 1 hour ago

Pantea TourangPantea Tourang

547

share|improve this question

edited 14 mins ago

Tony Hinkle

3,1151725

asked 1 hour ago

Pantea TourangPantea Tourang

547

edited 14 mins ago

Tony Hinkle

3,1151725

edited 14 mins ago

Tony Hinkle

3,1151725

asked 1 hour ago

Pantea TourangPantea Tourang

547

asked 1 hour ago

Pantea TourangPantea Tourang

547

1 Answer
1

active

oldest

votes

2

You can use TRY_CONVERT to check to see if the input can be converted. If it can't, a NULL value is returned so then you can then do an COALESCE to get either the converted value or the fixed date.

begin
 declare @result date
 set @result = TRY_CONVERT(date, @date, 111)
 set @result = COALESCE(@result, '2012-01-01')
 return @result
end

You could also use a TRY CATCH block and return the fixed date in the FINALLY block, but it's best practice to use TRY_CONVERT so that SQL Server doesn't have to handle an error as that requires more time and resources.

share|improve this answer

answered 34 mins ago

Tony HinkleTony Hinkle

3,1151725

add a comment | 

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f236872%2ffunction-to-receive-a-character-input-and-return-date-format-with-incorrect-inp%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

2

You can use TRY_CONVERT to check to see if the input can be converted. If it can't, a NULL value is returned so then you can then do an COALESCE to get either the converted value or the fixed date.

begin
 declare @result date
 set @result = TRY_CONVERT(date, @date, 111)
 set @result = COALESCE(@result, '2012-01-01')
 return @result
end

You could also use a TRY CATCH block and return the fixed date in the FINALLY block, but it's best practice to use TRY_CONVERT so that SQL Server doesn't have to handle an error as that requires more time and resources.

share|improve this answer

answered 34 mins ago

Tony HinkleTony Hinkle

3,1151725

add a comment | 

2

You can use TRY_CONVERT to check to see if the input can be converted. If it can't, a NULL value is returned so then you can then do an COALESCE to get either the converted value or the fixed date.

begin
 declare @result date
 set @result = TRY_CONVERT(date, @date, 111)
 set @result = COALESCE(@result, '2012-01-01')
 return @result
end

You could also use a TRY CATCH block and return the fixed date in the FINALLY block, but it's best practice to use TRY_CONVERT so that SQL Server doesn't have to handle an error as that requires more time and resources.

share|improve this answer

answered 34 mins ago

Tony HinkleTony Hinkle

3,1151725

add a comment | 

You can use TRY_CONVERT to check to see if the input can be converted. If it can't, a NULL value is returned so then you can then do an COALESCE to get either the converted value or the fixed date.

begin
 declare @result date
 set @result = TRY_CONVERT(date, @date, 111)
 set @result = COALESCE(@result, '2012-01-01')
 return @result
end

You could also use a TRY CATCH block and return the fixed date in the FINALLY block, but it's best practice to use TRY_CONVERT so that SQL Server doesn't have to handle an error as that requires more time and resources.

share|improve this answer

answered 34 mins ago

Tony HinkleTony Hinkle

3,1151725

begin
 declare @result date
 set @result = TRY_CONVERT(date, @date, 111)
 set @result = COALESCE(@result, '2012-01-01')
 return @result
end

share|improve this answer

answered 34 mins ago

Tony HinkleTony Hinkle

3,1151725

answered 34 mins ago

Tony HinkleTony Hinkle

3,1151725

answered 34 mins ago

Tony HinkleTony Hinkle

3,1151725