DB backup script

Hello, I inherited a SQL Server from my predecissor and came accross this backup script..Can anyone please help me in understanding what this means?
This script is currently backing up the 1.3GB database. and the current backup file size is 67GB. Maybe this script is adding up all the backs cummulatively everyday. I want it to make a fullbackup once a day and make incremental backups remaining 6 days. At the end of the week, I want to save the file somewhere and then start fresh with a new backup file. Is that possible? How do I achieve it. Any online sources to read some helpful tips? Appreciate your help...
-------

BACKUP DATABASE [STS_pthsps01_1] TO DISK = N'E:\PTHSPS01_BACKUP\SharepointDB' WITH NOINIT , NOUNLOAD , NAME = N'STS_pthsps01_1 backup', NOSKIP , STATS = 10, NOFORMAT DECLARE @.i INT
select @.i = position from msdb..backupset where database_name='STS_pthsps01_1'and type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name='STS_pthsps01_1')
RESTORE VERIFYONLY FROM DISK = N'E:\PTHSPS01_BACKUP\SharepointDB' WITH FILE = @.iYou are correct. The script is only appending backups. The NOINIT is what does that. If you want to overwrite the file change NOINIT to INIT.

As to changing it, you can set up 2 jobs under SQLAgent. The first runs weekly, and does the following:
1) copy the old backups to a new directory
2) Backs up the database (with init)

The second job would run 6 days a week and just do the differential backups probably to the same file, and let the weekly job pick up it's old file.

Play around with jobs in SQLAgent, and you should get it quickly enough. Also, look up BACKUP in BOL, so you can see all the bells and whistles there.|||You are correct. The script is only appending backups. The NOINIT is what does that. If you want to overwrite the file change NOINIT to INIT.

As to changing it, you can set up 2 jobs under SQLAgent. The first runs weekly, and does the following:
1) copy the old backups to a new directory
2) Backs up the database (with init)

The second job would run 6 days a week and just do the differential backups probably to the same file, and let the weekly job pick up it's old file.

Play around with jobs in SQLAgent, and you should get it quickly enough. Also, look up BACKUP in BOL, so you can see all the bells and whistles there.
Hi Thanks.
Thats a good suggestion. I will do that. Also, In the script that I copied F is for full. What needs to be changed to make full to differential.
I want to create 2 jobs.
1- Full backup every friday night with INIT
2- Diff backup the remaining days with NOINIT.
I will have a windows scheduler to copy this file every week to alternate location.|||Hello, I inherited a SQL Server from my predecissor and came accross this backup script..Can anyone please help me in understanding what this means?
This script is currently backing up the 1.3GB database. and the current backup file size is 67GB. Maybe this script is adding up all the backs cummulatively everyday. I want it to make a fullbackup once a day and make incremental backups remaining 6 days. At the end of the week, I want to save the file somewhere and then start fresh with a new backup file. Is that possible? How do I achieve it. Any online sources to read some helpful tips? Appreciate your help...
-------

BACKUP DATABASE [STS_pthsps01_1] TO DISK = N'E:\PTHSPS01_BACKUP\SharepointDB' WITH NOINIT , NOUNLOAD , NAME = N'STS_pthsps01_1 backup', NOSKIP , STATS = 10, NOFORMAT DECLARE @.i INT
select @.i = position from msdb..backupset where database_name='STS_pthsps01_1'and type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name='STS_pthsps01_1')
RESTORE VERIFYONLY FROM DISK = N'E:\PTHSPS01_BACKUP\SharepointDB' WITH FILE = @.i

The switch database_name='STS_pthsps01_1'and type!='F' means that it is fullbackup. How do I change it to be Differential? I tried creating a new job, but, its all T-SQL that I can do. I didnt see a GUI way.|||The restore command actually ends at "STATS = 10, NOFORMAT" After that, a query to determine the filenumber starts. The restore verifyonly checks the validity of the backup (kinda).
The types of backups are as follows:
D: Full
I: Differential (incremental)
L: Transaction log

Why the query is looking for type not equal to "F", I am not sure, since all of them are not equal to "F".|||Thanks Mcrowly for the clarification. I am clear now on the existing command. Now, Looking at the script, I am not seeing it to backup FULL. Can you please point me to where I can say backup D or backup I? I looked in BOL, and couldnt find any help from the T-SQL perspective there. Appreciate your help.|||A full backup is the default behaviour of the BACKUP DATABASE command. In order to get a differential backup, you have to supply the DIFFERENTIAL argument in the WITH list.|||Hi, Thanks for your reply.
I have modified the script to take differential as follows and it works fine.. Is this correct?
-------
BACKUP DATABASE [STS_pthsps01_1] TO DISK = N'E:\PTHSPS01_BACKUP\SharepointDB' WITH NOINIT , DIFFERENTIAL, NOUNLOAD , NAME = N'STS_pthsps01_1 backup', NOSKIP , STATS = 10, NOFORMAT DECLARE @.i INT
select @.i = position from msdb..backupset where database_name='STS_pthsps01_1'and type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name='STS_pthsps01_1')
RESTORE VERIFYONLY FROM DISK = N'E:\PTHSPS01_BACKUP\SharepointDB' WITH FILE = @.i
----------

Looks like this script backs up the diff between the full backup and the time diff is executed. The next diff backup will be again between full backup and 3rd day.
1) Full backup
2) Diff bwtween 1 and 2
3) Diff between 1 and 3
4) Diff between 1 and 4

How do we make the 3) to be 2 and 3 instead of 1 and 3?

Datetime conversion under diferent versions of SQL

Hello!
I'm using the same script to insert/update records on diferent versions of
SQL but i'm getting this error:
[Microsoft][ODBC SQL Server Driver][SQL Server]A conversão de um tipo de
dados char em um tipo de dados datetime resultou em um valor datetime fora
do intervalo.
(translation: error converting one string into datetime value out of range)
The SQL versions that I am probing is 8.00.194 (RTM) that is installed with
Microsoft SQL Personal Engine CD and ther other version is 8.00.2039 (SP4)
that i've downloaded and installed.
Can anywone help me?
Regards,
kTodosYou are probably passing dates in some regional format (e.g. dd/mm/yyyy) and
this is okay on one server (which may have British language settings) but
not on another (which may have US English language, or mdy dateformat). To
avoid these problems, always pass dates as 'YYYYMMDD'...
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"kTodos" <kanduru.x@.iol.pt> wrote in message
news:%23YZA%23Q6rHHA.2240@.TK2MSFTNGP03.phx.gbl...
> Hello!
> I'm using the same script to insert/update records on diferent versions of
> SQL but i'm getting this error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]A conversão de um tipo de
> dados char em um tipo de dados datetime resultou em um valor datetime fora
> do intervalo.
> (translation: error converting one string into datetime value out of
> range)
> The SQL versions that I am probing is 8.00.194 (RTM) that is installed
> with Microsoft SQL Personal Engine CD and ther other version is 8.00.2039
> (SP4) that i've downloaded and installed.
> Can anywone help me?
> Regards,
> kTodos
>|||... and for some extra reading: 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
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OtJcSa8rHHA.1200@.TK2MSFTNGP04.phx.gbl...
> You are probably passing dates in some regional format (e.g. dd/mm/yyyy) and this is okay on one
> server (which may have British language settings) but not on another (which may have US English
> language, or mdy dateformat). To avoid these problems, always pass dates as 'YYYYMMDD'...
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
> "kTodos" <kanduru.x@.iol.pt> wrote in message news:%23YZA%23Q6rHHA.2240@.TK2MSFTNGP03.phx.gbl...
>> Hello!
>> I'm using the same script to insert/update records on diferent versions of SQL but i'm getting
>> this error:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]A conversão de um tipo de dados char em um tipo de
>> dados datetime resultou em um valor datetime fora do intervalo.
>> (translation: error converting one string into datetime value out of range)
>> The SQL versions that I am probing is 8.00.194 (RTM) that is installed with Microsoft SQL
>> Personal Engine CD and ther other version is 8.00.2039 (SP4) that i've downloaded and installed.
>> Can anywone help me?
>> Regards,
>> kTodos
>

Datetime conversion under diferent versions of SQL

Hello!
I'm using the same script to insert/update records on diferent versions of
SQL but i'm getting this error:
[Microsoft][ODBC SQL Server Driver][SQL Server]A converso de um
tipo de
dados char em um tipo de dados datetime resultou em um valor datetime fora
do intervalo.
(translation: error converting one string into datetime value out of range)
The SQL versions that I am probing is 8.00.194 (RTM) that is installed with
Microsoft SQL Personal Engine CD and ther other version is 8.00.2039 (SP4)
that i've downloaded and installed.
Can anywone help me?
Regards,
kTodosYou are probably passing dates in some regional format (e.g. dd/mm/yyyy) and
this is okay on one server (which may have British language settings) but
not on another (which may have US English language, or mdy dateformat). To
avoid these problems, always pass dates as 'YYYYMMDD'...
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"kTodos" <kanduru.x@.iol.pt> wrote in message
news:%23YZA%23Q6rHHA.2240@.TK2MSFTNGP03.phx.gbl...
> Hello!
> I'm using the same script to insert/update records on diferent versions of
> SQL but i'm getting this error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]A converso de
um tipo de
> dados char em um tipo de dados datetime resultou em um valor datetime fora
> do intervalo.
> (translation: error converting one string into datetime value out of
> range)
> The SQL versions that I am probing is 8.00.194 (RTM) that is installed
> with Microsoft SQL Personal Engine CD and ther other version is 8.00.2039
> (SP4) that i've downloaded and installed.
> Can anywone help me?
> Regards,
> kTodos
>|||... and for some extra reading: http://www.karaszi.com/SQLServer/in...ime.as
p
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:OtJcSa8rHHA.1200@.TK2MSFTNGP04.phx.gbl...
> You are probably passing dates in some regional format (e.g. dd/mm/yyyy) a
nd this is okay on one
> server (which may have British language settings) but not on another (whic
h may have US English
> language, or mdy dateformat). To avoid these problems, always pass dates
as 'YYYYMMDD'...
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
> "kTodos" <kanduru.x@.iol.pt> wrote in message news:%23YZA%23Q6rHHA.2240@.TK2
MSFTNGP03.phx.gbl...
>

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 wrecking my head!

Anyone got a script to get the start-dates and end-dates for x number of wee
ks?
The tricky thing is that the script needs to account for ws where the
monday date is in say April and the Friday date is in May. In this case the
start-date is the monday but the end-date may be a wednesday or whenever the
last date of the month is. In other cases the start-date wont be the monday
date but could be the wednesday date. Does this make sense?
I need a result set like this...
Start-date End-date
2006-01-30 2006-01-31
2006-02-01 2006-02-03
2006-02-06 2006-02-10
2006-02-13 2006-02-17
2006-02-20 2006-02-24
2006-02-27 2006-02-28
2006-03-01 2006-03-03
2006-03-06 2006-03-10
2006-03-13 2006-03-17NH
Can you provide DDL+ sample data + expected result?
CREATE TABLE bbb
(
dt DATETIME,
...
....
)
"NH" <NH@.discussions.microsoft.com> wrote in message
news:43D6F63F-D1B3-454C-970E-ED83CDB4480A@.microsoft.com...
> Anyone got a script to get the start-dates and end-dates for x number of
> ws?
> The tricky thing is that the script needs to account for ws where the
> monday date is in say April and the Friday date is in May. In this case
> the
> start-date is the monday but the end-date may be a wednesday or whenever
> the
> last date of the month is. In other cases the start-date wont be the
> monday
> date but could be the wednesday date. Does this make sense?
> I need a result set like this...
> Start-date End-date
> 2006-01-30 2006-01-31
> 2006-02-01 2006-02-03
> 2006-02-06 2006-02-10
> 2006-02-13 2006-02-17
> 2006-02-20 2006-02-24
> 2006-02-27 2006-02-28
> 2006-03-01 2006-03-03
> 2006-03-06 2006-03-10
> 2006-03-13 2006-03-17|||What criterion/criteria determine whether your start-date is a Monday
or Wednesday?
If the end-date is a Wednesday, is the start-date then a Thursday?
Etc.
Andrew Watt [MVP]
On Mon, 24 Apr 2006 04:57:02 -0700, NH <NH@.discussions.microsoft.com>
wrote:

>Anyone got a script to get the start-dates and end-dates for x number of we
eks?
>The tricky thing is that the script needs to account for ws where the
>monday date is in say April and the Friday date is in May. In this case the
>start-date is the monday but the end-date may be a wednesday or whenever th
e
>last date of the month is. In other cases the start-date wont be the monday
>date but could be the wednesday date. Does this make sense?
>I need a result set like this...
>Start-date End-date
>2006-01-30 2006-01-31
>2006-02-01 2006-02-03
>2006-02-06 2006-02-10
>2006-02-13 2006-02-17
>2006-02-20 2006-02-24
>2006-02-27 2006-02-28
>2006-03-01 2006-03-03
>2006-03-06 2006-03-10
>2006-03-13 2006-03-17|||this is the table that the results will go into...
CREATE TABLE [SYSDBA].[CAP_CalendarWs] (
[key] [int] IDENTITY (1, 1) NOT NULL ,
[startdate] [datetime] NULL ,
[enddate] [datetime] NULL
) ON [PRIMARY]
I want to fill it with dates between 2006 and 2050.
Basically the start date will always be the monday, unless the any months
start date is some other day of the w. Same for end dates, they are
usually friday dates but a month end date could be a tuesday etc and this
needs to be recorded.
If you look at the sample data in my first post you should be able to see
the way it should work...? Does this make sense?
"Uri Dimant" wrote:

> NH
> Can you provide DDL+ sample data + expected result?
> CREATE TABLE bbb
> (
> dt DATETIME,
> ...
> .....
> )
>
>
> "NH" <NH@.discussions.microsoft.com> wrote in message
> news:43D6F63F-D1B3-454C-970E-ED83CDB4480A@.microsoft.com...
>
>|||try this and let me know if this was what you wanted.
select identity(int,0,1) as id into #temp from sysobjects
declare @.startdate datetime, @.enddate datetime
set @.startdate = '2006-01-30'
set @.enddate = '2006-03-17'
select dateadd(dd, a.id,@.startdate) as sow, dateadd(dd, b.id,@.startdate) as
eow from #temp a, #temp b
where
datediff(day,dateadd(dd, a.id,@.startdate),dateadd(dd, b.id,@.startdate))
between 1 and 5
and (
(datepart(dw, dateadd(dd, a.id,@.startdate)) = 2 and datepart(dw,
dateadd(dd, b.id,@.startdate)) = 6 )
or (datepart(dw, dateadd(dd, a.id,@.startdate)) = 2 and
datepart(dd,dateadd(dd, b.id,@.startdate)) =
datepart(dd, dateadd(dd,-1,cast( cast( year(dateadd(dd,
b.id,@.startdate))+ month(dateadd(dd, b.id,@.startdate))/12 as varchar) + '-'
+
cast(month(dateadd(dd, b.id,@.startdate))%12 + 1 as varchar) + '-01' as
datetime))))
or (datepart(dw, dateadd(dd, b.id,@.startdate)) = 6 and datepart(dd,
dateadd(dd, a.id,@.startdate)) = 1 ))
and datepart(mm, dateadd(dd, a.id,@.startdate)) = datepart(mm, dateadd(dd,
b.id,@.startdate))
and dateadd(dd, b.id,@.startdate) <= @.enddate and dateadd(dd,
a.id,@.startdate) <= @.enddate
drop table #temp|||A technical bug in my solution. date difference between 1 and 5 changed to 1
and 4.Use this.. updated.
select identity(int,0,1) as id into #temp from sysobjects
declare @.startdate datetime, @.enddate datetime
set @.startdate = '2006-01-01'
set @.enddate = '2050-01-01'
select dateadd(dd, a.id,@.startdate) as sow, dateadd(dd, b.id,@.startdate) as
eow from #temp a, #temp b
where
datediff(day,dateadd(dd, a.id,@.startdate),dateadd(dd, b.id,@.startdate))
between 0 and 4
and (
(datepart(dw, dateadd(dd, a.id,@.startdate)) = 2 and datepart(dw,
dateadd(dd, b.id,@.startdate)) = 6 )
or (datepart(dw, dateadd(dd, a.id,@.startdate)) = 2 and
datepart(dd,dateadd(dd, b.id,@.startdate)) =
datepart(dd, dateadd(dd,-1,cast( cast( year(dateadd(dd,
b.id,@.startdate))+ month(dateadd(dd, b.id,@.startdate))/12 as varchar) + '-'
+
cast(month(dateadd(dd, b.id,@.startdate))%12 + 1 as varchar) + '-01' as
datetime))))
or (datepart(dw, dateadd(dd, b.id,@.startdate)) = 6 and datepart(dd,
dateadd(dd, a.id,@.startdate)) = 1 ))
and datepart(mm, dateadd(dd, a.id,@.startdate)) = datepart(mm, dateadd(dd,
b.id,@.startdate))
and dateadd(dd, b.id,@.startdate) <= @.enddate and dateadd(dd,
a.id,@.startdate) <= @.enddate
order by dateadd(dd, a.id,@.startdate)
drop table #temp|||NH
Read please this article helps you to get an idea
http://www.aspfaq.com/show.asp?id=2519
"NH" <NH@.discussions.microsoft.com> wrote in message
news:0CAF9FBF-E162-48FF-9661-971551CE2D1A@.microsoft.com...
> this is the table that the results will go into...
> CREATE TABLE [SYSDBA].[CAP_CalendarWs] (
> [key] [int] IDENTITY (1, 1) NOT NULL ,
> [startdate] [datetime] NULL ,
> [enddate] [datetime] NULL
> ) ON [PRIMARY]
> I want to fill it with dates between 2006 and 2050.
> Basically the start date will always be the monday, unless the any months
> start date is some other day of the w. Same for end dates, they are
> usually friday dates but a month end date could be a tuesday etc and this
> needs to be recorded.
> If you look at the sample data in my first post you should be able to see
> the way it should work...? Does this make sense?
> "Uri Dimant" wrote:
>|||Hi all
Omnibuzz - there are some duplicates in that for me (e.g. 2006-04-03), but
I'm not sure what the problem is :(
Here's my stab... :)
--inputs
declare @.startdate datetime, @.enddate datetime
set @.startdate = '20060130'
set @.enddate = '20500101'
set datefirst 7
--calculation
declare @.NumberOfDays int
set @.NumberOfDays = datediff(d, @.startdate, @.enddate) + 1
set rowcount @.NumberOfDays
declare @.numbers table (i int identity(0,1), x bit)
insert into @.numbers select null from master.dbo.sysobjects a,
master.dbo.sysobjects b, master.dbo.sysobjects c
set rowcount 0
select d as StartDate,
case when datepart(day, d) = 1 --start of month
then dateadd(day, 6-datepart(dw, d), d)
when datepart(month, d) != datepart(month, d+4) --end of month
then dateadd(month, datediff(month, 0, d+4), 0)-1
else --normal w
d+4
end as EndDate
from
(select dateadd(dd, i, @.startdate) d from @.numbers) dates
where d = @.startdate --don't miss start date
or datepart(dw, d) = 2 --monday
or (datepart(day, d) = 1 and datepart(dw, d) between 3 and 6) --1st of
month and tue-fri
"Omnibuzz" wrote:

> A technical bug in my solution. date difference between 1 and 5 changed to
1
> and 4.Use this.. updated.
> select identity(int,0,1) as id into #temp from sysobjects
> declare @.startdate datetime, @.enddate datetime
> set @.startdate = '2006-01-01'
> set @.enddate = '2050-01-01'
> select dateadd(dd, a.id,@.startdate) as sow, dateadd(dd, b.id,@.startdate) a
s
> eow from #temp a, #temp b
> where
> datediff(day,dateadd(dd, a.id,@.startdate),dateadd(dd, b.id,@.startdate))
> between 0 and 4
> and (
> (datepart(dw, dateadd(dd, a.id,@.startdate)) = 2 and datepart(dw,
> dateadd(dd, b.id,@.startdate)) = 6 )
> or (datepart(dw, dateadd(dd, a.id,@.startdate)) = 2 and
> datepart(dd,dateadd(dd, b.id,@.startdate)) =
> datepart(dd, dateadd(dd,-1,cast( cast( year(dateadd(dd,
> b.id,@.startdate))+ month(dateadd(dd, b.id,@.startdate))/12 as varchar) + '-
' +
> cast(month(dateadd(dd, b.id,@.startdate))%12 + 1 as varchar) + '-01' as
> datetime))))
> or (datepart(dw, dateadd(dd, b.id,@.startdate)) = 6 and datepart(dd,
> dateadd(dd, a.id,@.startdate)) = 1 ))
> and datepart(mm, dateadd(dd, a.id,@.startdate)) = datepart(mm, dateadd(dd
,
> b.id,@.startdate))
> and dateadd(dd, b.id,@.startdate) <= @.enddate and dateadd(dd,
> a.id,@.startdate) <= @.enddate
> order by dateadd(dd, a.id,@.startdate)
> drop table #temp|||Hi Ryan,
Thanks for pointing it out, though I haven't checked it yet. I am back
home now. Will check it and post the update, if necessary, tomorrow.|||On Mon, 24 Apr 2006 04:57:02 -0700, NH wrote:

>Anyone got a script to get the start-dates and end-dates for x number of we
eks?
>The tricky thing is that the script needs to account for ws where the
>monday date is in say April and the Friday date is in May. In this case the
>start-date is the monday but the end-date may be a wednesday or whenever th
e
>last date of the month is. In other cases the start-date wont be the monday
>date but could be the wednesday date. Does this make sense?
(snip)
Hi NH,
Assuming that you already have a table of numbers in your database,
here's how you could fill your table quickly:
DECLARE @.StartOfPeriod smalldatetime
DECLARE @.EndOfPeriod smalldatetime
SET @.StartOfPeriod = '20060101'
SET @.EndOfPeriod = '20081231'
CREATE TABLE Ws
(StartDate smalldatetime NOT NULL PRIMARY KEY,
EndDate smalldatetime NOT NULL
)
-- Step 1: Generate full ws (mon-fri)
INSERT INTO Ws (StartDate, EndDate)
SELECT DATEADD(w, Number, '20050103'),
DATEADD(w, Number, '20050107')
FROM Numbers
WHERE DATEADD(w, Number, '20050103') >= @.StartOfPeriod
AND DATEADD(w, Number, '20050107') <= @.EndOfPeriod
-- Step 2a: For ws that span a month, add second partial w
INSERT INTO Ws (StartDate, EndDate)
SELECT DATEADD(day, 1 - DAY(EndDate), EndDate), EndDate
FROM Ws
WHERE MONTH(StartDate) <> MONTH(EndDate)
-- Step 2b: For ws that span a month, shorten first partial w
UPDATE Ws
SET EndDate = DATEADD(day, - DAY(EndDate), EndDate)
WHERE MONTH(StartDate) <> MONTH(EndDate)
SELECT * FROM Ws
go
Hugo Kornelis, SQL Server MVP

dates wrecking my head!

Anyone got a script to get the start-dates and end-dates for x number of weeks?
The tricky thing is that the script needs to account for weeks where the
monday date is in say April and the Friday date is in May. In this case the
start-date is the monday but the end-date may be a wednesday or whenever the
last date of the month is. In other cases the start-date wont be the monday
date but could be the wednesday date. Does this make sense?
I need a result set like this...
Start-date End-date
2006-01-30 2006-01-31
2006-02-01 2006-02-03
2006-02-06 2006-02-10
2006-02-13 2006-02-17
2006-02-20 2006-02-24
2006-02-27 2006-02-28
2006-03-01 2006-03-03
2006-03-06 2006-03-10
2006-03-13 2006-03-17Wrong forum, ignore this.
"NH" wrote:
> Anyone got a script to get the start-dates and end-dates for x number of weeks?
> The tricky thing is that the script needs to account for weeks where the
> monday date is in say April and the Friday date is in May. In this case the
> start-date is the monday but the end-date may be a wednesday or whenever the
> last date of the month is. In other cases the start-date wont be the monday
> date but could be the wednesday date. Does this make sense?
> I need a result set like this...
> Start-date End-date
> 2006-01-30 2006-01-31
> 2006-02-01 2006-02-03
> 2006-02-06 2006-02-10
> 2006-02-13 2006-02-17
> 2006-02-20 2006-02-24
> 2006-02-27 2006-02-28
> 2006-03-01 2006-03-03
> 2006-03-06 2006-03-10
> 2006-03-13 2006-03-17
>

Date/Time stamp

Hi All,

I have a script that adds the date/time stamp to a file in the following format:

200701120149PM.

here is the script:

set dttm=%~t1
for /F "tokens=1-6 delims=/: " %%i in ("%dttm%") do (

set date=%%k%%i%%j%%l%%m
)

I need to display the time as military. How can I do that?

Thanks.What is that?

Can't you use T-SQL?

What's the table definition (DDL) look like?|||This is a DOS command that displays the system date/time.
What table are you refering to?|||Well, since this is a MS SQL Server forum, you might want to ask a question about that, othwerwise, there might be another board that can help you out with DOS|||Definitely the wrong forum for this question. Nevertheless, I think the answer may depend on your regional settings; when I run it, the output is fine (ie, no AM/PM, just a military hour).

This site (http://www.robvanderwoude.com/index.html)has some good stuff on DOS scripts...

Regards,

hmscott