writing a function in sql server

Question

I wrote a function which converts a string into datetime. So,this is what I did

   CREATE FUNCTION formatit(
      @fromtime VARCHAR(50) -- varchar
       )
    RETURNS DATETIME
    AS 
    BEGIN 
    DECLARE @from varchar(50)
    DECLARE @value
      IF (CHARINDEX('NOON',@fromtime,0)) = 0 THEN

       SET @from = CONVERT(DATETIME, @fromtime) 
     ELSE
       SET @from =CONVERT(DATETIME, '01/01/2000 12pm') 
    RETURN(@from)
    END

 SELECT dbo.formatit('04/12/2011 NOON ')

So, u can see that if fromtime consists of word NOON i'm trying to use a default date. But i've been getting a error 'Conversion failed when converting datetime from character string.'

It works fine when I enter any time like 4 pm etc. but fails when i give noon. can u please letme know the way i can handle this?

If you want to use special values in time such as "Midnight", "Noon", "Dusk" You must create the necessary if statements in your code to define those time values. — John Hartsock
– John Hartsock, Commented Sep 9, 2011 at 20:08
My suggestion would be to stop letting users type in things like "noon" - you're going to have to code hundreds of different IF statements for all the potential scenarios. Make them pick from a date/time picker control, then you can have complete power over how the strings are formatted, and not worry about any of these special cases. — Aaron Bertrand
– Aaron Bertrand, Commented Sep 9, 2011 at 20:11
@Aaron: It is a modiifcation to already existing site. So, I don't think i can change it now — user838359
– user838359, Commented Sep 9, 2011 at 20:12

Hogan · Accepted Answer · 2011-09-09 20:12:02Z

1

enter image description here

this works

 declare @from datetime

 SET @from =CONVERT(DATETIME, '01/01/2000 12:00 pm')

The following was tested.

  CREATE FUNCTION formatit(
      @fromtime VARCHAR(50) -- varchar
       )
    RETURNS DATETIME
    AS 
    BEGIN 
    DECLARE @from datetime
     IF (CHARINDEX('NOON',@fromtime,0)) = 0 
       SET @from = CONVERT(DATETIME, @fromtime) 
     ELSE
       SET @from =CONVERT(DATETIME, '01/01/2000 12pm') 
    RETURN(@from)
    END

 SELECT dbo.formatit('04/12/2011 NOON ')

edited Sep 9, 2011 at 20:12

answered Sep 9, 2011 at 20:06

Hogan

70.7k10 gold badges83 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Hogan Over a year ago

I just ran it on the server. did you change how you declare @from?

user838359 Over a year ago

DO u think , my if statement is correct.. I get error at IF else.. i mean ur code

Hogan Over a year ago

Pictures or it didn't happen. OH LOOK a picture.

Hogan Over a year ago

Yes your if statement had an extra THEN the code I posted works.

James Johnson · Accepted Answer · 2011-09-09 20:06:56Z

1

I think you need to add the minutes after the 12, and put a space between the time and the AM/PM. See below:

SET @from = CONVERT(DATETIME, '01/01/2000 12:00 PM')

answered Sep 9, 2011 at 20:06

James Johnson

46.1k8 gold badges75 silver badges111 bronze badges

1 Comment

James Johnson Over a year ago

If not, try 12:00:00.000 PM

Wegged · Accepted Answer · 2011-09-09 20:07:03Z

1

@from is declared as varchar you are returning a datetime

answered Sep 9, 2011 at 20:07

Wegged

2,42321 silver badges26 bronze badges

Collectives™ on Stack Overflow

writing a function in sql server

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related