Change default Dateformat in SQL Server 2005

Can I change the default Dateformat in SQL Server 2005 ?
When I list all sys.syslanguages data, I can see that the dateformat for
French (langid = 2) is set to 'dmy' but I want to set it to 'ymd'.
I know that I can use SET DATEFORMAT YMD in my storeproc and I still know
that I can use Convert with my date but... I don't want to change all of my
existaing code.
Is this possible ?
ThanksYou can configure the default language, which also changes the default
date format, but you cannot configure the default date format without
changing the default language. So if you want to use the French
language, but the ymd date format, I see no other way than using SET
DATEFORMAT (in all your procedures or directly when you open the
connection).
Razvan|||Directly when you open the connection ?
What you mean ? A kind of trigger ?
Regard
Vince
"Razvan Socol" wrote:

> You can configure the default language, which also changes the default
> date format, but you cannot configure the default date format without
> changing the default language. So if you want to use the French
> language, but the ymd date format, I see no other way than using SET
> DATEFORMAT (in all your procedures or directly when you open the
> connection).
> Razvan
>|||Vincent Bernier wrote:
> Directly when you open the connection ?
> What you mean ? A kind of trigger ?
No. When you open the connection from your application, simply execute
a "SET DATEFORMAT ymd" (without any procedure or trigger). It should
remain in effect for all subsequent batches in the same connection,
unless another SET DATEFORMAT is encountered in your code.
If you execute SET DATEFORMAT in a procedure, it will remain in effect
for the scope of that procedure and the procedures/triggers called by
it, but it will be reset to the previous value when the procedure ends.
Using SET DATEFORMAT is not a very safe thing to do if you really
depend on that date format for specifying datetime values, so I
recommend that you use datetime constants specified in a neutral way
(which works regardless of the date format setting). See:
http://www.karaszi.com/SQLServer/info_datetime.asp
Razvan|||Thank, the ref site will help me.
"Razvan Socol" wrote:

> Vincent Bernier wrote:
> No. When you open the connection from your application, simply execute
> a "SET DATEFORMAT ymd" (without any procedure or trigger). It should
> remain in effect for all subsequent batches in the same connection,
> unless another SET DATEFORMAT is encountered in your code.
> If you execute SET DATEFORMAT in a procedure, it will remain in effect
> for the scope of that procedure and the procedures/triggers called by
> it, but it will be reset to the previous value when the procedure ends.
> Using SET DATEFORMAT is not a very safe thing to do if you really
> depend on that date format for specifying datetime values, so I
> recommend that you use datetime constants specified in a neutral way
> (which works regardless of the date format setting). See:
> http://www.karaszi.com/SQLServer/info_datetime.asp
> Razvan
>

Change default Dateformat in SQL Server 2005

Can I change the default Dateformat in SQL Server 2005 ?
When I list all sys.syslanguages data, I can see that the dateformat for
French (langid = 2) is set to 'dmy' but I want to set it to 'ymd'.
I know that I can use SET DATEFORMAT YMD in my storeproc and I still know
that I can use Convert with my date but... I don't want to change all of my
existaing code.
Is this possible ?
Thanks
You can configure the default language, which also changes the default
date format, but you cannot configure the default date format without
changing the default language. So if you want to use the French
language, but the ymd date format, I see no other way than using SET
DATEFORMAT (in all your procedures or directly when you open the
connection).
Razvan
|||Directly when you open the connection ?
What you mean ? A kind of trigger ?
Regard
Vince
"Razvan Socol" wrote:

> You can configure the default language, which also changes the default
> date format, but you cannot configure the default date format without
> changing the default language. So if you want to use the French
> language, but the ymd date format, I see no other way than using SET
> DATEFORMAT (in all your procedures or directly when you open the
> connection).
> Razvan
>
|||Vincent Bernier wrote:
> Directly when you open the connection ?
> What you mean ? A kind of trigger ?
No. When you open the connection from your application, simply execute
a "SET DATEFORMAT ymd" (without any procedure or trigger). It should
remain in effect for all subsequent batches in the same connection,
unless another SET DATEFORMAT is encountered in your code.
If you execute SET DATEFORMAT in a procedure, it will remain in effect
for the scope of that procedure and the procedures/triggers called by
it, but it will be reset to the previous value when the procedure ends.
Using SET DATEFORMAT is not a very safe thing to do if you really
depend on that date format for specifying datetime values, so I
recommend that you use datetime constants specified in a neutral way
(which works regardless of the date format setting). See:
http://www.karaszi.com/SQLServer/info_datetime.asp
Razvan
|||Thank, the ref site will help me.
"Razvan Socol" wrote:

> Vincent Bernier wrote:
> No. When you open the connection from your application, simply execute
> a "SET DATEFORMAT ymd" (without any procedure or trigger). It should
> remain in effect for all subsequent batches in the same connection,
> unless another SET DATEFORMAT is encountered in your code.
> If you execute SET DATEFORMAT in a procedure, it will remain in effect
> for the scope of that procedure and the procedures/triggers called by
> it, but it will be reset to the previous value when the procedure ends.
> Using SET DATEFORMAT is not a very safe thing to do if you really
> depend on that date format for specifying datetime values, so I
> recommend that you use datetime constants specified in a neutral way
> (which works regardless of the date format setting). See:
> http://www.karaszi.com/SQLServer/info_datetime.asp
> Razvan
>

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!