Date Formatting

Hi, all:
I'm testing SQL Server 2005 by using SQL Server 2000 database and supporting
software.
On the old sever, date formatting was yyyy-mm-dd, on SQL Server 2005 it's
yyyy-dd-mm and it's creating a problem with all the stored procedures and the
software.
Instead of changing the date format on all the afotware and stores
procedure, how can I change the date format in the server itself?
For instance, if I use SELECT GETDATE() I would get on the SQL Server 2000:
2007-10-11, and on the 2005 version I'm getting 2007-11-10.
Any ideas?
--
Thanks,
Rick.
"For every problem, there is a solution that is simple, neat, and wrong."
H. L. Mencken"yyyy-dd-mm doesn't make sense to me. It's not one of the date formats I
recognize (see CONVERT in BOL). However, I'm going to take a guess.
Check the default collation in the model database properties (and whichever
database you're testing in) and see if it's the same as the one on your 2000
box.
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:EF08C898-D56B-462F-B40E-F1788F9E3A92@.microsoft.com...
> Hi, all:
> I'm testing SQL Server 2005 by using SQL Server 2000 database and
> supporting
> software.
> On the old sever, date formatting was yyyy-mm-dd, on SQL Server 2005 it's
> yyyy-dd-mm and it's creating a problem with all the stored procedures and
> the
> software.
> Instead of changing the date format on all the afotware and stores
> procedure, how can I change the date format in the server itself?
> For instance, if I use SELECT GETDATE() I would get on the SQL Server
> 2000:
> 2007-10-11, and on the 2005 version I'm getting 2007-11-10.
> Any ideas?
> --
> Thanks,
> Rick.
> "For every problem, there is a solution that is simple, neat, and wrong."
> H. L. Mencken"|||You may change the date format using SET DATEFORMAT command.
More information on this with an example can be found at:
http://www.sqlcommunity.com/Default.aspx?grm2id=69&tabid=77
--
Thank you,
Saleem Hakani
HTTP://WWW.SQLCOMMUNITY.COM (World Wide Microsoft SQL Server Community)
SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
Articles, SQL Clinic and a lot of SQL fun.
Register (Free):
http://www.sqlcommunity.com/RegistrationLoginPage/tabid/68/Default.aspx?returnurl=%2fHome%2ftabid%2f36%2fDefault.aspx
"Rick" wrote:
> Hi, all:
> I'm testing SQL Server 2005 by using SQL Server 2000 database and supporting
> software.
> On the old sever, date formatting was yyyy-mm-dd, on SQL Server 2005 it's
> yyyy-dd-mm and it's creating a problem with all the stored procedures and the
> software.
> Instead of changing the date format on all the afotware and stores
> procedure, how can I change the date format in the server itself?
> For instance, if I use SELECT GETDATE() I would get on the SQL Server 2000:
> 2007-10-11, and on the 2005 version I'm getting 2007-11-10.
> Any ideas?
> --
> Thanks,
> Rick.
> "For every problem, there is a solution that is simple, neat, and wrong."
> H. L. Mencken"|||Saleem,
He specified at the server level (which might even be the database level),
not in each procedure.
From SET DATEFORMAT BOL:
This setting is used only in the interpretation of character strings
as they are converted to date values. It has no effect on the
display of date values.
The setting of SET DATEFORMAT is set at execute or run time and not
at parse time.
"Saleem Hakani" <SaleemHakani@.discussions.microsoft.com> wrote in message
news:7A0EF984-DAA0-45AA-940B-D2C8D2D4111D@.microsoft.com...
> You may change the date format using SET DATEFORMAT command.
> More information on this with an example can be found at:
> http://www.sqlcommunity.com/Default.aspx?grm2id=69&tabid=77
> --
> Thank you,
> Saleem Hakani
> HTTP://WWW.SQLCOMMUNITY.COM (World Wide Microsoft SQL Server Community)
> SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
> Articles, SQL Clinic and a lot of SQL fun.
> Register (Free):
> http://www.sqlcommunity.com/RegistrationLoginPage/tabid/68/Default.aspx?returnurl=%2fHome%2ftabid%2f36%2fDefault.aspx
>
> "Rick" wrote:
>> Hi, all:
>> I'm testing SQL Server 2005 by using SQL Server 2000 database and
>> supporting
>> software.
>> On the old sever, date formatting was yyyy-mm-dd, on SQL Server 2005 it's
>> yyyy-dd-mm and it's creating a problem with all the stored procedures and
>> the
>> software.
>> Instead of changing the date format on all the afotware and stores
>> procedure, how can I change the date format in the server itself?
>> For instance, if I use SELECT GETDATE() I would get on the SQL Server
>> 2000:
>> 2007-10-11, and on the 2005 version I'm getting 2007-11-10.
>> Any ideas?
>> --
>> Thanks,
>> Rick.
>> "For every problem, there is a solution that is simple, neat, and wrong."
>> H. L. Mencken"|||Hi Jay, SET DATEFORMAT can be used at the server level.
You can check the current active settings by executing DBCC USEROPTIONS.
Pardon me if you think I did not understand the question.
Thank you,
Saleem Hakani
HTTP://WWW.SQLCOMMUNITY.COM (World Wide Microsoft SQL Server Community)
SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
Articles, SQL Clinic and a lot of SQL fun.
Register (Free):
http://www.sqlcommunity.com/RegistrationLoginPage/tabid/68/Default.aspx?returnurl=%2fHome%2ftabid%2f36%2fDefault.aspx
"Jay" wrote:
> Saleem,
> He specified at the server level (which might even be the database level),
> not in each procedure.
> From SET DATEFORMAT BOL:
> This setting is used only in the interpretation of character strings
> as they are converted to date values. It has no effect on the
> display of date values.
> The setting of SET DATEFORMAT is set at execute or run time and not
> at parse time.
>
> "Saleem Hakani" <SaleemHakani@.discussions.microsoft.com> wrote in message
> news:7A0EF984-DAA0-45AA-940B-D2C8D2D4111D@.microsoft.com...
> > You may change the date format using SET DATEFORMAT command.
> > More information on this with an example can be found at:
> > http://www.sqlcommunity.com/Default.aspx?grm2id=69&tabid=77
> >
> > --
> > Thank you,
> > Saleem Hakani
> > HTTP://WWW.SQLCOMMUNITY.COM (World Wide Microsoft SQL Server Community)
> > SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
> > Articles, SQL Clinic and a lot of SQL fun.
> > Register (Free):
> > http://www.sqlcommunity.com/RegistrationLoginPage/tabid/68/Default.aspx?returnurl=%2fHome%2ftabid%2f36%2fDefault.aspx
> >
> >
> > "Rick" wrote:
> >
> >> Hi, all:
> >>
> >> I'm testing SQL Server 2005 by using SQL Server 2000 database and
> >> supporting
> >> software.
> >>
> >> On the old sever, date formatting was yyyy-mm-dd, on SQL Server 2005 it's
> >> yyyy-dd-mm and it's creating a problem with all the stored procedures and
> >> the
> >> software.
> >>
> >> Instead of changing the date format on all the afotware and stores
> >> procedure, how can I change the date format in the server itself?
> >>
> >> For instance, if I use SELECT GETDATE() I would get on the SQL Server
> >> 2000:
> >> 2007-10-11, and on the 2005 version I'm getting 2007-11-10.
> >>
> >> Any ideas?
> >>
> >> --
> >> Thanks,
> >>
> >> Rick.
> >>
> >> "For every problem, there is a solution that is simple, neat, and wrong."
> >> H. L. Mencken"
>
>|||> Pardon me if you think I did not understand the question.
I think Jay did understand the question. Here's a quote from the first post (the OP's post):
"For instance, if I use SELECT GETDATE() I would get on the SQL Server 2000:
2007-10-11, and on the 2005 version I'm getting 2007-11-10."
Clearly, this is a question of how datetime values are *displayed*. Not inperpreted for input. SET
DATEFORMAT has nothing to do with display of datetime data, since it is the client application that
converts the binary values returned by SQL Server into something human readable.
Rick,
You might wantr to check out: http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Saleem Hakani" <SaleemHakani@.discussions.microsoft.com> wrote in message
news:6D1081FD-9F92-4631-AEE0-605AED35A9F5@.microsoft.com...
> Hi Jay, SET DATEFORMAT can be used at the server level.
> You can check the current active settings by executing DBCC USEROPTIONS.
> Pardon me if you think I did not understand the question.
> Thank you,
> Saleem Hakani
> HTTP://WWW.SQLCOMMUNITY.COM (World Wide Microsoft SQL Server Community)
> SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
> Articles, SQL Clinic and a lot of SQL fun.
> Register (Free):
> http://www.sqlcommunity.com/RegistrationLoginPage/tabid/68/Default.aspx?returnurl=%2fHome%2ftabid%2f36%2fDefault.aspx
>
> "Jay" wrote:
>> Saleem,
>> He specified at the server level (which might even be the database level),
>> not in each procedure.
>> From SET DATEFORMAT BOL:
>> This setting is used only in the interpretation of character strings
>> as they are converted to date values. It has no effect on the
>> display of date values.
>> The setting of SET DATEFORMAT is set at execute or run time and not
>> at parse time.
>>
>> "Saleem Hakani" <SaleemHakani@.discussions.microsoft.com> wrote in message
>> news:7A0EF984-DAA0-45AA-940B-D2C8D2D4111D@.microsoft.com...
>> > You may change the date format using SET DATEFORMAT command.
>> > More information on this with an example can be found at:
>> > http://www.sqlcommunity.com/Default.aspx?grm2id=69&tabid=77
>> >
>> > --
>> > Thank you,
>> > Saleem Hakani
>> > HTTP://WWW.SQLCOMMUNITY.COM (World Wide Microsoft SQL Server Community)
>> > SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
>> > Articles, SQL Clinic and a lot of SQL fun.
>> > Register (Free):
>> > http://www.sqlcommunity.com/RegistrationLoginPage/tabid/68/Default.aspx?returnurl=%2fHome%2ftabid%2f36%2fDefault.aspx
>> >
>> >
>> > "Rick" wrote:
>> >
>> >> Hi, all:
>> >>
>> >> I'm testing SQL Server 2005 by using SQL Server 2000 database and
>> >> supporting
>> >> software.
>> >>
>> >> On the old sever, date formatting was yyyy-mm-dd, on SQL Server 2005 it's
>> >> yyyy-dd-mm and it's creating a problem with all the stored procedures and
>> >> the
>> >> software.
>> >>
>> >> Instead of changing the date format on all the afotware and stores
>> >> procedure, how can I change the date format in the server itself?
>> >>
>> >> For instance, if I use SELECT GETDATE() I would get on the SQL Server
>> >> 2000:
>> >> 2007-10-11, and on the 2005 version I'm getting 2007-11-10.
>> >>
>> >> Any ideas?
>> >>
>> >> --
>> >> Thanks,
>> >>
>> >> Rick.
>> >>
>> >> "For every problem, there is a solution that is simple, neat, and wrong."
>> >> H. L. Mencken"
>>|||SET DATEFORMAT mdy
select getdate()
Returns: 2007-10-12 06:40:47.090
SET DATEFORMAT dmy
select getdate()
Returns: 2007-10-12 06:40:47.090
"Saleem Hakani" <SaleemHakani@.discussions.microsoft.com> wrote in message
news:6D1081FD-9F92-4631-AEE0-605AED35A9F5@.microsoft.com...
> Hi Jay, SET DATEFORMAT can be used at the server level.
> You can check the current active settings by executing DBCC USEROPTIONS.
> Pardon me if you think I did not understand the question.
> Thank you,
> Saleem Hakani
> HTTP://WWW.SQLCOMMUNITY.COM (World Wide Microsoft SQL Server Community)
> SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
> Articles, SQL Clinic and a lot of SQL fun.
> Register (Free):
> http://www.sqlcommunity.com/RegistrationLoginPage/tabid/68/Default.aspx?returnurl=%2fHome%2ftabid%2f36%2fDefault.aspx
>
> "Jay" wrote:
>> Saleem,
>> He specified at the server level (which might even be the database
>> level),
>> not in each procedure.
>> From SET DATEFORMAT BOL:
>> This setting is used only in the interpretation of character
>> strings
>> as they are converted to date values. It has no effect on the
>> display of date values.
>> The setting of SET DATEFORMAT is set at execute or run time and
>> not
>> at parse time.
>>
>> "Saleem Hakani" <SaleemHakani@.discussions.microsoft.com> wrote in message
>> news:7A0EF984-DAA0-45AA-940B-D2C8D2D4111D@.microsoft.com...
>> > You may change the date format using SET DATEFORMAT command.
>> > More information on this with an example can be found at:
>> > http://www.sqlcommunity.com/Default.aspx?grm2id=69&tabid=77
>> >
>> > --
>> > Thank you,
>> > Saleem Hakani
>> > HTTP://WWW.SQLCOMMUNITY.COM (World Wide Microsoft SQL Server Community)
>> > SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
>> > Articles, SQL Clinic and a lot of SQL fun.
>> > Register (Free):
>> > http://www.sqlcommunity.com/RegistrationLoginPage/tabid/68/Default.aspx?returnurl=%2fHome%2ftabid%2f36%2fDefault.aspx
>> >
>> >
>> > "Rick" wrote:
>> >
>> >> Hi, all:
>> >>
>> >> I'm testing SQL Server 2005 by using SQL Server 2000 database and
>> >> supporting
>> >> software.
>> >>
>> >> On the old sever, date formatting was yyyy-mm-dd, on SQL Server 2005
>> >> it's
>> >> yyyy-dd-mm and it's creating a problem with all the stored procedures
>> >> and
>> >> the
>> >> software.
>> >>
>> >> Instead of changing the date format on all the afotware and stores
>> >> procedure, how can I change the date format in the server itself?
>> >>
>> >> For instance, if I use SELECT GETDATE() I would get on the SQL Server
>> >> 2000:
>> >> 2007-10-11, and on the 2005 version I'm getting 2007-11-10.
>> >>
>> >> Any ideas?
>> >>
>> >> --
>> >> Thanks,
>> >>
>> >> Rick.
>> >>
>> >> "For every problem, there is a solution that is simple, neat, and
>> >> wrong."
>> >> H. L. Mencken"
>>|||OK Tibor, now I'm confused.
The OP's example was hand typed, not copy/pasted but suggested that the
default output of getdate() was changed. Having dealt with defaults changing
when language changes, it seemed reasonable. I simply never use the default
and let the server use its own internal format, then specify the output I
want - which is what your article seems to suggest (amongst a bazillion
other things :) I am also usually tunnel-visioned to us_english (yet another
failing of mine).
I tried changing the language from us_english (mdy) to british (dmy)
expecting the default output to change. It did not. Is it even possible to
change the default output?
select getdate()
-- do/set domething
select getdate()
-- get a different output format
Thanks,
Jay
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eFYCPoJDIHA.5360@.TK2MSFTNGP03.phx.gbl...
>> Pardon me if you think I did not understand the question.
> I think Jay did understand the question. Here's a quote from the first
> post (the OP's post):
> "For instance, if I use SELECT GETDATE() I would get on the SQL Server
> 2000:
> 2007-10-11, and on the 2005 version I'm getting 2007-11-10."
> Clearly, this is a question of how datetime values are *displayed*. Not
> inperpreted for input. SET DATEFORMAT has nothing to do with display of
> datetime data, since it is the client application that converts the binary
> values returned by SQL Server into something human readable.
> Rick,
> You might wantr to check out:
> http://www.karaszi.com/SQLServer/info_datetime.asp
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Saleem Hakani" <SaleemHakani@.discussions.microsoft.com> wrote in message
> news:6D1081FD-9F92-4631-AEE0-605AED35A9F5@.microsoft.com...
>> Hi Jay, SET DATEFORMAT can be used at the server level.
>> You can check the current active settings by executing DBCC USEROPTIONS.
>> Pardon me if you think I did not understand the question.
>> Thank you,
>> Saleem Hakani
>> HTTP://WWW.SQLCOMMUNITY.COM (World Wide Microsoft SQL Server Community)
>> SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
>> Articles, SQL Clinic and a lot of SQL fun.
>> Register (Free):
>> http://www.sqlcommunity.com/RegistrationLoginPage/tabid/68/Default.aspx?returnurl=%2fHome%2ftabid%2f36%2fDefault.aspx
>>
>> "Jay" wrote:
>> Saleem,
>> He specified at the server level (which might even be the database
>> level),
>> not in each procedure.
>> From SET DATEFORMAT BOL:
>> This setting is used only in the interpretation of character
>> strings
>> as they are converted to date values. It has no effect on the
>> display of date values.
>> The setting of SET DATEFORMAT is set at execute or run time and
>> not
>> at parse time.
>>
>> "Saleem Hakani" <SaleemHakani@.discussions.microsoft.com> wrote in
>> message
>> news:7A0EF984-DAA0-45AA-940B-D2C8D2D4111D@.microsoft.com...
>> > You may change the date format using SET DATEFORMAT command.
>> > More information on this with an example can be found at:
>> > http://www.sqlcommunity.com/Default.aspx?grm2id=69&tabid=77
>> >
>> > --
>> > Thank you,
>> > Saleem Hakani
>> > HTTP://WWW.SQLCOMMUNITY.COM (World Wide Microsoft SQL Server
>> > Community)
>> > SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts,
>> > SQL
>> > Articles, SQL Clinic and a lot of SQL fun.
>> > Register (Free):
>> > http://www.sqlcommunity.com/RegistrationLoginPage/tabid/68/Default.aspx?returnurl=%2fHome%2ftabid%2f36%2fDefault.aspx
>> >
>> >
>> > "Rick" wrote:
>> >
>> >> Hi, all:
>> >>
>> >> I'm testing SQL Server 2005 by using SQL Server 2000 database and
>> >> supporting
>> >> software.
>> >>
>> >> On the old sever, date formatting was yyyy-mm-dd, on SQL Server 2005
>> >> it's
>> >> yyyy-dd-mm and it's creating a problem with all the stored procedures
>> >> and
>> >> the
>> >> software.
>> >>
>> >> Instead of changing the date format on all the afotware and stores
>> >> procedure, how can I change the date format in the server itself?
>> >>
>> >> For instance, if I use SELECT GETDATE() I would get on the SQL Server
>> >> 2000:
>> >> 2007-10-11, and on the 2005 version I'm getting 2007-11-10.
>> >>
>> >> Any ideas?
>> >>
>> >> --
>> >> Thanks,
>> >>
>> >> Rick.
>> >>
>> >> "For every problem, there is a solution that is simple, neat, and
>> >> wrong."
>> >> H. L. Mencken"
>>
>|||Let me start by quoting myself (from my prior post) ;-)
>> SET DATEFORMAT has nothing to do with display of datetime data, since it is the client
>> application that converts the binary values returned by SQL Server into something human readable.
Above holds. SQL Server sends a number of bits to the client app which interprets the bits and
displays them in a format (as a string) that we humans understand. These bits are *not* ASCII codes
with one letter for each part of the datetime format. I.e., the client app decides the format.
Most probably the OP used different client applications when connecting to the different servers.
Another thing that can alter datetime representation is whether the *client app* respects the
regional settings on the client machine. But it is still the client app.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <spam@.nospam.org> wrote in message news:%23Lb3IhNDIHA.4228@.TK2MSFTNGP02.phx.gbl...
> OK Tibor, now I'm confused.
> The OP's example was hand typed, not copy/pasted but suggested that the default output of
> getdate() was changed. Having dealt with defaults changing when language changes, it seemed
> reasonable. I simply never use the default and let the server use its own internal format, then
> specify the output I want - which is what your article seems to suggest (amongst a bazillion other
> things :) I am also usually tunnel-visioned to us_english (yet another failing of mine).
> I tried changing the language from us_english (mdy) to british (dmy) expecting the default output
> to change. It did not. Is it even possible to change the default output?
> select getdate()
> -- do/set domething
> select getdate()
> -- get a different output format
> Thanks,
> Jay
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:eFYCPoJDIHA.5360@.TK2MSFTNGP03.phx.gbl...
>> Pardon me if you think I did not understand the question.
>> I think Jay did understand the question. Here's a quote from the first post (the OP's post):
>> "For instance, if I use SELECT GETDATE() I would get on the SQL Server 2000:
>> 2007-10-11, and on the 2005 version I'm getting 2007-11-10."
>> Clearly, this is a question of how datetime values are *displayed*. Not inperpreted for input.
>> SET DATEFORMAT has nothing to do with display of datetime data, since it is the client
>> application that converts the binary values returned by SQL Server into something human readable.
>> Rick,
>> You might wantr to check out: http://www.karaszi.com/SQLServer/info_datetime.asp
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Saleem Hakani" <SaleemHakani@.discussions.microsoft.com> wrote in message
>> news:6D1081FD-9F92-4631-AEE0-605AED35A9F5@.microsoft.com...
>> Hi Jay, SET DATEFORMAT can be used at the server level.
>> You can check the current active settings by executing DBCC USEROPTIONS.
>> Pardon me if you think I did not understand the question.
>> Thank you,
>> Saleem Hakani
>> HTTP://WWW.SQLCOMMUNITY.COM (World Wide Microsoft SQL Server Community)
>> SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
>> Articles, SQL Clinic and a lot of SQL fun.
>> Register (Free):
>> http://www.sqlcommunity.com/RegistrationLoginPage/tabid/68/Default.aspx?returnurl=%2fHome%2ftabid%2f36%2fDefault.aspx
>>
>> "Jay" wrote:
>> Saleem,
>> He specified at the server level (which might even be the database level),
>> not in each procedure.
>> From SET DATEFORMAT BOL:
>> This setting is used only in the interpretation of character strings
>> as they are converted to date values. It has no effect on the
>> display of date values.
>> The setting of SET DATEFORMAT is set at execute or run time and not
>> at parse time.
>>
>> "Saleem Hakani" <SaleemHakani@.discussions.microsoft.com> wrote in message
>> news:7A0EF984-DAA0-45AA-940B-D2C8D2D4111D@.microsoft.com...
>> > You may change the date format using SET DATEFORMAT command.
>> > More information on this with an example can be found at:
>> > http://www.sqlcommunity.com/Default.aspx?grm2id=69&tabid=77
>> >
>> > --
>> > Thank you,
>> > Saleem Hakani
>> > HTTP://WWW.SQLCOMMUNITY.COM (World Wide Microsoft SQL Server Community)
>> > SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
>> > Articles, SQL Clinic and a lot of SQL fun.
>> > Register (Free):
>> > http://www.sqlcommunity.com/RegistrationLoginPage/tabid/68/Default.aspx?returnurl=%2fHome%2ftabid%2f36%2fDefault.aspx
>> >
>> >
>> > "Rick" wrote:
>> >
>> >> Hi, all:
>> >>
>> >> I'm testing SQL Server 2005 by using SQL Server 2000 database and
>> >> supporting
>> >> software.
>> >>
>> >> On the old sever, date formatting was yyyy-mm-dd, on SQL Server 2005 it's
>> >> yyyy-dd-mm and it's creating a problem with all the stored procedures and
>> >> the
>> >> software.
>> >>
>> >> Instead of changing the date format on all the afotware and stores
>> >> procedure, how can I change the date format in the server itself?
>> >>
>> >> For instance, if I use SELECT GETDATE() I would get on the SQL Server
>> >> 2000:
>> >> 2007-10-11, and on the 2005 version I'm getting 2007-11-10.
>> >>
>> >> Any ideas?
>> >>
>> >> --
>> >> Thanks,
>> >>
>> >> Rick.
>> >>
>> >> "For every problem, there is a solution that is simple, neat, and wrong."
>> >> H. L. Mencken"
>>
>>
>|||Is it possible your SQL Server 2005 is sitting on a server where the date is
set to November?
Brian|||Ya, that explains why I was confused. The OP got it wrong.
Thanks Tibor.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:23BF9BC8-4D32-40F2-949E-A845C921C7CA@.microsoft.com...
> Let me start by quoting myself (from my prior post) ;-)
>> SET DATEFORMAT has nothing to do with display of datetime data, since it
>> is the client application that converts the binary values returned by
>> SQL Server into something human readable.
> Above holds. SQL Server sends a number of bits to the client app which
> interprets the bits and displays them in a format (as a string) that we
> humans understand. These bits are *not* ASCII codes with one letter for
> each part of the datetime format. I.e., the client app decides the format.
> Most probably the OP used different client applications when connecting to
> the different servers.
> Another thing that can alter datetime representation is whether the
> *client app* respects the regional settings on the client machine. But it
> is still the client app.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <spam@.nospam.org> wrote in message
> news:%23Lb3IhNDIHA.4228@.TK2MSFTNGP02.phx.gbl...
>> OK Tibor, now I'm confused.
>> The OP's example was hand typed, not copy/pasted but suggested that the
>> default output of getdate() was changed. Having dealt with defaults
>> changing when language changes, it seemed reasonable. I simply never use
>> the default and let the server use its own internal format, then specify
>> the output I want - which is what your article seems to suggest (amongst
>> a bazillion other things :) I am also usually tunnel-visioned to
>> us_english (yet another failing of mine).
>> I tried changing the language from us_english (mdy) to british (dmy)
>> expecting the default output to change. It did not. Is it even possible
>> to change the default output?
>> select getdate()
>> -- do/set domething
>> select getdate()
>> -- get a different output format
>> Thanks,
>> Jay
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:eFYCPoJDIHA.5360@.TK2MSFTNGP03.phx.gbl...
>> Pardon me if you think I did not understand the question.
>> I think Jay did understand the question. Here's a quote from the first
>> post (the OP's post):
>> "For instance, if I use SELECT GETDATE() I would get on the SQL Server
>> 2000:
>> 2007-10-11, and on the 2005 version I'm getting 2007-11-10."
>> Clearly, this is a question of how datetime values are *displayed*. Not
>> inperpreted for input. SET DATEFORMAT has nothing to do with display of
>> datetime data, since it is the client application that converts the
>> binary values returned by SQL Server into something human readable.
>> Rick,
>> You might wantr to check out:
>> http://www.karaszi.com/SQLServer/info_datetime.asp
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Saleem Hakani" <SaleemHakani@.discussions.microsoft.com> wrote in
>> message news:6D1081FD-9F92-4631-AEE0-605AED35A9F5@.microsoft.com...
>> Hi Jay, SET DATEFORMAT can be used at the server level.
>> You can check the current active settings by executing DBCC
>> USEROPTIONS.
>> Pardon me if you think I did not understand the question.
>> Thank you,
>> Saleem Hakani
>> HTTP://WWW.SQLCOMMUNITY.COM (World Wide Microsoft SQL Server Community)
>> SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
>> Articles, SQL Clinic and a lot of SQL fun.
>> Register (Free):
>> http://www.sqlcommunity.com/RegistrationLoginPage/tabid/68/Default.aspx?returnurl=%2fHome%2ftabid%2f36%2fDefault.aspx
>>
>> "Jay" wrote:
>> Saleem,
>> He specified at the server level (which might even be the database
>> level),
>> not in each procedure.
>> From SET DATEFORMAT BOL:
>> This setting is used only in the interpretation of character
>> strings
>> as they are converted to date values. It has no effect on the
>> display of date values.
>> The setting of SET DATEFORMAT is set at execute or run time
>> and not
>> at parse time.
>>
>> "Saleem Hakani" <SaleemHakani@.discussions.microsoft.com> wrote in
>> message
>> news:7A0EF984-DAA0-45AA-940B-D2C8D2D4111D@.microsoft.com...
>> > You may change the date format using SET DATEFORMAT command.
>> > More information on this with an example can be found at:
>> > http://www.sqlcommunity.com/Default.aspx?grm2id=69&tabid=77
>> >
>> > --
>> > Thank you,
>> > Saleem Hakani
>> > HTTP://WWW.SQLCOMMUNITY.COM (World Wide Microsoft SQL Server
>> > Community)
>> > SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts,
>> > SQL
>> > Articles, SQL Clinic and a lot of SQL fun.
>> > Register (Free):
>> > http://www.sqlcommunity.com/RegistrationLoginPage/tabid/68/Default.aspx?returnurl=%2fHome%2ftabid%2f36%2fDefault.aspx
>> >
>> >
>> > "Rick" wrote:
>> >
>> >> Hi, all:
>> >>
>> >> I'm testing SQL Server 2005 by using SQL Server 2000 database and
>> >> supporting
>> >> software.
>> >>
>> >> On the old sever, date formatting was yyyy-mm-dd, on SQL Server
>> >> 2005 it's
>> >> yyyy-dd-mm and it's creating a problem with all the stored
>> >> procedures and
>> >> the
>> >> software.
>> >>
>> >> Instead of changing the date format on all the afotware and stores
>> >> procedure, how can I change the date format in the server itself?
>> >>
>> >> For instance, if I use SELECT GETDATE() I would get on the SQL
>> >> Server
>> >> 2000:
>> >> 2007-10-11, and on the 2005 version I'm getting 2007-11-10.
>> >>
>> >> Any ideas?
>> >>
>> >> --
>> >> Thanks,
>> >>
>> >> Rick.
>> >>
>> >> "For every problem, there is a solution that is simple, neat, and
>> >> wrong."
>> >> H. L. Mencken"
>>
>>
>>
>