Zrjydtj

Disable screen dimming in Ubuntu Mate 18.04.2 LTS

Why isn't the definition of absolute value applied when squaring a radical containing a variable?

Binary Numbers Magic Trick

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

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

Was there a shared-world project before "Thieves World"?

Reverse the word in a string with the same order in javascript

French for 'It must be my imagination'?

Is creating your own "experiment" considered cheating during a physics exam?

Mac Pro install disk keeps ejecting itself

Sci-fi novel series with instant travel between planets through gates. A river runs through the gates

Was it really necessary for the Lunar module LM to have 2 stages?

Do I have an "anti-research" personality?

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

Is there any limitation with Arduino Nano serial communication distance?

How to pronounce 'C++' in Spanish

Why was the Spitfire's elliptical wing almost uncopied by other aircraft of World War 2?

Fizzy, soft, pop and still drinks

Who is the Umpire in this picture?

Killing undead fish underwater

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

How to verbalise code in Mathematica?

What happened to Captain America in Endgame?

Sci-fi book: portals appear in London and send a failed artist towards a designated path where he operate a giant superweapon

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

stored procedure returns ERROR 1305 (42000): FUNCTION does not existHelp with tricky update statementIn PostgreSQL, is there a type-safe first() aggregate function?How do I create a user-defined aggregate function in MySQL (redux)?Conversion of a varchar data type to a datetime data type resulted in an out-of-range valuehow to convert this varchar to datetime format?Excel Import ID, Data Type DoubleQuery Time is doubled because of a column in selectDatetime to formatted Date Implicit Conversioncreating postgresql function with table as input

.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 3 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
  9 mins ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Do you want strict parsing for yyyymmdd format?
  
  – Dan Guzman
  7 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 3 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
  9 mins ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Do you want strict parsing for yyyymmdd format?
  
  – Dan Guzman
  7 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 3 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 3 mins ago

Tony Hinkle

3,1151725

asked 1 hour ago

Pantea TourangPantea Tourang

547

share|improve this question

edited 3 mins ago

Tony Hinkle

3,1151725

asked 1 hour ago

Pantea TourangPantea Tourang

547

edited 3 mins ago

Tony Hinkle

3,1151725

edited 3 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 23 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 23 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 23 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 23 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 23 mins ago

Tony HinkleTony Hinkle

3,1151725

answered 23 mins ago

Tony HinkleTony Hinkle

3,1151725

answered 23 mins ago

Tony HinkleTony Hinkle

3,1151725