February 4, 2010 at 2:20 pm
Hi,
i want to retrieve the server uptime so i run this query
exec master..xp_cmdshell 'net STATISTICS server | find "Statistics since"'
this give me the server start time.
the problem is that the server start time that i receive in windows server 2008 and sql server 2008 is m/d/yyyy(today date 2/4/2010 ).
how i can get it to be dd/mm/yyyy?
i need it to be exactly on all server type (2003,2008,2008r2) and on all sql (2005,2008)
THX
February 4, 2010 at 2:34 pm
Better take it in a sql table and format it there.
MJ
February 4, 2010 at 2:40 pm
Can you change the Regional setting on the machine or in SQL Server to United Kingdom? That should change your default to dd/mm/yyyy
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
February 4, 2010 at 2:53 pm
MANU-J. (2/4/2010)
Better take it in a sql table and format it there.MJ
I would go with this suggestion. Change the format from sql side.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 4, 2010 at 2:54 pm
Stefan Krzywicki (2/4/2010)
Can you change the Regional setting on the machine or in SQL Server to United Kingdom? That should change your default to dd/mm/yyyy
i can't change it permanently.
but can i change it just for the script itself to get the right results without influencethe server?
February 4, 2010 at 3:35 pm
if you import the date into a datetime field and then use the following:
SELECT convert(varchar,convert(datetime, '2/4/2010'),103)
You should be able to accomplish what you need.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 4, 2010 at 3:52 pm
CirquedeSQLeil (2/4/2010)
if you import the date into a datetime field and then use the following:
SELECT convert(varchar,convert(datetime, '2/4/2010'),103)You should be able to accomplish what you need.
ok.
if i use this command EXEC master..xp_cmdshell 'net STATISTICS server | find "Statistics since"'
and in i insert the results to a table how can i manipulate the table to get the date and time from the results?
some times the result will be like this in the table
Statistics since 2/4/2010 11:11:43 PM
and sometimes like this
Statistics since 12/24/2010 05:11:43 AM
THX
February 4, 2010 at 4:02 pm
if you set a default on the field to accept a specific format - it should convert it for you.
Are you capturing more information than that? If you know which servers are associated to which format, you could also build case statements around the two types.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 4, 2010 at 4:19 pm
CirquedeSQLeil (2/4/2010)
if you set a default on the field to accept a specific format - it should convert it for you.Are you capturing more information than that? If you know which servers are associated to which format, you could also build case statements around the two types.
i going to run this report on a lot of servers.
if i run this syntax "set dateformat dmy" does it effect all server or just the current session?
February 4, 2010 at 5:29 pm
current session.
From MSDN
This setting affects the interpretation of character strings as they are converted to date values for storage in the database. It does not affect the display of date data type values that are stored in the database or the storage format.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 4, 2010 at 9:24 pm
Example:
CREATE TABLE [dbo].[Statsdate] (
[Statsdate] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
declare @pos int
insert statsdate
EXEC master..xp_cmdshell 'net STATISTICS server | find "Statistics since"'
delete from statsdate where statsdate is null
select @pos= patindex('%[0-9]%',statsdate) from statsdate
update statsdate
set statsdate=substring(statsdate,@pos-1,len(statsdate))
SELECT convert(varchar,convert(datetime,statsdate),103) from statsdate
HTH!
February 4, 2010 at 9:30 pm
looks good to me.
I haven't tested that - but i think it could work in this scenario
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 5, 2010 at 2:21 am
can you tell me please on what it deepened in sql server to show the date format like mdy or dmy?
THX
February 5, 2010 at 10:24 am
Your regional settings determine the date format. However, that is why there are standards that many use to format the datestamp in a common format for servers they administer that are in different regions.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 5, 2010 at 1:36 pm
The command 'net statistics server' seems not so reliable to report the uptime of a server.
Out of curiousity, I ran it on on several servers, the results:
(1) my own XP laptop --showing correct time
(2) two Windows 2008 server: 2/6/2106
(3) another Windows 2008 server: 1/1/1980
Searched online and found that
net statistics workstation | find "Statistics since"
or
systeminfo | find "Boot Time"
would give the precise info.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply