Yhyjyk

If a warlock with the Repelling Blast invocation casts Eldritch Blast and hits, must the targets always be pushed back?

What is the strongest case that can be made in favour of the UK regaining some control over fishing policy after Brexit?

How to back up a running remote server?

How to pronounce 'C++' in Spanish

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

Providence Pentominoes Puzzle By Andrew Bradburn (Jigsaw)

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

Pulling the rope with one hand is as heavy as with two hands?

Binary Numbers Magic Trick

Is there really no use for MD5 anymore?

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

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

Why the difference in metal between 銀行 and お金?

How to stop co-workers from teasing me because I know Russian?

US visa is under administrative processing, I need the passport back ASAP

A possible fake AI on Patreon!

How can governments justify taking income tax on earnings, and then taking more tax on spending?

How do Bards prepare spells?

How to figure out whether the data is sample data or population data apart from the client's information?

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

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

Any examples of headwear for races with animal ears?

Hilbert Space and Banach Space

How could Tony Stark make this in Endgame?

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

Hope you're doing well

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 58 mins ago

Pantea TourangPantea Tourang

547

add a comment | 

3

Hope you're doing well

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 58 mins ago

Pantea TourangPantea Tourang

547

add a comment | 

Hope you're doing well

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 58 mins 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 58 mins ago

Pantea TourangPantea Tourang

547

share|improve this question

edited 3 mins ago

Tony Hinkle

3,1151725

asked 58 mins ago

Pantea TourangPantea Tourang

547

edited 3 mins ago

Tony Hinkle

3,1151725

edited 3 mins ago

Tony Hinkle

3,1151725

asked 58 mins ago

Pantea TourangPantea Tourang

547

asked 58 mins 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

answered 9 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

answered 9 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

answered 9 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

answered 9 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

answered 9 mins ago

Tony HinkleTony Hinkle

3,1151725

answered 9 mins ago

Tony HinkleTony Hinkle

3,1151725

answered 9 mins ago

Tony HinkleTony Hinkle

3,1151725