Hello ,
Is it possible/recommended to do SQL server instance backups in Single
user mode ?
Thanks in advance,
atvHi
No need. BACKUP database command actually does nnot blok others
"velu5" <thirumalaivelu@.gmail.com> wrote in message
news:1193743670.860831.196700@.k35g2000prh.googlegroups.com...
> Hello ,
> Is it possible/recommended to do SQL server instance backups in Single
> user mode ?
> Thanks in advance,
> atv
>|||Dear Velu,
Can I know why you want to do the database backups in single user mode?
Regards
Balaji
"velu5" wrote:
> Hello ,
> Is it possible/recommended to do SQL server instance backups in Single
> user mode ?
> Thanks in advance,
> atv
>|||The SQL Backup command is non-blocking and provides a transactionally
consistent backup without any additional intervention such as single-user
mode.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"velu5" <thirumalaivelu@.gmail.com> wrote in message
news:1193743670.860831.196700@.k35g2000prh.googlegroups.com...
> Hello ,
> Is it possible/recommended to do SQL server instance backups in Single
> user mode ?
> Thanks in advance,
> atv
>
Showing posts with label instance. Show all posts
Showing posts with label instance. Show all posts
Thursday, March 29, 2012
DB Backup - Single User Mode
Hello ,
Is it possible/recommended to do SQL server instance backups in Single
user mode ?
Thanks in advance,
atvIf you mean regular backups (using BACKUP DATABASE and BACKUP LOG commands), then no, no need to set
the database to single user mode...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"velu5" <thirumalaivelu@.gmail.com> wrote in message
news:1193743910.012534.3610@.v29g2000prd.googlegroups.com...
> Hello ,
> Is it possible/recommended to do SQL server instance backups in Single
> user mode ?
> Thanks in advance,
> atv
>|||Tx for your responses.
I completely understand and accept that there is no need to do a
backup in single user mode, but for one case if the server is already
in single user mode ..
Here is what I found, in SQL 2000 it was possible to do the
backups(in single user mode) while the SQL 2005 server fails to accept
the connection for backup (the same code/binary SQL-DMO statements are
used for both).
Are there any major changes between SQL server 2000 and SQL server
2005 ?
On Oct 31, 3:02 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> If you mean regular backups (usingBACKUPDATABASE andBACKUPLOG commands), then no, no need to set
> the database tosingleusermode...
> --
> Tibor Karaszi,SQLServer MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "velu5" <thirumalaiv...@.gmail.com> wrote in message
> news:1193743910.012534.3610@.v29g2000prd.googlegroups.com...
> > Hello ,
> > Is it possible/recommended to doSQLserver instance backups inSingle
> >usermode?
> > Thanks in advance,
> >atv|||What's New in SQL Server 2005:
http://www.microsoft.com/sql/prodinfo/overview/whats-new-in-sqlserver2005.mspx
http://technet.microsoft.com/tr-tr/library/ms170363(en-us).aspx
--
Ekrem Önsoy
"velu5" <thirumalaivelu@.gmail.com> wrote in message
news:eb680ec9-d7c5-40c2-bec6-bbbdeada23af@.e23g2000prf.googlegroups.com...
> Tx for your responses.
> I completely understand and accept that there is no need to do a
> backup in single user mode, but for one case if the server is already
> in single user mode ..
> Here is what I found, in SQL 2000 it was possible to do the
> backups(in single user mode) while the SQL 2005 server fails to accept
> the connection for backup (the same code/binary SQL-DMO statements are
> used for both).
> Are there any major changes between SQL server 2000 and SQL server
> 2005 ?
>
> On Oct 31, 3:02 am, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> If you mean regular backups (usingBACKUPDATABASE andBACKUPLOG commands),
>> then no, no need to set
>> the database tosingleusermode...
>> --
>> Tibor Karaszi,SQLServer
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>> "velu5" <thirumalaiv...@.gmail.com> wrote in message
>> news:1193743910.012534.3610@.v29g2000prd.googlegroups.com...
>> > Hello ,
>> > Is it possible/recommended to doSQLserver instance backups inSingle
>> >usermode?
>> > Thanks in advance,
>> >atv
>|||Hmm, below worked just fine on my machine (2005 with sp2):
USE master
ALTER DATABASE pubs SET SINGLE_USER
BACKUP DATABASE pubs TO DISK = 'C:\pubs.bak'
I can only assume that your DMO code for some reason tries to open a connection to the database on
2005 and which causes the failure. I'd run a Profiler trace to verify what TSQL is submitted.
Assuming this is your own code (using DMO - which is how I read your post), then it might be
difficult to do something... Except for working with the code to see if can stay away from the
database. In the end you might have to construct the backup command and execute is using
.ExecuteImmediately or something similar.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"velu5" <thirumalaivelu@.gmail.com> wrote in message
news:eb680ec9-d7c5-40c2-bec6-bbbdeada23af@.e23g2000prf.googlegroups.com...
> Tx for your responses.
> I completely understand and accept that there is no need to do a
> backup in single user mode, but for one case if the server is already
> in single user mode ..
> Here is what I found, in SQL 2000 it was possible to do the
> backups(in single user mode) while the SQL 2005 server fails to accept
> the connection for backup (the same code/binary SQL-DMO statements are
> used for both).
> Are there any major changes between SQL server 2000 and SQL server
> 2005 ?
>
> On Oct 31, 3:02 am, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> If you mean regular backups (usingBACKUPDATABASE andBACKUPLOG commands), then no, no need to set
>> the database tosingleusermode...
>> --
>> Tibor Karaszi,SQLServer
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>> "velu5" <thirumalaiv...@.gmail.com> wrote in message
>> news:1193743910.012534.3610@.v29g2000prd.googlegroups.com...
>> > Hello ,
>> > Is it possible/recommended to doSQLserver instance backups inSingle
>> >usermode?
>> > Thanks in advance,
>> >atv
>
Is it possible/recommended to do SQL server instance backups in Single
user mode ?
Thanks in advance,
atvIf you mean regular backups (using BACKUP DATABASE and BACKUP LOG commands), then no, no need to set
the database to single user mode...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"velu5" <thirumalaivelu@.gmail.com> wrote in message
news:1193743910.012534.3610@.v29g2000prd.googlegroups.com...
> Hello ,
> Is it possible/recommended to do SQL server instance backups in Single
> user mode ?
> Thanks in advance,
> atv
>|||Tx for your responses.
I completely understand and accept that there is no need to do a
backup in single user mode, but for one case if the server is already
in single user mode ..
Here is what I found, in SQL 2000 it was possible to do the
backups(in single user mode) while the SQL 2005 server fails to accept
the connection for backup (the same code/binary SQL-DMO statements are
used for both).
Are there any major changes between SQL server 2000 and SQL server
2005 ?
On Oct 31, 3:02 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> If you mean regular backups (usingBACKUPDATABASE andBACKUPLOG commands), then no, no need to set
> the database tosingleusermode...
> --
> Tibor Karaszi,SQLServer MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "velu5" <thirumalaiv...@.gmail.com> wrote in message
> news:1193743910.012534.3610@.v29g2000prd.googlegroups.com...
> > Hello ,
> > Is it possible/recommended to doSQLserver instance backups inSingle
> >usermode?
> > Thanks in advance,
> >atv|||What's New in SQL Server 2005:
http://www.microsoft.com/sql/prodinfo/overview/whats-new-in-sqlserver2005.mspx
http://technet.microsoft.com/tr-tr/library/ms170363(en-us).aspx
--
Ekrem Önsoy
"velu5" <thirumalaivelu@.gmail.com> wrote in message
news:eb680ec9-d7c5-40c2-bec6-bbbdeada23af@.e23g2000prf.googlegroups.com...
> Tx for your responses.
> I completely understand and accept that there is no need to do a
> backup in single user mode, but for one case if the server is already
> in single user mode ..
> Here is what I found, in SQL 2000 it was possible to do the
> backups(in single user mode) while the SQL 2005 server fails to accept
> the connection for backup (the same code/binary SQL-DMO statements are
> used for both).
> Are there any major changes between SQL server 2000 and SQL server
> 2005 ?
>
> On Oct 31, 3:02 am, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> If you mean regular backups (usingBACKUPDATABASE andBACKUPLOG commands),
>> then no, no need to set
>> the database tosingleusermode...
>> --
>> Tibor Karaszi,SQLServer
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>> "velu5" <thirumalaiv...@.gmail.com> wrote in message
>> news:1193743910.012534.3610@.v29g2000prd.googlegroups.com...
>> > Hello ,
>> > Is it possible/recommended to doSQLserver instance backups inSingle
>> >usermode?
>> > Thanks in advance,
>> >atv
>|||Hmm, below worked just fine on my machine (2005 with sp2):
USE master
ALTER DATABASE pubs SET SINGLE_USER
BACKUP DATABASE pubs TO DISK = 'C:\pubs.bak'
I can only assume that your DMO code for some reason tries to open a connection to the database on
2005 and which causes the failure. I'd run a Profiler trace to verify what TSQL is submitted.
Assuming this is your own code (using DMO - which is how I read your post), then it might be
difficult to do something... Except for working with the code to see if can stay away from the
database. In the end you might have to construct the backup command and execute is using
.ExecuteImmediately or something similar.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"velu5" <thirumalaivelu@.gmail.com> wrote in message
news:eb680ec9-d7c5-40c2-bec6-bbbdeada23af@.e23g2000prf.googlegroups.com...
> Tx for your responses.
> I completely understand and accept that there is no need to do a
> backup in single user mode, but for one case if the server is already
> in single user mode ..
> Here is what I found, in SQL 2000 it was possible to do the
> backups(in single user mode) while the SQL 2005 server fails to accept
> the connection for backup (the same code/binary SQL-DMO statements are
> used for both).
> Are there any major changes between SQL server 2000 and SQL server
> 2005 ?
>
> On Oct 31, 3:02 am, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> If you mean regular backups (usingBACKUPDATABASE andBACKUPLOG commands), then no, no need to set
>> the database tosingleusermode...
>> --
>> Tibor Karaszi,SQLServer
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>> "velu5" <thirumalaiv...@.gmail.com> wrote in message
>> news:1193743910.012534.3610@.v29g2000prd.googlegroups.com...
>> > Hello ,
>> > Is it possible/recommended to doSQLserver instance backups inSingle
>> >usermode?
>> > Thanks in advance,
>> >atv
>
DB Backup - Single User Mode
Hello ,
It's possible. I can't think of a reason to recommend it unless you
especially needed to prevent any changes (for example if you were backing up
in advance of an upgrade or planning to decommission the original database).
Just to emphasize what David said: Yes, it's possible, but the only reason
you would do it, is because you have already put the database in single-
user mode. That is, it's works perfectly well to have backups running with
users active.
>
Just to emphasize what David said: Yes, it's possible, but the only reason
you would do it, is because you have already put the database insingle-usermode. That is, it's works perfectly well to have backups running with
users active.
>
--
Erland Sommarskog,SQLServer MVP, esq...@.sommarskog.se
>
Books Online forSQLServer 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online forSQLServer 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||"velu5" <thirumalaivelu@.gmail.comwrote in message
news:3686490e-167c-4062-9ecf-2c043615e22a@.s19g2000prg.googlegroups.com...
Hmm, you sure you don't something else already making a connection?
Yes. Many changes. But I don't know any specifically that would cause this
particular issue.
>>
>Just to emphasize what David said: Yes, it's possible, but the only
>reason
>you would do it, is because you have already put the database
>insingle-usermode. That is, it's works perfectly well to have backups
>running with
>users active.
>>
>--
>Erland Sommarskog,SQLServer MVP, esq...@.sommarskog.se
>>
>Books Online forSQLServer 2005
>athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
>Books Online forSQLServer 2000
>athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||velu5 (thirumalaivelu@.gmail.com) writes:
Well, DMO became dusty and old with SQL 2005, and it's possible
that DMO somehow manages to cause double connections.
Is it possible/recommended to do SQL server instance backups in Single
user mode ?
Thanks in advance,
atv"velu5" <thirumalaivelu@.gmail.comwrote in message
news:1193743908.840076.37120@.t8g2000prg.googlegrou ps.com...
Quote:
Originally Posted by
Hello ,
>
Is it possible/recommended to do SQL server instance backups in Single
user mode ?
>
Thanks in advance,
atv
>
It's possible. I can't think of a reason to recommend it unless you
especially needed to prevent any changes (for example if you were backing up
in advance of an upgrade or planning to decommission the original database).
--
David Portas|||velu5 (thirumalaivelu@.gmail.com) writes:
Quote:
Originally Posted by
Is it possible/recommended to do SQL server instance backups in Single
user mode ?
Just to emphasize what David said: Yes, it's possible, but the only reason
you would do it, is because you have already put the database in single-
user mode. That is, it's works perfectly well to have backups running with
users active.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Tx for your response.
I completely understand and accept that there is no need to do a
backup in single user mode, but for one case if the server is already
in single user mode ..
Here is what I found, in SQL 2000 it was possible to do the
backups(in single user mode) while the SQL 2005 server fails to accept
the connection for backup (the same code/binary SQL-DMO statements are
used for both).
Are there any major changes between SQL server 2000 and SQL server
2005 ?
On Oct 31, 3:00 am, Erland Sommarskog <esq...@.sommarskog.sewrote:
Quote:
Originally Posted by
velu5 (thirumalaiv...@.gmail.com) writes:
Quote:
Originally Posted by
Is it possible/recommended to doSQLserver instance backups inSingle
usermode?
>
Just to emphasize what David said: Yes, it's possible, but the only reason
you would do it, is because you have already put the database insingle-usermode. That is, it's works perfectly well to have backups running with
users active.
>
--
Erland Sommarskog,SQLServer MVP, esq...@.sommarskog.se
>
Books Online forSQLServer 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online forSQLServer 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||"velu5" <thirumalaivelu@.gmail.comwrote in message
news:3686490e-167c-4062-9ecf-2c043615e22a@.s19g2000prg.googlegroups.com...
Quote:
Originally Posted by
Tx for your response.
I completely understand and accept that there is no need to do a
backup in single user mode, but for one case if the server is already
in single user mode ..
Here is what I found, in SQL 2000 it was possible to do the
backups(in single user mode) while the SQL 2005 server fails to accept
the connection for backup (the same code/binary SQL-DMO statements are
used for both).
>
Hmm, you sure you don't something else already making a connection?
Quote:
Originally Posted by
Are there any major changes between SQL server 2000 and SQL server
2005 ?
>
Yes. Many changes. But I don't know any specifically that would cause this
particular issue.
Quote:
Originally Posted by
On Oct 31, 3:00 am, Erland Sommarskog <esq...@.sommarskog.sewrote:
Quote:
Originally Posted by
>velu5 (thirumalaiv...@.gmail.com) writes:
Quote:
Originally Posted by
Is it possible/recommended to doSQLserver instance backups inSingle
>usermode?
>>
>Just to emphasize what David said: Yes, it's possible, but the only
>reason
>you would do it, is because you have already put the database
>insingle-usermode. That is, it's works perfectly well to have backups
>running with
>users active.
>>
>--
>Erland Sommarskog,SQLServer MVP, esq...@.sommarskog.se
>>
>Books Online forSQLServer 2005
>athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
>Books Online forSQLServer 2000
>athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||velu5 (thirumalaivelu@.gmail.com) writes:
Quote:
Originally Posted by
Tx for your response.
I completely understand and accept that there is no need to do a
backup in single user mode, but for one case if the server is already
in single user mode ..
Here is what I found, in SQL 2000 it was possible to do the
backups(in single user mode) while the SQL 2005 server fails to accept
the connection for backup (the same code/binary SQL-DMO statements are
used for both).
>
Are there any major changes between SQL server 2000 and SQL server
2005 ?
Well, DMO became dusty and old with SQL 2005, and it's possible
that DMO somehow manages to cause double connections.
I've always stayed away from DMO (and its successor SMO), so I cannot
really say much more.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Wednesday, March 21, 2012
DateTime Problem
i'm having a web forum, i wanna make a page to show all today's posts, so for instance i tried to do this in the SQL statment:
|||thanx too much it worked pretty fine, but i made it like this and its perfect:
Where Posts.DatePosted Between #09/20/2005# AND #09/22/2005#
but this erroe appeared to me:
Incorrect syntax near '#'
so can you please tell me better way and exact way to do this, thanx too much
Where Posts.DatePosted >= '20050920' ANDPosts.DatePosted < '20050923'
|||thanx too much it worked pretty fine, but i made it like this and its perfect:
HAVING MAX(Posts.DatePosted) >= '09/22/2005' AND MAX(Posts.DatePosted) < '09/23/2005'
Sunday, March 11, 2012
DateTime Format in Localized version of MSDE
Hi
How do we determine the date time format in a SQL Server instance.
Specifically I would like to know, if the date time data type in SQL Server
is Language Specific or Language Neutral.
We are facing the following problem. I have a managed app, which is
localized. I need to update some data from the managed app to the
database(we are using MSDE). When I run the managed app in Italian locale,
with Italian build of MSDE, the database update fails.
The problem we figured out was, the date time cast in database fails. This
is because the time separator(for Italian locale) in .NET app is a period,
while in SQL MSDE(Italian build) it is a colon (
The following are my queries.
1. Is Date Time data type in SQL Language specific or Language Neutral? If
it is Language Neutral, I assume it will use the en-US culture, correct me
if I am wrong.
2. If date time is language specific, how is the collation set. Is it set by
default when MSDE is installed? Will the Operating System language version,
impact the collation, while installing MSDE.
3. When I run the query 'Select GetDate()' in Query Analyzer, the time
separator is displayed as a colon. Does the language version of SQL Server
tools(query analyzer/enterprise manager) have an impact on the date time
displayed?
Your inputs will help me a lot. Please reply to my ID (Ramjee_t@.infosys.com)
Thanks
RT
In message <ep1Idw#aFHA.580@.TK2MSFTNGP15.phx.gbl>, ramjee
<ramjee_t@.infosys.com> writes
>Hi
>How do we determine the date time format in a SQL Server instance.
>Specifically I would like to know, if the date time data type in SQL Server
>is Language Specific or Language Neutral.
>We are facing the following problem. I have a managed app, which is
>localized. I need to update some data from the managed app to the
>database(we are using MSDE). When I run the managed app in Italian locale,
>with Italian build of MSDE, the database update fails.
>The problem we figured out was, the date time cast in database fails. This
>is because the time separator(for Italian locale) in .NET app is a period,
>while in SQL MSDE(Italian build) it is a colon (
>The following are my queries.
>1. Is Date Time data type in SQL Language specific or Language Neutral? If
>it is Language Neutral, I assume it will use the en-US culture, correct me
>if I am wrong.
Not exactly. Physically in the database it is always stored the same way
however, the collation order does determine some of the supported
formats displaying and updating a DateTime field.
>2. If date time is language specific, how is the collation set. Is it set by
>default when MSDE is installed? Will the Operating System language version,
>impact the collation, while installing MSDE.
The default collation order is set when the instance of MSDE is
installed. However under MSDE 2000 / SQL Server 2000 the collation order
of each database can be different. Thats up to you when you CREATE the
DATABASE (ie: you determine the default collation order for each
database). In addtion, you can specify the collation order to use on
each Table and Field if really required. Check BOL for the CREATE
DATABASE and TABLE. You are therefore quite capable of using the same
collation order for every instance of MSDE you install regardless of
country.
>3. When I run the query 'Select GetDate()' in Query Analyzer, the time
>separator is displayed as a colon. Does the language version of SQL Server
>tools(query analyzer/enterprise manager) have an impact on the date time
>displayed?
Its all about handling dates in a consistent manor.
Its generally a good idea to always update a DataTime field using the
universal format "yyyy-mm-dd hh:nn:ss". By doing this, MSDE never gets
confused about which part is the month and day (ie: 2005-01-05 is always
5th Jan whereas 05-01-2005 could be 5th Jan or 1st May). Again, this
also solves international differences.
Its also therefore generally a good idea to always retrieve the DateTime
in a known format. Therefore using a command like "SELECT
Convert(datetime, MyDateField, 102) as MyDate FROM ..." would always
return the date in a UK format for example. That way your application
does not get confused and the localisation to the client is left to your
application.
>Your inputs will help me a lot. Please reply to my ID (Ramjee_t@.infosys.com)
No Problem.
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com
|||Hi Andrew,
You are mixing up collation, which is a property of character type columns
and variables in SQL Server, and the language that can be set for a
connection or user. The last one determines how dates as strings are
interpreted.
1) You are right that datetime and smalldatetime in SQL Server are stored in
a binary, language-neutral format. How the datetimes are displayed depends
on the client application however. For example Query Analyzer will by
default display dates in yyyy-mm-dd hh:mm:ss format. Enterprise Manager on
the other hand will use your Windows local settings to decide the display
format. How dates as strings are interpreted when inserting, updating or
deleting depends on the language setting for the connection, which are by
default derived from the language settings for the current user, although
they can be set explicitly with SET LANGUAGE.
2) As I said earlier, collation is irrelevant for datetime. The default
language settings for the user (login) are derived from the language in
which SQL Server is installed, but can be specified explicitly when creating
the login, or changed afterwards.
3) "yyyy-mm-dd hh:nn:ss" is not a safe format for datetime. Try the
following:
SET LANGUAGE us_english
SELECT CAST('2005-06-14 00:00:00' AS DATETIME)
GO
SET LANGUAGE british
SELECT CAST('2005-06-14 00:00:00' AS DATETIME)
There are 2 safe date formats in SQL Server:
yyyymmdd
and
yyyy-mm-ddThh:mm:ss
It is _not_ a good idea to always retrieve the datetime in a known string
format. Just retrieve the datetime as datetime, and let your application and
your user decide how to display is in a human-readable format. A properly
designed application will just use the Regional Settings from Windows to
decide how to display dates, and if you return datetime in a string format,
you just end up converting datetime values twice.
Jacco Schalkwijk
SQL Server MVP
"Andrew D. Newbould" <newsgroups@.NOzadSPANsoft.com> wrote in message
news:n0wpLfBXbrpCFwsj@.zadsoft.gotadsl.co.uk...
> In message <ep1Idw#aFHA.580@.TK2MSFTNGP15.phx.gbl>, ramjee
> <ramjee_t@.infosys.com> writes
> Not exactly. Physically in the database it is always stored the same way
> however, the collation order does determine some of the supported formats
> displaying and updating a DateTime field.
>
> The default collation order is set when the instance of MSDE is installed.
> However under MSDE 2000 / SQL Server 2000 the collation order of each
> database can be different. Thats up to you when you CREATE the DATABASE
> (ie: you determine the default collation order for each database). In
> addtion, you can specify the collation order to use on each Table and
> Field if really required. Check BOL for the CREATE DATABASE and TABLE. You
> are therefore quite capable of using the same collation order for every
> instance of MSDE you install regardless of country.
>
> Its all about handling dates in a consistent manor.
> Its generally a good idea to always update a DataTime field using the
> universal format "yyyy-mm-dd hh:nn:ss". By doing this, MSDE never gets
> confused about which part is the month and day (ie: 2005-01-05 is always
> 5th Jan whereas 05-01-2005 could be 5th Jan or 1st May). Again, this also
> solves international differences.
> Its also therefore generally a good idea to always retrieve the DateTime
> in a known format. Therefore using a command like "SELECT
> Convert(datetime, MyDateField, 102) as MyDate FROM ..." would always
> return the date in a UK format for example. That way your application does
> not get confused and the localisation to the client is left to your
> application.
>
> No Problem.
> --
> Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
> ZAD Software Systems Web : www.zadsoft.com
How do we determine the date time format in a SQL Server instance.
Specifically I would like to know, if the date time data type in SQL Server
is Language Specific or Language Neutral.
We are facing the following problem. I have a managed app, which is
localized. I need to update some data from the managed app to the
database(we are using MSDE). When I run the managed app in Italian locale,
with Italian build of MSDE, the database update fails.
The problem we figured out was, the date time cast in database fails. This
is because the time separator(for Italian locale) in .NET app is a period,
while in SQL MSDE(Italian build) it is a colon (
The following are my queries.
1. Is Date Time data type in SQL Language specific or Language Neutral? If
it is Language Neutral, I assume it will use the en-US culture, correct me
if I am wrong.
2. If date time is language specific, how is the collation set. Is it set by
default when MSDE is installed? Will the Operating System language version,
impact the collation, while installing MSDE.
3. When I run the query 'Select GetDate()' in Query Analyzer, the time
separator is displayed as a colon. Does the language version of SQL Server
tools(query analyzer/enterprise manager) have an impact on the date time
displayed?
Your inputs will help me a lot. Please reply to my ID (Ramjee_t@.infosys.com)
Thanks
RT
In message <ep1Idw#aFHA.580@.TK2MSFTNGP15.phx.gbl>, ramjee
<ramjee_t@.infosys.com> writes
>Hi
>How do we determine the date time format in a SQL Server instance.
>Specifically I would like to know, if the date time data type in SQL Server
>is Language Specific or Language Neutral.
>We are facing the following problem. I have a managed app, which is
>localized. I need to update some data from the managed app to the
>database(we are using MSDE). When I run the managed app in Italian locale,
>with Italian build of MSDE, the database update fails.
>The problem we figured out was, the date time cast in database fails. This
>is because the time separator(for Italian locale) in .NET app is a period,
>while in SQL MSDE(Italian build) it is a colon (
>The following are my queries.
>1. Is Date Time data type in SQL Language specific or Language Neutral? If
>it is Language Neutral, I assume it will use the en-US culture, correct me
>if I am wrong.
Not exactly. Physically in the database it is always stored the same way
however, the collation order does determine some of the supported
formats displaying and updating a DateTime field.
>2. If date time is language specific, how is the collation set. Is it set by
>default when MSDE is installed? Will the Operating System language version,
>impact the collation, while installing MSDE.
The default collation order is set when the instance of MSDE is
installed. However under MSDE 2000 / SQL Server 2000 the collation order
of each database can be different. Thats up to you when you CREATE the
DATABASE (ie: you determine the default collation order for each
database). In addtion, you can specify the collation order to use on
each Table and Field if really required. Check BOL for the CREATE
DATABASE and TABLE. You are therefore quite capable of using the same
collation order for every instance of MSDE you install regardless of
country.
>3. When I run the query 'Select GetDate()' in Query Analyzer, the time
>separator is displayed as a colon. Does the language version of SQL Server
>tools(query analyzer/enterprise manager) have an impact on the date time
>displayed?
Its all about handling dates in a consistent manor.
Its generally a good idea to always update a DataTime field using the
universal format "yyyy-mm-dd hh:nn:ss". By doing this, MSDE never gets
confused about which part is the month and day (ie: 2005-01-05 is always
5th Jan whereas 05-01-2005 could be 5th Jan or 1st May). Again, this
also solves international differences.
Its also therefore generally a good idea to always retrieve the DateTime
in a known format. Therefore using a command like "SELECT
Convert(datetime, MyDateField, 102) as MyDate FROM ..." would always
return the date in a UK format for example. That way your application
does not get confused and the localisation to the client is left to your
application.
>Your inputs will help me a lot. Please reply to my ID (Ramjee_t@.infosys.com)
No Problem.
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com
|||Hi Andrew,
You are mixing up collation, which is a property of character type columns
and variables in SQL Server, and the language that can be set for a
connection or user. The last one determines how dates as strings are
interpreted.
1) You are right that datetime and smalldatetime in SQL Server are stored in
a binary, language-neutral format. How the datetimes are displayed depends
on the client application however. For example Query Analyzer will by
default display dates in yyyy-mm-dd hh:mm:ss format. Enterprise Manager on
the other hand will use your Windows local settings to decide the display
format. How dates as strings are interpreted when inserting, updating or
deleting depends on the language setting for the connection, which are by
default derived from the language settings for the current user, although
they can be set explicitly with SET LANGUAGE.
2) As I said earlier, collation is irrelevant for datetime. The default
language settings for the user (login) are derived from the language in
which SQL Server is installed, but can be specified explicitly when creating
the login, or changed afterwards.
3) "yyyy-mm-dd hh:nn:ss" is not a safe format for datetime. Try the
following:
SET LANGUAGE us_english
SELECT CAST('2005-06-14 00:00:00' AS DATETIME)
GO
SET LANGUAGE british
SELECT CAST('2005-06-14 00:00:00' AS DATETIME)
There are 2 safe date formats in SQL Server:
yyyymmdd
and
yyyy-mm-ddThh:mm:ss
It is _not_ a good idea to always retrieve the datetime in a known string
format. Just retrieve the datetime as datetime, and let your application and
your user decide how to display is in a human-readable format. A properly
designed application will just use the Regional Settings from Windows to
decide how to display dates, and if you return datetime in a string format,
you just end up converting datetime values twice.
Jacco Schalkwijk
SQL Server MVP
"Andrew D. Newbould" <newsgroups@.NOzadSPANsoft.com> wrote in message
news:n0wpLfBXbrpCFwsj@.zadsoft.gotadsl.co.uk...
> In message <ep1Idw#aFHA.580@.TK2MSFTNGP15.phx.gbl>, ramjee
> <ramjee_t@.infosys.com> writes
> Not exactly. Physically in the database it is always stored the same way
> however, the collation order does determine some of the supported formats
> displaying and updating a DateTime field.
>
> The default collation order is set when the instance of MSDE is installed.
> However under MSDE 2000 / SQL Server 2000 the collation order of each
> database can be different. Thats up to you when you CREATE the DATABASE
> (ie: you determine the default collation order for each database). In
> addtion, you can specify the collation order to use on each Table and
> Field if really required. Check BOL for the CREATE DATABASE and TABLE. You
> are therefore quite capable of using the same collation order for every
> instance of MSDE you install regardless of country.
>
> Its all about handling dates in a consistent manor.
> Its generally a good idea to always update a DataTime field using the
> universal format "yyyy-mm-dd hh:nn:ss". By doing this, MSDE never gets
> confused about which part is the month and day (ie: 2005-01-05 is always
> 5th Jan whereas 05-01-2005 could be 5th Jan or 1st May). Again, this also
> solves international differences.
> Its also therefore generally a good idea to always retrieve the DateTime
> in a known format. Therefore using a command like "SELECT
> Convert(datetime, MyDateField, 102) as MyDate FROM ..." would always
> return the date in a UK format for example. That way your application does
> not get confused and the localisation to the client is left to your
> application.
>
> No Problem.
> --
> Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
> ZAD Software Systems Web : www.zadsoft.com
Sunday, February 19, 2012
DATEDIFF Return Monday - Friday or Just weekdays
I have a query and am trying to just return the difference between two dates but not include weekends.
For instance, if I have 08/21/2006 - 08/28/2006, there are 6 weekdays.
I tried this, but I am getting 7 as a result.
SELECTDATEDIFF(weekday, request_start_date, request_end_date)AS days_off, request_idFROM requestAny help would be greatly appreciated.
You may find this UDF helpful
http://www.sqlservercentral.com/columnists/sjones/businessdays.asp
|||You need DateDiff with the correct DatePart so I think you need either DayofYear or Hours so you can convert it back to days. Try the link below for details. Hope this helps.
http://msdn2.microsoft.com/en-us/library/ms189794.aspx
Subscribe to:
Posts (Atom)