How can I determine current
SET DATEFORMAT ?DBCC USEROPTIONS
Martin C K Poon
Senior Analyst Programmer
====================================
"Alur" <Alur@.discussions.microsoft.com> bl
news:D2C70A61-AD21-4EFE-A945-5A7A72EBF6B7@.microsoft.com g...
> How can I determine current
> SET DATEFORMAT ?|||If you haven't localized SQL Server for your language, the default date
format is the American one: DD/MM/YY.
Use
SELECT GETDATE() and this will show you the default currently.
"Alur" wrote:
> How can I determine current
> SET DATEFORMAT ?|||> SELECT GETDATE() and this will show you the default currently.
No, the presentation of datetime has no correlation of how input of datetime
strings are
interpreted. See http://www.karaszi.com/SQLServer/info_datetime.asp for more
information.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Derekman" <Derekman@.discussions.microsoft.com> wrote in message
news:23BB0F57-2A93-4E20-95C8-7E5038756D51@.microsoft.com...
> If you haven't localized SQL Server for your language, the default date
> format is the American one: DD/MM/YY.
> Use
> SELECT GETDATE() and this will show you the default currently.
> "Alur" wrote:
>|||Sorry, Derekman, but this is not correct.
The American format, the default, is MDY.
Also, GETDATE() has nothing to do with DATEFORMAT.
DATEFORMAT shows you how SQL Server interprets incoming strings as dates.
For example, if you ask it to convert '3/4/06' to a datetime, will it be
April 3rd or March 4th?
SELECT CONVERT(datetime, '3/4/06')
DATEFORMAT tells SQL Server how to interpret a string that has all numbers,
which number is the month,
which is day and which is year. For the default MDY, it means the first
number is month, so '3/4/06' would be March 4th.
GETDATE returns the current date and time in a default output format, which
is based on your regional settings.
To DISPLAY a datetime in another format, you need to convert it to a string,
and specify a style. You can see the different styles available if you read
about CONVERT in the Books Online.
HTH
Kalen Delaney, SQL Server MVP
"Derekman" <Derekman@.discussions.microsoft.com> wrote in message
news:23BB0F57-2A93-4E20-95C8-7E5038756D51@.microsoft.com...
> If you haven't localized SQL Server for your language, the default date
> format is the American one: DD/MM/YY.
> Use
> SELECT GETDATE() and this will show you the default currently.
> "Alur" wrote:
>|||DBCC USEROPTIONS
Another solution is to write some code similar to this:
SET DATEFORMAT ydm
GO
DECLARE @.datevar datetime
SET @.datevar = '01/02/03'
SELECT cast(datepart(month,@.datevar)as char(1))
+ cast(datepart(day,@.datevar)as char(1))
SELECT case cast(datepart(month,@.datevar)as char(1))
+ cast(datepart(day,@.datevar)as char(1))
when 31 then 'dym'
when 21 then 'dmy'
when 12 then 'mdy'
when 13 then 'myd'
when 32 then 'ydm'
when 23 then 'ymd'
else '?'
end
"Alur" wrote:
> How can I determine current
> SET DATEFORMAT ?|||Hi Greg
This is a really
solution! I hope you don't mind that I cleaned it up
just a bit:
SET DATEFORMAT ydm; -- For testing
GO
DECLARE @.datevar datetime,
@.datecode char(2);
SET @.datevar = '01/02/03'
SELECT @.datecode = cast(datepart(month,@.datevar)as char(1))
+ cast(datepart(day,@.datevar)as char(1));
SELECT @.datecode AS datecode; -- For troubleshooting
SELECT CASE @.datecode
when '31' then 'dym'
when '21' then 'dmy'
when '12' then 'mdy'
when '13' then 'myd'
when '32' then 'ydm'
when '23' then 'ymd'
else '?'
END AS DATEFORMAT;
HTH
Kalen Delaney, SQL Server MVP
"Greg Larsen" <GregLarsen@.discussions.microsoft.com> wrote in message
news:E90E6795-5952-4D4F-AAB5-00B764EB5130@.microsoft.com...
> DBCC USEROPTIONS
> Another solution is to write some code similar to this:
> SET DATEFORMAT ydm
> GO
> DECLARE @.datevar datetime
> SET @.datevar = '01/02/03'
> SELECT cast(datepart(month,@.datevar)as char(1))
> + cast(datepart(day,@.datevar)as char(1))
> SELECT case cast(datepart(month,@.datevar)as char(1))
> + cast(datepart(day,@.datevar)as char(1))
> when 31 then 'dym'
> when 21 then 'dmy'
> when 12 then 'mdy'
> when 13 then 'myd'
> when 32 then 'ydm'
> when 23 then 'ymd'
> else '?'
> end
> "Alur" wrote:
>|||No problem on the clean up. New script is much better.
"Kalen Delaney" wrote:
> Hi Greg
> This is a really
solution! I hope you don't mind that I cleaned it up
> just a bit:
> SET DATEFORMAT ydm; -- For testing
> GO
> DECLARE @.datevar datetime,
> @.datecode char(2);
> SET @.datevar = '01/02/03'
> SELECT @.datecode = cast(datepart(month,@.datevar)as char(1))
> + cast(datepart(day,@.datevar)as char(1));
> SELECT @.datecode AS datecode; -- For troubleshooting
> SELECT CASE @.datecode
> when '31' then 'dym'
> when '21' then 'dmy'
> when '12' then 'mdy'
> when '13' then 'myd'
> when '32' then 'ydm'
> when '23' then 'ymd'
> else '?'
> END AS DATEFORMAT;
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Greg Larsen" <GregLarsen@.discussions.microsoft.com> wrote in message
> news:E90E6795-5952-4D4F-AAB5-00B764EB5130@.microsoft.com...
>
>|||Thank you very much.|||Thank you.
s.mmm'). This way, SQL Server can interpret the string as a datetime no matter the language or formatdate settings.