dateformat not as expected

Question

I have the following function below.

It returns a date like Feb 29 2012 10:00PM. Is there a way to make it return the format as 2/29/2012 10:00PM

CREATE FUNCTION scrubDateString 
(
    -- Add the parameters for the function here
    @inputDate varchar(150)
)
RETURNS DATETIME
AS
BEGIN
    -- Declare the return variable here
    DECLARE @Result DATETIME

    -- Add the T-SQL statements to compute the return value here
    DECLARE @tmpDate datetime
    SET @Result = null
    IF (ISDATE(@inputDate)=1)
    BEGIN
        SET @Result = DATEADD(HH, 5, @inputDate)
    END

    -- Return the result of the function
    RETURN @Result
END

how do i take into account utc time, for example this function will be responsible for updating tables in a database. based on where the installer is executed i need for this function to return utc time. — Rod, Feb 29 '12 at 02:34

score 2 · Accepted Answer · answered Feb 28 '12 at 23:13

The function itself is returning a Date and time, "Feb 29 2012 10:00PM" and "2/29/2012 10:00AM" are merely string representations of that date (I am assuming that the AM and PM switch are not relevant to the question and are just a mistake). IF you want to format the date as a particular string look at the CONVERT function. e.g:

DECLARE @Date DATETIME
SET @Date = scrubDateString(@YourString)
SELECT CONVERT(VARCHAR, @Date, 101) + ' ' + RIGHT(CONVERT(VARCHAR, @Date, 0), 7) [Date]

Although I'd strongly advise leaving your date as a date, and doing any formatting on the application side if at all possible. If I have assumed wrong about the AM PM switch you could alter the code above to the following:

DECLARE @Date DATETIME
SET @Date = scrubDateString(@YourString)
IF (DATEPART(HOUR, @Date) > 12)
    BEGIN
        SET @Date = DATEADD(HOUR, -12, @Date)
    END
SELECT CONVERT(VARCHAR, @Date, 101) + ' ' + RIGHT(CONVERT(VARCHAR, @Date, 0), 7) [Date]

It is a column alias. It just means that the column name shows as "Date" not "(No Column Name)" — GarethD, Mar 13 '12 at 15:32

score 1 · Answer 2 · edited May 23 '17 at 12:29

1

If you want to format a DateTime you will need to return it as VARCHAR instead of as a date. If you declare a FUNCTION to return DateTime then it will always return a DateTime formatted in the standard SQL way.

You can return it as a string by using the sql CONVERT function:

RETURN CONVERT(varchar(500), @Result, 101)

A list of formats can be found on msdn. If you want a different format then you need to do something like what's discussed in this question

edited May 23 '17 at 12:29

Community

1
1

answered Feb 28 '12 at 23:11

Greg

3,442
3
29
50

Although, if you're using the function in SQL then the format shouldn't matter. If you're returning the data from SQL to your program then your program should expect a SQL DateTime type and you can do the formatting there – Greg Feb 28 '12 at 23:13

score 1 · Answer 3 · answered Feb 28 '12 at 23:12

just change\add this:

SET @Result = DATEADD(HH, 5, @inputDate)
SET @Result = CONVERT(VARCHAR(10), @Result , 101) + ' ' + RIGHT(CONVERT(VARCHAR, GETDATE(), 100), 7)

you can make it on one line, of course, just did on 2 for clarity

forgot to add that you need to change the return variable to varchar, instead of datetime. If you cant do that, maybe you can add the convert to the places that are calling the function (which would be worst, for sure)

dateformat not as expected

3 Answers3