UDF for BOTH Date & Time

Question

Post reply

UDF for BOTH Date & Time

Anthony Lukindo

SSC-Addicted

Points: 407
More actions
July 29, 2004 at 2:27 pm

#112101

Hi:
A VB-Syntax-equivalent UDF to extract and format date was recently posted.
. . . But Alas! This function stopped with Date Formatting! . . . time was skipped!!
Has anyone done a full UDF that does both Date & Time?
Something that would work with:
SELECT dbo.udf_FormatDateTime ([TDate], 'HH:MM:SS yyyy/mm/dd')
I can augment the UDF to include Time ---but just in-case someone has already done that p/se advise
Regards
Anthony

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Jeff Burton SSCommitted Points: 1551 More actions · Answer 1

Here are 2 UDF's I contributed awhile ago to sqlservercentral.com. They should be in the "scripts" section somewhere.

RJZeroN is used to zero-fill/right-justify an integer value which is used by date/time formatting UDF FormatDate.

CREATE FUNCTION dbo.RJZeroN

--

-- Author: Jeff Burton

-- Date: 03/24/2004

-- Name: Function dbo.RJZeroN

-- Purpose: Right-justify and zero fill the passed integer value @InValue

-- into a character string (up to 20 characters to hold the

-- largest SQL Server integer value) of passed size @Length. If

-- value cannot be formatted NULL is returned.

--

-- Return Value: VARCHAR(20)

--

(

@InValue BIGINT

,@Length TINYINT

)

RETURNS VARCHAR(20)

AS

BEGIN

DECLARE @ReturnValue VARCHAR(20),

@InValueLen INT,

@InValueSignChar VARCHAR(1),

@InValueSignCharLen INT

SET @InValueLen = LEN(@InValue)

IF @InValue < 0

SET @InValueSignChar = '-'

ELSE

SET @InValueSignChar = ''

SET @InValueSignCharLen = LEN(@InValueSignChar)

SET @ReturnValue =

CASE

WHEN (ISNULL(@Length,0) NOT BETWEEN 1 AND 20)

OR (@InValueLen > @Length) OR (@InValue IS NULL)

/* invalid length or input value argument */

THEN NULL

ELSE

@InValueSignChar

+REPLICATE('0',@Length - @InValueLen)

+CAST(ABS(@InValue) AS VARCHAR(20))

END

RETURN @ReturnValue

END

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

CREATE FUNCTION dbo.FormatDate

--

-- Author: Jeff Burton

-- Date: 02/17/2004

-- Name: Function dbo.FormatDate

-- Purpose: Format passed date according to passed date formatting

-- string. This function mimics the functionality of

-- Oracle PL/SQL function "to_char" for date formatting.

-- -------------

-- Restrictions:

-- -------------

--

-- (1) Date parts are right justified and zero-filled. (The Oracle PL/SQL

-- "to_char" function does not do this but this format should be more

-- consistent/useful.)

--

-- (2) The following date format codes are not handled (but this s.p.

-- can be modified for them if needed): CC, SCC, E, EE, IYY, IY, I, J, RM, RR, RRRR,

-- SSSSS, TZD, TZH, TZM, TZR, "Y,YYY", YEAR, SYEAR, SYYYY, W, YYY.

--

-- (3) Where there is a common date format code common to Oracle PL/SQL and

-- SQL2K T-SQL, date formatting for T-SQL is used:

--

-- DAY (T-SQL format "WEEKDAY" or "DW" should be used instead.)

-- DY (T-SQL "contrived" format "DAYAB" should be used instead.)

--

-- HH (If AM/A.M./PM/P.M. is in the @dateformatstring this is same as

-- T-SQL format "hh". T-SQL "contrived" format "HH12" should be used

-- instead.)

--

-- MONTH (T-SQL "contrived" format "monthfull" should be used instead.)

--

-- (4) T-SQL date formats m/n/d/s/y/day are not supported for use by this function

-- because these letter appear in month or day names. Workaround: Use

-- mm/mi/dd/ss/dy/dd codes respectively instead.

--

-- ----------------------------------

-- Valid Date Formatting Codes

-- (return same values as T-SQL

-- func. DATEPART except where noted)

-- ----------------------------------

--

-- Code

-- -------------

-- year, yy, yyyy

-- quarter, qq, q

-- month, mm

-- mon: Contrived. Returns the month name abbrev (e.g. Jan).

-- monthfull: Contrived. Returns the month name (e.g. January).

-- dayofyear dy

-- dd

-- dayab Contrived. Returns the day name abbrev (e.g. Sun).

-- week, wk, ww

-- weekday, dw

-- hour, hh

-- hh24 Contrived for Oracle date format "hh24".

-- hh12 Contrived for Oracle date format "hh12".

-- minute, mi

-- second, ss

-- hs Contrived for hundredths of seconds

-- millisecond, ms

--

-- Modifications:

--

-- 05/26/04 JBB Edit the meridian (AM/PM) based on the hours returned

-- from DATEPART. (E.g., If hours is 13+ but 'AM' is in

-- date format spec replace it with 'PM'.)

--

(

@InputDate DATETIME

,@DateFormatString NVARCHAR(80)

)

RETURNS NVARCHAR(80)

AS

BEGIN

DECLARE @FormattedDate NVARCHAR(80),

@HH12Hours VARCHAR(2),

@MeridianSpecified BIT,

@Meridian VARCHAR(2)

SET @FormattedDate = CAST(@DateFormatString AS NVARCHAR(80))

IF

CHARINDEX('AM',@DateFormatString) > 0

OR CHARINDEX('A.M.',@DateFormatString) > 0

OR CHARINDEX('PM',@DateFormatString) > 0

OR CHARINDEX('P.M.',@DateFormatString) > 0

BEGIN

SET @MeridianSpecified=1

END

ELSE

BEGIN

SET @MeridianSpecified=0

END

IF DATEPART(hh,@InputDate) <= 12

BEGIN

SET @HH12Hours = dbo.RJZeroN(DATEPART(hh,@InputDate),2)

SET @Meridian='AM'

END

ELSE

BEGIN

SET @HH12Hours = dbo.RJZeroN

(DATEPART(hh,@InputDate) - 12 ,2 )

SET @Meridian='PM'

END

SET @FormattedDate = REPLACE(@FormattedDate,'dayofyear',

dbo.RJZeroN(DATENAME(dayofyear,@InputDate),3))

/* 'ddd' is an Oracle PL/SQL format code (same as T-SQL 'dayofyear' code). */

SET @FormattedDate = REPLACE(@FormattedDate,'ddd',

dbo.RJZeroN(DATENAME(dayofyear,@InputDate),3))

/* 'dayab' is contrived replacement for Oracle PL/SQL format 'dy'. */

SET @FormattedDate = REPLACE(@FormattedDate,'dayab',

SUBSTRING(DATENAME(weekday,@InputDate),1,3))

SET @FormattedDate = REPLACE(@FormattedDate,'monthfull',

DATENAME(month,@InputDate))

SET @FormattedDate = REPLACE(@FormattedDate,'month',

dbo.RJZeroN(DATEPART(month,@InputDate),2))

SET @FormattedDate = REPLACE(@FormattedDate,'mm',

dbo.RJZeroN(DATEPART(month,@InputDate),2))

SET @FormattedDate = REPLACE(@FormattedDate,'mon',

SUBSTRING(DATENAME(month,@InputDate),1,3))

SET @FormattedDate = REPLACE(@FormattedDate,'year',

dbo.RJZeroN(DATENAME(year,@InputDate),4))

SET @FormattedDate = REPLACE(@FormattedDate,'yyyy',

dbo.RJZeroN(DATENAME(year,@InputDate),4))

/* 'Iyyy' is an Oracle PL/SQL format code (same as T-SQL code 'yyyy'). */

SET @FormattedDate = REPLACE(@FormattedDate,'Iyyy',

dbo.RJZeroN(DATENAME(year,@InputDate),4))

SET @FormattedDate = REPLACE(@FormattedDate,'yy',

dbo.RJZeroN(DATENAME(year,@InputDate),4))

SET @FormattedDate = REPLACE(@FormattedDate,'quarter',

dbo.RJZeroN(DATENAME(quarter,@InputDate),2))

SET @FormattedDate = REPLACE(@FormattedDate,'qq',

dbo.RJZeroN(DATENAME(quarter,@InputDate),2))

SET @FormattedDate = REPLACE(@FormattedDate,'q',

dbo.RJZeroN(DATENAME(quarter,@InputDate),2))

SET @FormattedDate = REPLACE(@FormattedDate,'dd',

dbo.RJZeroN(DATENAME(day,@InputDate),2))

SET @FormattedDate = REPLACE(@FormattedDate,'dy',

dbo.RJZeroN(DATENAME(dayofyear,@InputDate),3))

SET @FormattedDate = REPLACE(@FormattedDate,'weekday',

DATENAME(weekday,@InputDate))

SET @FormattedDate = REPLACE(@FormattedDate,'dw',

DATENAME(weekday,@InputDate))

SET @FormattedDate = REPLACE(@FormattedDate,'week',

dbo.RJZeroN(DATENAME(week,@InputDate),2))

SET @FormattedDate = REPLACE(@FormattedDate,'wk',

dbo.RJZeroN(DATENAME(week,@InputDate),2))

SET @FormattedDate = REPLACE(@FormattedDate,'ww',

dbo.RJZeroN(DATENAME(week,@InputDate),2))

/* 'Iw' is an Oracle PL/SQL format code (same as T-SQL format code 'dw'). */

SET @FormattedDate = REPLACE(@FormattedDate,'Iw',

DATENAME(weekday,@InputDate))

/* 'hh24' is an Oracle PL/SQL format code (same as T-SQL 'hh' code). */

SET @FormattedDate = REPLACE(@FormattedDate,'hh24',

dbo.RJZeroN(DATENAME(hour,@InputDate),2))

/* 'hh12' is an Oracle PL/SQL format code. */

SET @FormattedDate = REPLACE(@FormattedDate,'hh12',

@HH12Hours)

IF @MeridianSpecified=0

BEGIN

SET @FormattedDate = REPLACE(@FormattedDate,'hour',

dbo.RJZeroN(DATENAME(hour,@InputDate),2))

SET @FormattedDate = REPLACE(@FormattedDate,'hh',

dbo.RJZeroN(DATENAME(hour,@InputDate),2))

END

ELSE

BEGIN

SET @FormattedDate = REPLACE(@FormattedDate,'hour',

@HH12Hours)

SET @FormattedDate = REPLACE(@FormattedDate,'hh',

@HH12Hours)

END

SET @FormattedDate = REPLACE(@FormattedDate,'minute',

dbo.RJZeroN(DATENAME(minute,@InputDate),2))

SET @FormattedDate = REPLACE(@FormattedDate,'millisecond',

dbo.RJZeroN(DATENAME(millisecond,@InputDate),3))

SET @FormattedDate = REPLACE(@FormattedDate,'mi',

dbo.RJZeroN(DATENAME(minute,@InputDate),2))

SET @FormattedDate = REPLACE(@FormattedDate,'second',

dbo.RJZeroN(DATENAME(second,@InputDate),2))

SET @FormattedDate = REPLACE(@FormattedDate,'ss',

dbo.RJZeroN(DATENAME(second,@InputDate),2))

SET @FormattedDate = REPLACE(@FormattedDate,'hs',

dbo.RJZeroN

(ROUND(DATEPART(millisecond,@InputDate),-1)/10 ,2)

)

SET @FormattedDate = REPLACE(@FormattedDate,'ms',

dbo.RJZeroN(DATENAME(millisecond,@InputDate),3))

/* 'ff' is an Oracle PL/SQL format code (same as T-SQL 'ms' code). */

SET @FormattedDate = REPLACE(@FormattedDate,'ff',

dbo.RJZERON(DATENAME(millisecond,@InputDate),3))

/* Adjust meridian if necessary */

IF @MeridianSpecified=1

BEGIN

SET @FormattedDate = REPLACE(@FormattedDate,'AM',@Meridian)

SET @FormattedDate = REPLACE(@FormattedDate,'A.M.',@Meridian)

SET @FormattedDate = REPLACE(@FormattedDate,'PM',@Meridian)

SET @FormattedDate = REPLACE(@FormattedDate,'P.M.',@Meridian)

END

RETURN @FormattedDate

END

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

Anthony Lukindo SSC-Addicted Points: 407 More actions · Answer 2

Anthony Lukindo

SSC-Addicted

Points: 407

August 2, 2004 at 11:17 pm

#517373

Thanks the two script work perfectly!!!

Anthony

Jeff Burton SSCommitted Points: 1551 More actions · Answer 3

Jeff Burton

SSCommitted

Points: 1551

August 3, 2004 at 5:48 am

#517417

Great! Glad I could help out.

Jeff

webrunner SSC-Dedicated Points: 31865 More actions · Answer 4

Jeff,

I'm late to the party but I found your functions after a lot of searching in vain. Thanks! This function does exactly what I need - return a datetime formatted like '06/11/2007 05:50 PM'. It is amazing that there is no simple way to do this in SQL Server.

Thanks again,

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

Ok... first the "party line" This type of formatting should NOT be done in SQL Server... it should be done in the GUI ... if you have one...

That, not-with-standing, I'll throw my hat into the ring with the other Jeff... telephone guys will like the "6SecInc" part a lot... read the comments for all the possibilities...

CREATE FUNCTION dbo.fnFormatDateTime

/*************************************************************************************************

Purpose: This function reformats a DateSerial based on the content of a format string.

Date: 03/10/2004 (Full rewrite of all code collections and snippets)

Author: Jeffrey B. Moden

--------------------------------------------------------------------------------------------------

Description:

Two categories of functions exist: Elapsed time and DateTime formatting. The Elapsed time format

functions will not allow DateTime formatting to occur and should probably be used individually.

See the comments in each section to determine what format strings are available and what their

expected output should be. Characters not part of predefined formats are faithfully replicated

in the output.

--------------------------------------------------------------------------------------------------

Usage: dbo.fnFormatDateTime(dateserial or elapsed time in fractional days,'formatstring')

--------------------------------------------------------------------------------------------------

Examples:

The following example returns an elapsed time or "duration" formatted in decimal minutes.

SELECT dbo.fnFormatDateTime(@EndDateTime-@StartDateTime,'NNN.N')

The following example returns an elapsed time or "duration" formatted for telephone billing and

has been rounded up to the nearest 6 seconds (6 second incremental billing).

SELECT dbo.fnFormatDateTime(@CallEndDateTime-@CallStartDateTime,'6SECINC')

The following example returns an elapsed time or "duration" formatted for telephone billing and

has been rounded up to the nearest minute (whole minute billing).

SELECT dbo.fnFormatDateTime(@CallEndDateTime-@CallStartDateTime,'RNN')

The following examples return custom dates and times as indicated in the format string.

SELECT dbo.fnFormatDateTime(GETDATE(),'DAY, MONTH ?D YYYY')

...returns (for example) Wednesday, March 10 2004

SELECT dbo.fnFormatDateTime(GETDATE(),'?YJJJ JULIAN')

...returns (for example) 4070 JULIAN

SELECT dbo.fnFormatDateTime(GETDATE(),'THE CURRENT DATE & TIME IS: MM/DD/YY HH:NN:SS.TTT PM')

...returns (for example) THE CURRENT DATE & TIME IS: 03/04/04 11:22:01.153 AM

SELECT dbo.fnFormatDateTime(GETDATE(),'THE CURRENT TIME IS: 24HNN hours military time, HH:NN PM civilian time')

...returns (for example) THE CURRENT TIME IS: 2326 hours military time, 11:26 PM civilian time

--------------------------------------------------------------------------------------------------

Revisions:

Rev 0 - 03/10/2004 - JBM - Convert old collection of stored procedures and code snippets to a

single function which combines all of the individual formatting

techinques developed over the years, into a single ultra-flexible

Date and Time formatting function.

Rev 1 - 04/10/2005 - JBM - Ticket QA 13950 - The 'DDY' format-key for Julian days interferes with

the ability to correctly produce the standard 'mmddyy' and 'mmddyyyy'

formats. Searched system tables for usage of the 'DDY' format and none

currently exists so repairs will not break any other code. Changed the

'DDY' format key to 'JJJ' and the '?DY' format key to '?JJ'.

Rev 2 - 04/12/2005 - JBM - Ticket QA 13950 - Changed the order of precedence for a "?" funtions so

that they could be used with their counterparts without spaces and

still work correctly.

*************************************************************************************************/

(

@DateSerial DATETIME, --Holds the time duration or date/time input

@FMTString VARCHAR(250) --Holds the format type indicator

)

RETURNS VARCHAR(250) --Defines the datatype of the output (return)

AS

BEGIN

--===== Presets ==================================================================================

DECLARE @fmtCASE INT --Used to determine "Elapsed Time" cases in format

SET @fmtCASE =

CHARINDEX('HHH.H' ,@FMTString)

+CHARINDEX('HHH' ,@FMTString)

+CHARINDEX('NNN.N' ,@FMTString)

+CHARINDEX('NNN' ,@FMTString)

+CHARINDEX('RNN' ,@FMTString)

+CHARINDEX('6SECINC',@FMTString)

+CHARINDEX('SECDUR' ,@FMTString)

-- If any of the above formats exist in the format string,

-- then @fmtCASE will be greater than 0 and only

-- elapsed time calculations will be replaced on the format line.

IF @fmtCASE > 0

BEGIN

--===== Elapsed time calculations =================================================================

-- These calculations will override all non-elapsed time formats. The format characters for

-- non-elapsed time formats will show in the result rather than being converted

SET @FMTString =

REPLACE(

@FMTString

--===== Each conversion is this sub-section must have a REPLACE( associated with it

-- and must follow the format of ,'formatchars',conversionformula)

-- Be careful when adding new functionality so as not to destroy the order

-- sensitivity or the ability to have non-format plain-text characters in

-- the format string.

-- Rounded decimal hours

,'HHH.H' ,CONVERT(VARCHAR(28), --Convert numbers to text

CONVERT(DECIMAL(28,1), --Does the formatting

(CONVERT(FLOAT,@DateSerial)*24.0)))) --Calcs decimal minutes

-- Unrounded whole hours

,'HHH' ,CONVERT(VARCHAR(8),DateDIFF(HH,0,@DateSerial)))

-- Rounded decimal minutes

,'NNN.N' ,CONVERT(VARCHAR(28), --Convert numbers to text

CONVERT(DECIMAL(28,1), --Does the formatting

(CONVERT(FLOAT,@DateSerial)*24.0*60.0)))) --Calcs decimal minutes

-- Unrounded whole minutes

,'NNN' ,CONVERT(VARCHAR(8),DateDIFF(MI,0,@DateSerial)))

-- Whole minutes rounded UP

,'RNN' ,CONVERT(VARCHAR(28), --Convert numbers to text

CONVERT(DECIMAL(28,0), --Does the formatting

(CONVERT(FLOAT,@DateSerial)*24.0*60.0) --Calcs decimal minutes

+.49999))) --Rounds up to nearest minute

-- Decimal minutes rounded up to 6 second increments

,'6SECINC',CONVERT(VARCHAR(28), --Convert numbers to text

CONVERT(DECIMAL(28,1), --Does the formatting

(CONVERT(FLOAT,@DateSerial)*24.0*60.0) --Calcs decimal minutes

+.049999))) --Rounds up to nearest tenth

-- Unrounded whole seconds

,'SECDUR' ,CONVERT(VARCHAR(28), --Convert numbers to text

ROUND(DateDIFF(SS,0,@DateSerial),0))) --Returns duration as seconds

END

ELSE

--===== Date and Time formatting functions ========================================================

-- These "non-elapsed time" functions replace the formatting characters indicated in quotes

-- with the appropriate character values except when any of the "Elapsed Time" formatting

-- functions are present in the formatting string.

BEGIN

SET @FMTString =

REPLACE(

@FMTString

--===== Each conversion is this sub-section must have a REPLACE( associated with it

-- and must follow the format of ,'formatchars',conversionformula)

-- Be careful when adding new functionality so as not to destroy the order

-- sensitivity or the ability to have non-format plain-text characters in

-- the format string.

-- Long (full) month name

,'MONTH' ,DATENAME(MM,@DateSerial))

-- 1 or 2 digit month number without zero fill

,'?M' ,DATEPART(MM,@DateSerial))

-- 3 character abbreviated month name

,'MMM' ,LEFT(DATENAME(MM,@DateSerial),3))

-- 2 digit month number with 0 fill for months <10

,'MM' ,RIGHT('00'+CONVERT(VARCHAR(2),DATEPART(MM,@DateSerial)),2))

-- 1, 2, or 3 digit day of year (Julian) without zero fill

,'?JJ' ,DATENAME(DY,@DateSerial))

-- 3 digit day of year (Julian) with zero fill

,'JJJ' ,RIGHT('000'+DATENAME(DY,@DateSerial),3))

-- Long (full) day name

,'DAY' ,DATENAME(DW,@DateSerial))

-- 1 or 2 digit day of month number without zero fill

,'?D' ,DATEPART(DD,@DateSerial))

-- 3 character abbreviated day name

,'DDD' ,LEFT(DATENAME(DW,@DateSerial),3))

-- 2 digit day of month number with 0 fill for days <10

,'DD' ,RIGHT('00'+CONVERT(VARCHAR(2),DATEPART(DD,@DateSerial)),2))

-- 1 digit year number for Julian type dates

,'?Y' ,RIGHT(DATENAME(YY,@DateSerial),1))

-- 4 digit year number

,'YYYY' ,DATENAME(YY,@DateSerial))

-- 2 digit year number with 0 fill for years <10

,'YY' ,RIGHT(DATENAME(YY,@DateSerial),2))

-- 2 digit 0 filled hours for 24 hour clock

,'24H' ,RIGHT('00'+DATENAME(HH,@DateSerial),2))

-- 1 or 2 digit hours for 12 hour clock without 0 fill

,'?H' ,CASE

WHEN DATENAME(HH,@DateSerial)>12

THEN DATENAME(HH,@DateSerial-.5)

ELSE DATENAME(HH,@DateSerial)

END)

-- 2 digit 0 filled hours for 12 hour clock

,'HH' ,CASE

WHEN DATENAME(HH,@DateSerial)>12

THEN RIGHT('00'+DATENAME(HH,@DateSerial-.5),2)

ELSE RIGHT('00'+DATENAME(HH,@DateSerial),2)

END)

-- 2 digit 0 filled minutes

,'NN' ,RIGHT('00'+DATENAME(MI,@DateSerial),2))

-- 2 digit 0 filled seconds

,'SS' ,RIGHT('00'+DATENAME(SS,@DateSerial),2))

-- 2 digit 0 filled milli-seconds

,'TTT' ,RIGHT('000'+DATENAME(MS,@DateSerial),3))

-- AM or PM indicator based on time of day

,'PM' ,RIGHT(CONVERT(CHAR(19),@DateSerial,100),2))

-- 1 or 2 digit week of year

,'?W' ,DATENAME(WW,@DateSerial))

-- 2 digit 0 filled week of year

,'WW' ,RIGHT('00'+DATENAME(WW,@DateSerial),2))

-- 1 digit quarter of year

,'?Q' ,DATENAME(QQ,@DateSerial))

END

--===== Return the reformatted format string and exit =============================================

RETURN @FMTString --Return the answer as output of function

END

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

webrunner SSC-Dedicated Points: 31865 More actions · Answer 6

Hello Jeff,

Yes, I agree that the formatting should be done in a GUI if possible, or at least that the outgoing datetime should not have to be formatted in order to be processed at the other end. But I want to be prepared for the possible request.

And also, given that SQL Server already has a set of date conversion formats available via the CONVERT function, I don't see why they didn't include 'mm/dd/yyyy hh:mm:ss AM/PM' as an option - unless I missed it.

Thanks,

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

No... you didn't miss it... have always wondered why they went from minutes right straight to milliseconds my own self... for that matter, why didn't they just include a programmable date format?

Heh... I agree 10,000%... being prepared is a good thing... before I (finally) convinced the folks at work about formatting in the GUI, they were talking about hardcoding all sorts of really strange formats in about 40 different functions... so, like the other Jeff, I made one that was "programmable" to cover just about anything they could come up with including non-date/time related words (with some restrictions). I figured if I couldn't change their mind about the formatting, at least I'd be prepared to limit the amount of damage they'd do

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Burton SSCommitted Points: 1551 More actions · Answer 8

Amazing how some of these threads live on!

I originally wrote my FormatDate udf as we migrated from Oracle (which had the "to_char" function for this). It's not the fastest UDF, but it was useful at the time.

I agree with Jeff M. that the GUI app should have functionality for this and should be used instead. We use Cold Fusion here and it has better date/time formatting than the SQL 2K built-in fuctions. I don't know if date/time formatting was improved in SQL 2005 since I'm not using that yet.

Jeff B.

webrunner SSC-Dedicated Points: 31865 More actions · Answer 9

Thanks, Jeff!

I haven't had a chance to install your version, and luckily it looks like I will be able to allow the default SQL datetime formatting and the calling script will figure out the date at that end.

But I agree with you about formatting in the GUI. Applications should be built to take the basic SQL datetime format as is, given that it comes that way and as a result will also mean fewer unexpected results.

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

webrunner SSC-Dedicated Points: 31865 More actions · Answer 10

Hello Jeff,

Thanks for your function - I am very happy these threads live on!

I think you are right - SQL datetime formatting hasn't changed much as far as I know, even in SQL 2005. And, yes, ColdFusion has a nice collection of formatting functions - just the kind of stuff the other Jeff (Moden) was talking about when he said to format dates in the GUI.

Thanks again,

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 11

Just a bit of a follow up on the suggestion that dates and times be formatted by the GUI...

If you have an international presence, many GUI's can be "programmed" to respond to the local settings for whatever region of the world you're in. If you hard code it in SQL, you're stuck with either the wrong format or the GUI has to convert what you've provided back to a real date/time and then convert/format it for the correct display according to the local regional settings on any given PC. Formatting dates and times in SQL for returns to a GUI is a really, really bad idea.

When it comes to batch processes that create CSV and other text based files, all bet's are off. Those types of files are usually pretty stringent as to what the date format will be. If the 3rd party that designed those files is smart, they'll insist on the ISO format (yyyymmdd hh:mi:ss {24 hour time}) for dates and times because everyone can easily convert that and most systems (if not all) recognize that format with no conversion required.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

JacekO SSCertifiable Points: 6610 More actions · Answer 12

Another reason to format the datetime on the GUI is sorting. If you return a dataset with a datetime column and you present it in the data grid you have the sorting included for free. If you return the data formated as a string you have to go thru extra steps of converting and hiding the data in order to present it properly.

The software app we work on is for both US and international markets and we do all the formatting (numbers, dates and time) on the GUI. The database stores the settings and we use them to modify the registry when the app is started so the formatting does not require any extra coding on the GUI part.

PS. We can afford to mess with the regional registry settings because the app is the only app on the rig.

---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]

Anthony Lukindo SSC-Addicted Points: 407 More actions · Answer 13

Hi:

... The suggestion to do GUI-side formatting for date field is well taken. However, if MS SQL Server does provide a date formatting function then such a function should cover all formatting scenarios rather than being half-cooked.

One thing is that the formatt: "YYYY\MM\DD hh:mm:ss.s" has a big and critical advantage of being sortable as a string, and yet this was not considered in MS SQL Server.

Anthony

Sergiy SSC Guru Points: 110208 More actions · Answer 14

1) Format datetime using style 120.

2) Replace "-" with "\"

3) Take as many LEFT characters as you need for requested format.

4) Minimal thinking effort is always useful in programming.

_____________
Code for TallyGenerator