CODEWITHSUNDEEP
sqlsql-server-2008
user838359
user838359

Reputation: 173

writing a function in sql server

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?

Upvotes: 0

Views: 1110

Answers (3)

Hogan
Hogan

Reputation: 70538

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 ')

Upvotes: 1

Wegged
Wegged

Reputation: 2423

@from is declared as varchar you are returning a datetime

Upvotes: 1

James Johnson
James Johnson

Reputation: 46067

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')

Upvotes: 1

Related Questions