DB Backup on a compressed drive

Is this a good idea to use compressed drive (Windows NTFS compression) for D
B
backup files?
What is the common practice and what are the risks?
Thanks,
VladVlad,
See http://support.microsoft.com/defaul...b;en-us;231347.
"Performing SQL Server database backups to disk files on compressed volumes
is also not recommended."
HTH
Jerry
"Vlad" <Vlad@.discussions.microsoft.com> wrote in message
news:579552B6-8EF5-4741-8F7E-B04BF8EABEF6@.microsoft.com...
> Is this a good idea to use compressed drive (Windows NTFS compression) for
> DB
> backup files?
> What is the common practice and what are the risks?
> --
> Thanks,
> Vlad|||That article applies to the database files themselves, not the backups.
Personally, I prefer not to use compressed drives for backup. I prefer
using a commercial SQL backup compression tool. This decreases the time to
get the data out of SQL, thus minimizing the impact on the host system.
Since I also prefer backing up directly to a network file share, this has
the additional benefit of reducing the network traffic.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uU8t$dWuFHA.3236@.TK2MSFTNGP14.phx.gbl...
> Vlad,
> See http://support.microsoft.com/defaul...b;en-us;231347.
> "Performing SQL Server database backups to disk files on compressed
> volumes is also not recommended."
> HTH
> Jerry
> "Vlad" <Vlad@.discussions.microsoft.com> wrote in message
> news:579552B6-8EF5-4741-8F7E-B04BF8EABEF6@.microsoft.com...
>|||Geoff,
From the article:
"Performing SQL Server database backups to disk files on compressed volumes
is also not recommended. The failure modes are still under investigation,
but some evidence indicates that this backup is less reliable than backing
up to regular non-compressed volumes. If backups to compressed volumes are
performed, a complete DBCC should be run before each backup and after each
restore. The time required to run the DBCC should be factored into the
decision to use this backup technique. "
Jerry
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:OCyqJnWuFHA.1132@.TK2MSFTNGP10.phx.gbl...
> That article applies to the database files themselves, not the backups.
> Personally, I prefer not to use compressed drives for backup. I prefer
> using a commercial SQL backup compression tool. This decreases the time
> to get the data out of SQL, thus minimizing the impact on the host system.
> Since I also prefer backing up directly to a network file share, this has
> the additional benefit of reducing the network traffic.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:uU8t$dWuFHA.3236@.TK2MSFTNGP14.phx.gbl...
>sql

DB Backup on a compressed drive

Is this a good idea to use compressed drive (Windows NTFS compression) for DB
backup files?
What is the common practice and what are the risks?
Thanks,
Vlad
Vlad,
See http://support.microsoft.com/default...;en-us;231347.
"Performing SQL Server database backups to disk files on compressed volumes
is also not recommended."
HTH
Jerry
"Vlad" <Vlad@.discussions.microsoft.com> wrote in message
news:579552B6-8EF5-4741-8F7E-B04BF8EABEF6@.microsoft.com...
> Is this a good idea to use compressed drive (Windows NTFS compression) for
> DB
> backup files?
> What is the common practice and what are the risks?
> --
> Thanks,
> Vlad
|||That article applies to the database files themselves, not the backups.
Personally, I prefer not to use compressed drives for backup. I prefer
using a commercial SQL backup compression tool. This decreases the time to
get the data out of SQL, thus minimizing the impact on the host system.
Since I also prefer backing up directly to a network file share, this has
the additional benefit of reducing the network traffic.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uU8t$dWuFHA.3236@.TK2MSFTNGP14.phx.gbl...
> Vlad,
> See http://support.microsoft.com/default...;en-us;231347.
> "Performing SQL Server database backups to disk files on compressed
> volumes is also not recommended."
> HTH
> Jerry
> "Vlad" <Vlad@.discussions.microsoft.com> wrote in message
> news:579552B6-8EF5-4741-8F7E-B04BF8EABEF6@.microsoft.com...
>
|||Geoff,
From the article:
"Performing SQL Server database backups to disk files on compressed volumes
is also not recommended. The failure modes are still under investigation,
but some evidence indicates that this backup is less reliable than backing
up to regular non-compressed volumes. If backups to compressed volumes are
performed, a complete DBCC should be run before each backup and after each
restore. The time required to run the DBCC should be factored into the
decision to use this backup technique. "
Jerry
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:OCyqJnWuFHA.1132@.TK2MSFTNGP10.phx.gbl...
> That article applies to the database files themselves, not the backups.
> Personally, I prefer not to use compressed drives for backup. I prefer
> using a commercial SQL backup compression tool. This decreases the time
> to get the data out of SQL, thus minimizing the impact on the host system.
> Since I also prefer backing up directly to a network file share, this has
> the additional benefit of reducing the network traffic.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:uU8t$dWuFHA.3236@.TK2MSFTNGP14.phx.gbl...
>

DB Backup on a compressed drive

Is this a good idea to use compressed drive (Windows NTFS compression) for DB
backup files?
What is the common practice and what are the risks?
--
Thanks,
VladVlad,
See http://support.microsoft.com/default.aspx?scid=kb;en-us;231347.
"Performing SQL Server database backups to disk files on compressed volumes
is also not recommended."
HTH
Jerry
"Vlad" <Vlad@.discussions.microsoft.com> wrote in message
news:579552B6-8EF5-4741-8F7E-B04BF8EABEF6@.microsoft.com...
> Is this a good idea to use compressed drive (Windows NTFS compression) for
> DB
> backup files?
> What is the common practice and what are the risks?
> --
> Thanks,
> Vlad|||That article applies to the database files themselves, not the backups.
Personally, I prefer not to use compressed drives for backup. I prefer
using a commercial SQL backup compression tool. This decreases the time to
get the data out of SQL, thus minimizing the impact on the host system.
Since I also prefer backing up directly to a network file share, this has
the additional benefit of reducing the network traffic.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uU8t$dWuFHA.3236@.TK2MSFTNGP14.phx.gbl...
> Vlad,
> See http://support.microsoft.com/default.aspx?scid=kb;en-us;231347.
> "Performing SQL Server database backups to disk files on compressed
> volumes is also not recommended."
> HTH
> Jerry
> "Vlad" <Vlad@.discussions.microsoft.com> wrote in message
> news:579552B6-8EF5-4741-8F7E-B04BF8EABEF6@.microsoft.com...
>> Is this a good idea to use compressed drive (Windows NTFS compression)
>> for DB
>> backup files?
>> What is the common practice and what are the risks?
>> --
>> Thanks,
>> Vlad
>|||Geoff,
From the article:
"Performing SQL Server database backups to disk files on compressed volumes
is also not recommended. The failure modes are still under investigation,
but some evidence indicates that this backup is less reliable than backing
up to regular non-compressed volumes. If backups to compressed volumes are
performed, a complete DBCC should be run before each backup and after each
restore. The time required to run the DBCC should be factored into the
decision to use this backup technique. "
Jerry
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:OCyqJnWuFHA.1132@.TK2MSFTNGP10.phx.gbl...
> That article applies to the database files themselves, not the backups.
> Personally, I prefer not to use compressed drives for backup. I prefer
> using a commercial SQL backup compression tool. This decreases the time
> to get the data out of SQL, thus minimizing the impact on the host system.
> Since I also prefer backing up directly to a network file share, this has
> the additional benefit of reducing the network traffic.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:uU8t$dWuFHA.3236@.TK2MSFTNGP14.phx.gbl...
>> Vlad,
>> See http://support.microsoft.com/default.aspx?scid=kb;en-us;231347.
>> "Performing SQL Server database backups to disk files on compressed
>> volumes is also not recommended."
>> HTH
>> Jerry
>> "Vlad" <Vlad@.discussions.microsoft.com> wrote in message
>> news:579552B6-8EF5-4741-8F7E-B04BF8EABEF6@.microsoft.com...
>> Is this a good idea to use compressed drive (Windows NTFS compression)
>> for DB
>> backup files?
>> What is the common practice and what are the risks?
>> --
>> Thanks,
>> Vlad
>>
>

Datum Variable

Hi,
how I can assign a date to a variable ?:o
I have no idea .... any smal tip ?
thx
thumbyou do not know this and it is time for mid terms. college gets more expensive and you get less out of it.

Declare @.MyVar datetime

SET @.MyVar = GETDATE()|||thanks for your answer and your time.

my problem was

DECLARE @.geburtstag DATETIME

SET @.geburtstag = '23.07.1968'

correctly is that

DECLARE @.geburtstag DATETIME

SET @.geburtstag = '23-07-1968'

thx

thumb

DateTime Bugs?

Hi,
Below shown simple script to get the wday. Any idea why the wday
for spanish datetime is 1 instead of 2 for 'Ene 16 2006 2:00PM' ('Jan 16
2006 2:00PM') '
TEST
--
print DATEPART(dw,'Jan 16 2006 2:00PM')
SET LANGUAGE spanish
print getdate()
declare @.datetime datetime
set @.datetime = convert(datetime, 'Ene 16 2006 2:00PM', 121)
print @.datetime
print DATEPART(dw,@.datetime)
print DATEPART(dw,convert(datetime, 'Ene 16 2006 2:00PM', 109))
SET LANGUAGE us_english
OUTPUT
--
2
Changed language setting to Espaol.
Ene 19 2006 9:58PM
Ene 16 2006 2:00PM
1
1
Changed language setting to us_english.
Thanks,
KennyI believe it is something to do with which day of the w to be considered
as first day. As default (English) it is Sunday.
If you issue SET DATEFIRST 7 (7 represents Sunday) just before DATAEPART
function, it should solve your "bug".
print DATEPART(dw,'Jan 16 2006 2:00PM')
SET LANGUAGE spanish
print getdate()
declare @.datetime datetime
set @.datetime = convert(datetime, 'Ene 16 2006 2:00PM', 121)
print @.datetime
SET DATEFIRST 7
print DATEPART(dw,@.datetime)
print DATEPART(dw,convert(datetime, 'Ene 16 2006 2:00PM', 109))
SET LANGUAGE us_english
"Kenny" <keejh@.hotmail.com> wrote in message
news:%23XFnc6WHGHA.3936@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Below shown simple script to get the wday. Any idea why the wday
> for spanish datetime is 1 instead of 2 for 'Ene 16 2006 2:00PM' ('Jan 16
> 2006 2:00PM') '
> TEST
> --
> print DATEPART(dw,'Jan 16 2006 2:00PM')
> SET LANGUAGE spanish
> print getdate()
> declare @.datetime datetime
> set @.datetime = convert(datetime, 'Ene 16 2006 2:00PM', 121)
> print @.datetime
> print DATEPART(dw,@.datetime)
> print DATEPART(dw,convert(datetime, 'Ene 16 2006 2:00PM', 109))
> SET LANGUAGE us_english
> OUTPUT
> --
> 2
> Changed language setting to Espaol.
> Ene 19 2006 9:58PM
> Ene 16 2006 2:00PM
> 1
> 1
> Changed language setting to us_english.
> Thanks,
> Kenny
>|||Microsoft failed to follow ISO standards about day of the wek numbers.
They also wrote their own version of ws-within-year numbers.|||What are you talking about? 8601 was not even out until 1988 and was not
popular until second version in 2000. Sybase was created before that.
Also, check the calendar on your desk. It starts with Sunday. People were
using start of w on Sunday long before ISO. Besides, you can change the
start day anyway.
William Stacey [MVP]
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1137739565.063520.234520@.g14g2000cwa.googlegroups.com...
| Microsoft failed to follow ISO standards about day of the wek numbers.
| They also wrote their own version of ws-within-year numbers.
||||Hello, Joe
Indeed, the w numbers returned by the DATEPART are not the ISO w
numbers. There is an example in Books Online on how to create a UDF to
return the ISO w number. However, the original poster was talking
about wdays, not w numbers (which is a completely different
thing).
Razvan|||As indicated in other posts, day of w is dependent on which country you l
ive in. In the US,
Sunday is the first day of the w. In Sweden (and majority of Europe, prob
ably all), first day of
w is Monday. DATEPART to calculate day of w is dependent on SET LANGUA
GE and can be overridden
with SET DATEFIRST.
set language us_english
print DATEPART(dw,getdate())
set language british
print DATEPART(dw,getdate())
set language spanish
print DATEPART(dw,getdate())
set language polish
print DATEPART(dw,getdate())
set language german
print DATEPART(dw,getdate())
set language swedish
print DATEPART(dw,getdate())
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kenny" <keejh@.hotmail.com> wrote in message news:%23XFnc6WHGHA.3936@.TK2MSFTNGP12.phx.gbl..
.
> Hi,
> Below shown simple script to get the wday. Any idea why the wday
for spanish datetime is
> 1 instead of 2 for 'Ene 16 2006 2:00PM' ('Jan 16 2006 2:00PM') '
> TEST
> --
> print DATEPART(dw,'Jan 16 2006 2:00PM')
> SET LANGUAGE spanish
> print getdate()
> declare @.datetime datetime
> set @.datetime = convert(datetime, 'Ene 16 2006 2:00PM', 121)
> print @.datetime
> print DATEPART(dw,@.datetime)
> print DATEPART(dw,convert(datetime, 'Ene 16 2006 2:00PM', 109))
> SET LANGUAGE us_english
> OUTPUT
> --
> 2
> Changed language setting to Espaol.
> Ene 19 2006 9:58PM
> Ene 16 2006 2:00PM
> 1
> 1
> Changed language setting to us_english.
> Thanks,
> Kenny
>

Dates of a week

Hi! I have the week number and the year. I want get all the dates that fall
in that week.
Is anyone who has idea to get this?
Barentry this
CREATE FUNCTION [dbo].[fnStartDayOfWeek](
@.date datetime )
RETURNS datetime
BEGIN
SET @.date = CONVERT(varchar(10), @.date, 111)
RETURN DATEADD(DD, 1 - DATEPART(DW, @.date), @.date)
END
GO
CREATE FUNCTION [dbo].[fnLastDayOfWeek](
@.date datetime )
RETURNS datetime
BEGIN
SET @.date = CONVERT(varchar(10), @.date, 111)
RETURN DATEADD(DD, 1 - DATEPART(DW, @.date)+6, @.date)
END
GO
DECLARE @.StartOfYear varchar(10)
DECLARE @.year varchar(4)
DECLARE @.WeekNo int
SET @.WeekNo = 2
SET @.Year = '2005'
SET @.StartOfYear = @.Year+'0101'
SELECT dbo. fnLastDayOfWeek(DATEADD(dd,7*@.WeekNo,@.St
artOfYear) )
SELECT dbo. fnStartDayOfWeek(DATEADD(dd,7*@.WeekNo,@.S
tartOfYear) )
Aneessh R
"Baren" <Baren@.discussions.microsoft.com> wrote in message
news:2216A3EB-5EA3-4BA3-9119-0758F4A055F2@.microsoft.com...
> Hi! I have the week number and the year. I want get all the dates that
> fall
> in that week.
> Is anyone who has idea to get this?
> Baren|||There are many good reasons to have a 'calendar' table in your database.
This is one of them.
See:
http://www.aspfaq.com/show.asp?id=2519
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Baren" <Baren@.discussions.microsoft.com> wrote in message
news:2216A3EB-5EA3-4BA3-9119-0758F4A055F2@.microsoft.com...
> Hi! I have the week number and the year. I want get all the dates that
> fall
> in that week.
> Is anyone who has idea to get this?
> Baren

Dates of a week

Hi! I have the week number and the year. I want get all the dates that fall
in that week.
Is anyone who has idea to get this?
Barentry this
CREATE FUNCTION [dbo].[fnStartDayOfWeek](
@.date datetime )
RETURNS datetime
BEGIN
SET @.date = CONVERT(varchar(10), @.date, 111)
RETURN DATEADD(DD, 1 - DATEPART(DW, @.date), @.date)
END
GO
CREATE FUNCTION [dbo].[fnLastDayOfWeek](
@.date datetime )
RETURNS datetime
BEGIN
SET @.date = CONVERT(varchar(10), @.date, 111)
RETURN DATEADD(DD, 1 - DATEPART(DW, @.date)+6, @.date)
END
GO
DECLARE @.StartOfYear varchar(10)
DECLARE @.year varchar(4)
DECLARE @.WeekNo int
SET @.WeekNo = 2
SET @.Year = '2005'
SET @.StartOfYear = @.Year+'0101'
SELECT dbo.fnLastDayOfWeek(DATEADD(dd,7*@.WeekNo,@.StartOfYear) )
SELECT dbo.fnStartDayOfWeek(DATEADD(dd,7*@.WeekNo,@.StartOfYear) )
Aneessh R
"Baren" <Baren@.discussions.microsoft.com> wrote in message
news:2216A3EB-5EA3-4BA3-9119-0758F4A055F2@.microsoft.com...
> Hi! I have the week number and the year. I want get all the dates that
> fall
> in that week.
> Is anyone who has idea to get this?
> Baren|||There are many good reasons to have a 'calendar' table in your database.
This is one of them.
See:
http://www.aspfaq.com/show.asp?id=2519
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Baren" <Baren@.discussions.microsoft.com> wrote in message
news:2216A3EB-5EA3-4BA3-9119-0758F4A055F2@.microsoft.com...
> Hi! I have the week number and the year. I want get all the dates that
> fall
> in that week.
> Is anyone who has idea to get this?
> Baren

Date/Time select Problem

Hi NG,

i've got a mystic problem i can't solve, perhaps one of you has a good idea...
i will explain with some code, so fo better understanding my probroblem:
i'll use asp, but for testing the syntax i use the MS Query Aanalyzer.

following Stored Procedure is ok and does it's job fine with "EXECUTE
IntDayView"

CREATE PROCEDURE dbo.IntDayView AS
SELECT Mitarbeiter.UserName, Interviews.Interview_Nr, Ergebnisse.Thema_Nr,
Ergebnisse.Bewertung, Ergebnisse.Bemerkung
FROM Interviews, Mitarbeiter, Ergebnisse
WHERE (Interviews.DatumZeit >=convert(datetime,'2004-02-10 00:00:00',120)
AND Interviews.DatumZeit <=convert(datetime,'2004-05-16 23:59:59',120)
AND Interviews.User_Nr=Mitarbeiter.User_Nr
AND Interviews.Interview_Nr=Ergebnisse.Interview_Nr)

but now, i want to have variables for the Date string, so i tried following
syntax:

CREATE PROCEDURE dbo.IntDayView @.DayViewf DATETIME,@.DayViewl DATETIME AS
SELECT Mitarbeiter.UserName, Interviews.Interview_Nr, Ergebnisse.Thema_Nr,
Ergebnisse.Bewertung, Ergebnisse.Bemerkung
FROM Interviews, Mitarbeiter, Ergebnisse
WHERE (Interviews.DatumZeit >=@.DayViewf
AND Interviews.DatumZeit <=@.DayViewl
AND Interviews.User_Nr=Mitarbeiter.User_Nr
AND Interviews.Interview_Nr=Ergebnisse.Interview_Nr)
---
EXECUTE IntDayView
@.DayViewf=convert(datetime,'2004-02-10 00:00:00',120),
@.DayViewl=convert(datetime,'2004-05-16 23:59:59',120)

so i tried the convert in execute, and also in the Procedure, but both times an
error occures Error Nr. 156, or 8114,
so something with the convert is wrong but, also if i do it without convert it
does not work.

So how to select by time, if this doesn't work???
Any suggestions??

Please help me,
thanks

Jan SchmidtHi Jan,

I think that the first thing to point out is that SQL Server is
smarter than your average toaster, so it doesn't actually need you to
use the CONVERT function when specifying DATETIME values. As long as
you are using an accepted non-ambiguous format it should be fine. For
example, the following code works without error (also notice the use
of the BETWEEN operator, which you might find useful):

CREATE TABLE Test (my_id INT IDENTITY, my_date DATETIME NOT NULL)
GO

INSERT INTO Test VALUES ('2004-01-01')
INSERT INTO Test VALUES ('2003-01-01')
INSERT INTO Test VALUES ('2005-01-01')
INSERT INTO Test VALUES ('2004-06-01')
GO

CREATE PROCEDURE My_Proc
@.start_date DATETIME,
@.end_date DATETIME
AS
BEGIN
SELECT *
FROM Test
WHERE my_date BETWEEN @.start_date AND @.end_date
END
GO

EXEC My_Proc @.start_date = '2004-01-01', @.end_date = '2004-09-01'
GO

DROP PROCEDURE My_Proc
GO

DROP TABLE Test
GO

-Tom.

"Jan Schmidt" <histery@.gmx.net> wrote in message news:<2hh2keFcv2ooU1@.uni-berlin.de>...
> Hi NG,
> i've got a mystic problem i can't solve, perhaps one of you has a good idea...
> i will explain with some code, so fo better understanding my probroblem:
> i'll use asp, but for testing the syntax i use the MS Query Aanalyzer.
> following Stored Procedure is ok and does it's job fine with "EXECUTE
> IntDayView"
> CREATE PROCEDURE dbo.IntDayView AS
> SELECT Mitarbeiter.UserName, Interviews.Interview_Nr, Ergebnisse.Thema_Nr,
> Ergebnisse.Bewertung, Ergebnisse.Bemerkung
> FROM Interviews, Mitarbeiter, Ergebnisse
> WHERE (Interviews.DatumZeit >=convert(datetime,'2004-02-10 00:00:00',120)
> AND Interviews.DatumZeit <=convert(datetime,'2004-05-16 23:59:59',120)
> AND Interviews.User_Nr=Mitarbeiter.User_Nr
> AND Interviews.Interview_Nr=Ergebnisse.Interview_Nr)
> but now, i want to have variables for the Date string, so i tried following
> syntax:
> CREATE PROCEDURE dbo.IntDayView @.DayViewf DATETIME,@.DayViewl DATETIME AS
> SELECT Mitarbeiter.UserName, Interviews.Interview_Nr, Ergebnisse.Thema_Nr,
> Ergebnisse.Bewertung, Ergebnisse.Bemerkung
> FROM Interviews, Mitarbeiter, Ergebnisse
> WHERE (Interviews.DatumZeit >=@.DayViewf
> AND Interviews.DatumZeit <=@.DayViewl
> AND Interviews.User_Nr=Mitarbeiter.User_Nr
> AND Interviews.Interview_Nr=Ergebnisse.Interview_Nr)
> ---
> EXECUTE IntDayView
> @.DayViewf=convert(datetime,'2004-02-10 00:00:00',120),
> @.DayViewl=convert(datetime,'2004-05-16 23:59:59',120)
> so i tried the convert in execute, and also in the Procedure, but both times an
> error occures Error Nr. 156, or 8114,
> so something with the convert is wrong but, also if i do it without convert it
> does not work.
> So how to select by time, if this doesn't work???
> Any suggestions??
> Please help me,
> thanks
> Jan Schmidt|||Thomas R. Hummel (tom_hummel@.hotmail.com) writes:
> I think that the first thing to point out is that SQL Server is
> smarter than your average toaster, so it doesn't actually need you to
> use the CONVERT function when specifying DATETIME values. As long as
> you are using an accepted non-ambiguous format it should be fine. For
> example, the following code works without error (also notice the use
> of the BETWEEN operator, which you might find useful):
> CREATE TABLE Test (my_id INT IDENTITY, my_date DATETIME NOT NULL)
> GO
> INSERT INTO Test VALUES ('2004-01-01')

But that is not an unambiguous format. This format fails with SET
DATEFORMAT DMY or SET LANGUAGE GERMAN. There are two safe dateformats:

YYYYMMDD and YYYY-MM-DDTHH:MM:SS[.mmm] T in the latter formats stands
for itself.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||[posted and mailed, please reply in news]

Jan Schmidt (histery@.gmx.net) writes:
> EXECUTE IntDayView
> @.DayViewf=convert(datetime,'2004-02-10 00:00:00',120),
> @.DayViewl=convert(datetime,'2004-05-16 23:59:59',120)
> so i tried the convert in execute, and also in the Procedure, but both
> times an error occures Error Nr. 156, or 8114, so something with the
> convert is wrong but, also if i do it without convert it does not work.

You cannot pass expressions as parameters when you call stored procedure.
You need to do:

DECLARE @.d1 datetime,
@.d2 datetime
SELECT @.d1 = convert(datetime,'2004-02-10 00:00:00',120),
@.d2 = convert(datetime,'2004-02-10 23:59:59',120)
EXEC IntDayView @.d1, @.d2

Or:

EXEC IntDayView '2004-02-10T00:00:00', '2004-02-10T23:59:59'

I would suggest that you rewrite the procedure as:

WHERE (Interviews.DatumZeit >= @.DayViewf
AND Interviews.DatumZeit < @.DayViewl

Now you can say:

EXEC '20040210', '20040211'

Please see my reply to Thomas Hummel about date formats.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp