Change dateformat

Hi Guys,
Is there any way by that we can change default format of date.
ThanksHi
Lookup SET DATEFORMAT command in the BOL.
"Manish Sukhija" <ManishSukhija@.discussions.microsoft.com> wrote in message
news:782D9F1F-B7DB-4C54-84BB-32E9ABACFA9A@.microsoft.com...
> Hi Guys,
> Is there any way by that we can change default format of date.
> Thanks|||Hi Uri,
I've already tried it, but it doesn't work, just have a look what i
wrote...
SET DATEFORMAT mdy
GO
DECLARE @.datevar datetime
SET @.datevar = '12/31/98'
SELECT @.datevar
send me some example, if you can
Thanks
"Uri Dimant" wrote:

> Hi
> Lookup SET DATEFORMAT command in the BOL.
>
> "Manish Sukhija" <ManishSukhija@.discussions.microsoft.com> wrote in messag
e
> news:782D9F1F-B7DB-4C54-84BB-32E9ABACFA9A@.microsoft.com...
>
>|||Oh, I think I understand what you need
Look you can change format of the GETDATE() function by using CONVERT
system function (look at BOL)
However if are going to display the date on the client side ,so it depends
on regional; setting of the workstation thus it is a good practice to use
FORMAT functuin (VB) to display the data in format you want
If it does not help you ,please explain what you are trying to accomplish
along with DDL+ sample data+ expected result
"Manish Sukhija" <ManishSukhija@.discussions.microsoft.com> wrote in message
news:EC5E0DC0-C9A8-435E-A333-4261B4AC23C1@.microsoft.com...
> Hi Uri,
> I've already tried it, but it doesn't work, just have a look what i
> wrote...
> SET DATEFORMAT mdy
> GO
> DECLARE @.datevar datetime
> SET @.datevar = '12/31/98'
> SELECT @.datevar
> send me some example, if you can
> Thanks
> "Uri Dimant" wrote:
>|||Hi uri,
i was thinking same which you told me, but now i'm feeling fine
that i've confermed from you, anyway thanks.
i noticed URI whenever i get into trouble
you always helped me, i admire you, but sometimes i need help urgent, so
could i have your e-mail address, sothat i can mail you directly.
Thanks
"Uri Dimant" wrote:

> Oh, I think I understand what you need
> Look you can change format of the GETDATE() function by using CONVERT
> system function (look at BOL)
> However if are going to display the date on the client side ,so it depend
s
> on regional; setting of the workstation thus it is a good practice to use
> FORMAT functuin (VB) to display the data in format you want
>
> If it does not help you ,please explain what you are trying to accomplish
> along with DDL+ sample data+ expected result
>
> "Manish Sukhija" <ManishSukhija@.discussions.microsoft.com> wrote in messag
e
> news:EC5E0DC0-C9A8-435E-A333-4261B4AC23C1@.microsoft.com...
>
>|||Manish
Thank you for your words , I prefer to answer throu this newsgroup anyway
you have may email when you click on relply to from my post.
"Manish Sukhija" <ManishSukhija@.discussions.microsoft.com> wrote in message
news:65E6D7A1-2467-48DC-A7B8-492AD3AF77CA@.microsoft.com...
> Hi uri,
> i was thinking same which you told me, but now i'm feeling fine
> that i've confermed from you, anyway thanks.
> i noticed URI whenever i get into
> trouble
> you always helped me, i admire you, but sometimes i need help urgent, so
> could i have your e-mail address, sothat i can mail you directly.
> Thanks
> "Uri Dimant" wrote:
>|||Uri Dimant is always helpful to others
Manish, why do you need changing the date format?
You should do the formation at Client side
Madhivanan|||is use this function.. works great..
CREATE FUNCTION [dbo].[FormatDateTime]
(
@.dDate datetime, --Date value to be formatted
@.sFormat varchar(200) --Format for date value
)
RETURNS varchar(200)
AS
BEGIN
/*
Description:
Formats a date to a specific format.
Parameters:
@.dDate datetime
A value or field of datatype datetime or a value or field that can be
explicitly converted to
a datetime datatype.
@.sFormat varchar(200)
Format codes using the characters described below
MMMM or DDDD = the full name for the day or month
MMM or DDD = the first 3 letters of the month or day
MM or DD = the two digit code signifying the month or day
M1 or D1 = the month or day value without a preceding zero
YYYY = a four digit year
YY = a two digit year
hh 2-digit hour 01, 02, ..., 12, 13, ..., 22, 23
nn 2-digit minute 01, 02, ..., 58, 59
ss 2-digit second 01, 02, ..., 58, 59
zzz 3-digit milliseconds 000, 001, ..., 998, 999
AM Use 12-hour (AM/PM) time format
All other characters will not be replaced such as / - . * # a b z x %
and
will show
up in the date in the same relative position that they appear in the
format
parameter.
Examples
select dbo.FormatDateTime('9/21/2001','dddd, mmmm d1, yyyy')
Friday, September 21, 2001
select dbo.FormatDate('9/21/2001','mm/dd/yyyy')
09/21/2001
select dbo.FormatDate('9/21/2001','mm-dd-yyyy')
09/21/2001
select dbo.FormatDate('9/21/2001','yyyymmdd')
20010921
select dbo.FormatDate('9/5/2001','m1/d1/yy')
9/5/01
select dbo.FormatDate('9/21/2001','mmm-yyyy')
Sep-2001
*/
-- Insert the Month
set @.sFormat = replace(@.sFormat,'MMMM',datename(month,@.
dDate))
set @.sFormat =
replace(@.sFormat,'MMM',convert(char(3),d
atename(month,@.dDate)))
set @.sFormat =
replace(@.sFormat,'MM',right(convert(char
(4),@.dDate,12),2))
set @.sFormat =
replace(@.sFormat,'M1',convert(varchar(2)
,convert(int,right(convert(char(4),@.
dDate,12),2))))
-- Insert the Day
set @.sFormat = replace(@.sFormat,'DDDD',datename(wday
,@.dDate))
set @.sFormat =
replace(@.sFormat,'DDD',convert(char(3),d
atename(wday,@.dDate)))
set @.sFormat =
replace(@.sFormat,'DD',right(convert(char
(6),@.dDate,12),2))
set @.sFormat =
replace(@.sFormat,'D1',convert(varchar(2)
,convert(int,right(convert(char(6),@.
dDate,12),2))))
-- Insert the Year
set @.sFormat = replace(@.sFormat,'YYYY',convert(char(4),
@.dDate,112))
set @.sFormat = replace(@.sFormat,'YY',convert(char(2),@.d
Date,12))
-- Insert Minutes, Seconds and Milliseconds
set @.sFormat = replace(@.sFormat, 'NN', RIGHT(CONVERT(char(16), @.dDate,
126), 2))
set @.sFormat = replace(@.sFormat, 'SS', RIGHT(CONVERT(char(19), @.dDate,
126), 2))
set @.sFormat = REPLACE(@.sFormat, 'ZZZ', RIGHT(CONVERT(char(24), @.dDate,
109), 3))
-- Check if the user wants 12-hour or 24-hour formatting
IF PATINDEX('%AM%', @.sFormat) > 0
BEGIN
/* 12-hour format (AM/PM) desired */
set @.sFormat = REPLACE(@.sFormat, 'HH',
CAST(CAST(RIGHT(CONVERT(char(14),
@.dDate, 0), 2) AS int) AS varchar(2)))
set @.sFormat = REPLACE(@.sFormat, 'AM', RIGHT(CONVERT(char(26),
@.dDate,
109), 2))
END
ELSE
BEGIN
/* 24-hour format desired */
set @.sFormat = REPLACE(@.sFormat, 'HH', DATEPART(hh, @.dDate))
END
RETURN @.sFormat
END|||On 19 Dec 2005 16:36:00 -0800, CJ wrote:

>is use this function.. works great..
Hi CJ,
But it comes at a price. It's much slower than using the normal string
handling and conversion functions.
I compared performance of your function against alternative code. The
test data was my calendar table, which holds all dates from 2000-01-01
up to and including 2029-12-31 (a total of 10958 rows).
I started with a basic test, using the YYYYMMDD format:
DECLARE @.start datetime
SET @.start = getdate()
SELECT dbo.FormatDateTime(c.dt, 'YYYYMMDD') AS x
INTO #dummy1
FROM dbo.Calendar AS c
SELECT DATEDIFF(ms, @.start, getdate())
go
DECLARE @.start datetime
SET @.start = getdate()
SELECT CONVERT(char(8), c.dt, 112) AS x
INTO #dummy2
FROM dbo.Calendar AS c
SELECT DATEDIFF(ms, @.start, getdate())
Both produce the exact same output (I select into a temp table to avoid
the overhead of sending data over a network and disaplying it on a
screen, but I did check the results later). With your function, elapsed
time was 1788 ms (average of two executions with hot cache); the regular
CONVERT function used 78 ms.
Next test was more complex: MMM-YYYY. The test script was basically
unchanged, except for these lines:
SELECT dbo.FormatDateTime(c.dt, 'MMM-YYYY') AS x
(...)
SELECT REPLACE(RIGHT(CONVERT(char(11), c.dt, 106), 8), ' ', '-') AS x
Test results: function used 1788 ms, expression with nested calls to
builtin functions took 102 ms.
Final test was very complicated: DDDD, MMMM D1, YYYY:
SELECT dbo.FormatDateTime(c.dt, 'DDDD, MMMM D1, YYYY') AS x
(...)
SELECT DATENAME(wday, c.dt) + ', '
+ DATENAME(month, c.dt) + ' '
+ RIGHT(CONVERT(char(12), c.dt, 107), 8) AS x
Function took 1850 ms; complex expression with builtins 110 ms.
The function you posted can be a great time-saver if you have little
time to write the code (though it's not exactly rocket science to
manipulate the date format using builtin functions). But don't ever use
it in time-critical queries!
Oh, and before I forget: formatting should better be handled on the
client! :-]
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Hugo-
Yes.. It tried to answer the initial question and actually, I use this
when returning xml data via for XML EXPLICIT to avoid any extra
processing on the server but after seeing your numbers, I might be
scrapping it. FYI: This function was taken off the web so I don't
claim to be the author. ;)
Regards-
Thanks for your advice!