British Summer Time stopping SQL backup routine

Hi All
I have set up several DB Maintenance Plans in my SQL 2000 installation to
backup 3 sep DBs to a designated 'day' folder (ie 7 different folders to
cover Mon - Sun) on the same HD every day at 1am. This was working fine
everyday until the clocks moved forward by 1 hour to accommodate BST
(British Summer Time) and then the backups stopped backing up.
Now my PC Server being a good SBS 2000 installation auto-changed it's clock
to 1 hour forward, but the backups have just stopped working.
To correct this, I simply went into each DB Maintenance plan , opened up the
schedule, unticked a day, ticked it again and then saved it (ala what I call
a refresh) and everything started working again.
I know I could put these backups into 1 folder so I didn't have to update 21
or so individual plans, but I need to put it into these day folders to make
it easier for the day-to-day monitor-er of these backups.
I also know this problem only appears to happen twice a year (day when the
clocks go back and day when the clocks go forward), but because it is such
an unnecessary procedure, I just wanted to ask you if there was any fix for
it.
Many thanks.
Rgds
RobbieThere's not really a "fix", as there isn't really a problem, at least not
with SQL Server. 1:00 AM doesn't exist on the day you go to summer time, as
the clock moves from 00:59:59 GMT to 02:00:00 BST. On the other side, you
would have 1:00 AM twice when the clocks go back.
The best practice, if you want to avoid these little irritations, is not to
schedule anything within this period, and instead start your backup at 00:59
AM.
--
Jacco Schalkwijk
SQL Server MVP
"Astra" <info@.noemail.com> wrote in message
news:emGph0QNFHA.3000@.TK2MSFTNGP10.phx.gbl...
> Hi All
> I have set up several DB Maintenance Plans in my SQL 2000 installation to
> backup 3 sep DBs to a designated 'day' folder (ie 7 different folders to
> cover Mon - Sun) on the same HD every day at 1am. This was working fine
> everyday until the clocks moved forward by 1 hour to accommodate BST
> (British Summer Time) and then the backups stopped backing up.
> Now my PC Server being a good SBS 2000 installation auto-changed it's
> clock
> to 1 hour forward, but the backups have just stopped working.
> To correct this, I simply went into each DB Maintenance plan , opened up
> the
> schedule, unticked a day, ticked it again and then saved it (ala what I
> call
> a refresh) and everything started working again.
> I know I could put these backups into 1 folder so I didn't have to update
> 21
> or so individual plans, but I need to put it into these day folders to
> make
> it easier for the day-to-day monitor-er of these backups.
> I also know this problem only appears to happen twice a year (day when the
> clocks go back and day when the clocks go forward), but because it is such
> an unnecessary procedure, I just wanted to ask you if there was any fix
> for
> it.
> Many thanks.
> Rgds
> Robbie
>|||Dohhh!!!
Many thanks Jacco
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:ue9SDFRNFHA.3156@.TK2MSFTNGP15.phx.gbl...
There's not really a "fix", as there isn't really a problem, at least not
with SQL Server. 1:00 AM doesn't exist on the day you go to summer time, as
the clock moves from 00:59:59 GMT to 02:00:00 BST. On the other side, you
would have 1:00 AM twice when the clocks go back.
The best practice, if you want to avoid these little irritations, is not to
schedule anything within this period, and instead start your backup at 00:59
AM.
--
Jacco Schalkwijk
SQL Server MVP
"Astra" <info@.noemail.com> wrote in message
news:emGph0QNFHA.3000@.TK2MSFTNGP10.phx.gbl...
> Hi All
> I have set up several DB Maintenance Plans in my SQL 2000 installation to
> backup 3 sep DBs to a designated 'day' folder (ie 7 different folders to
> cover Mon - Sun) on the same HD every day at 1am. This was working fine
> everyday until the clocks moved forward by 1 hour to accommodate BST
> (British Summer Time) and then the backups stopped backing up.
> Now my PC Server being a good SBS 2000 installation auto-changed it's
> clock
> to 1 hour forward, but the backups have just stopped working.
> To correct this, I simply went into each DB Maintenance plan , opened up
> the
> schedule, unticked a day, ticked it again and then saved it (ala what I
> call
> a refresh) and everything started working again.
> I know I could put these backups into 1 folder so I didn't have to update
> 21
> or so individual plans, but I need to put it into these day folders to
> make
> it easier for the day-to-day monitor-er of these backups.
> I also know this problem only appears to happen twice a year (day when the
> clocks go back and day when the clocks go forward), but because it is such
> an unnecessary procedure, I just wanted to ask you if there was any fix
> for
> it.
> Many thanks.
> Rgds
> Robbie
>

British English Date Problem - ISO-dates not accepted (64-bit)

Problem exists on a 64-bit SQL Server (although the same result occurs on a
32-bit platform), where we have installed MS-SQL with the British English
locale.
We are experiencing problems when passing ISO format dates (yyyy-mm-dd)
within queries. UK date format (25/12/2004) are ok but as soon as an iso
date is used '2004-12-25' an error occurs.
<For example:>
Select * from tbltest where testdate > '2003-04-16 00:00:00'
<Returns error:>
Server: Msg 296, Level 16, State 3, Line 1
The conversion of char data type to smalldatetime data type resulted in an
out-of-range smalldatetime value.
There must be something that we are missing here, as changing the users
locale to the default 'English' (US English) corrects the problem.
Thanks in advance
IanHave a look at these:
http://www.karaszi.com/SQLServer/info_datetime.asp Guide to Datetimes
http://www.sqlservercentral.com/columnists/bsyverson/sqldatetime.asp
Datetimes
http://www.murach.com/books/sqls/article.htm Datetime Searching
I would use the format of 'yyyymmdd' instead.
--
Andrew J. Kelly SQL MVP
"Ian" <Ian@.discussions.microsoft.com> wrote in message
news:77EB65F8-40FE-4BE6-9058-425CFA1F36B8@.microsoft.com...
> Problem exists on a 64-bit SQL Server (although the same result occurs on
> a
> 32-bit platform), where we have installed MS-SQL with the British English
> locale.
> We are experiencing problems when passing ISO format dates (yyyy-mm-dd)
> within queries. UK date format (25/12/2004) are ok but as soon as an iso
> date is used '2004-12-25' an error occurs.
> <For example:>
> Select * from tbltest where testdate > '2003-04-16 00:00:00'
> <Returns error:>
> Server: Msg 296, Level 16, State 3, Line 1
> The conversion of char data type to smalldatetime data type resulted in an
> out-of-range smalldatetime value.
> There must be something that we are missing here, as changing the users
> locale to the default 'English' (US English) corrects the problem.
> Thanks in advance
> Ian|||thanks for the info...
Ian
"Andrew J. Kelly" wrote:
> Have a look at these:
>
> http://www.karaszi.com/SQLServer/info_datetime.asp Guide to Datetimes
> http://www.sqlservercentral.com/columnists/bsyverson/sqldatetime.asp
> Datetimes
> http://www.murach.com/books/sqls/article.htm Datetime Searching
>
> I would use the format of 'yyyymmdd' instead.
> --
> Andrew J. Kelly SQL MVP
>
> "Ian" <Ian@.discussions.microsoft.com> wrote in message
> news:77EB65F8-40FE-4BE6-9058-425CFA1F36B8@.microsoft.com...
> > Problem exists on a 64-bit SQL Server (although the same result occurs on
> > a
> > 32-bit platform), where we have installed MS-SQL with the British English
> > locale.
> >
> > We are experiencing problems when passing ISO format dates (yyyy-mm-dd)
> > within queries. UK date format (25/12/2004) are ok but as soon as an iso
> > date is used '2004-12-25' an error occurs.
> >
> > <For example:>
> >
> > Select * from tbltest where testdate > '2003-04-16 00:00:00'
> >
> > <Returns error:>
> >
> > Server: Msg 296, Level 16, State 3, Line 1
> > The conversion of char data type to smalldatetime data type resulted in an
> > out-of-range smalldatetime value.
> >
> > There must be something that we are missing here, as changing the users
> > locale to the default 'English' (US English) corrects the problem.
> >
> > Thanks in advance
> > Ian
>
>

British English Date Problem - ISO-dates not accepted (64-bit)

Problem exists on a 64-bit SQL Server (although the same result occurs on a
32-bit platform), where we have installed MS-SQL with the British English
locale.
We are experiencing problems when passing ISO format dates (yyyy-mm-dd)
within queries. UK date format (25/12/2004) are ok but as soon as an iso
date is used '2004-12-25' an error occurs.
<For example:>
Select * from tbltest where testdate > '2003-04-16 00:00:00'
<Returns error:>
Server: Msg 296, Level 16, State 3, Line 1
The conversion of char data type to smalldatetime data type resulted in an
out-of-range smalldatetime value.
There must be something that we are missing here, as changing the users
locale to the default 'English' (US English) corrects the problem.
Thanks in advance
Ian
Have a look at these:
http://www.karaszi.com/SQLServer/info_datetime.asp Guide to Datetimes
http://www.sqlservercentral.com/colu...qldatetime.asp
Datetimes
http://www.murach.com/books/sqls/article.htm Datetime Searching
I would use the format of 'yyyymmdd' instead.
Andrew J. Kelly SQL MVP
"Ian" <Ian@.discussions.microsoft.com> wrote in message
news:77EB65F8-40FE-4BE6-9058-425CFA1F36B8@.microsoft.com...
> Problem exists on a 64-bit SQL Server (although the same result occurs on
> a
> 32-bit platform), where we have installed MS-SQL with the British English
> locale.
> We are experiencing problems when passing ISO format dates (yyyy-mm-dd)
> within queries. UK date format (25/12/2004) are ok but as soon as an iso
> date is used '2004-12-25' an error occurs.
> <For example:>
> Select * from tbltest where testdate > '2003-04-16 00:00:00'
> <Returns error:>
> Server: Msg 296, Level 16, State 3, Line 1
> The conversion of char data type to smalldatetime data type resulted in an
> out-of-range smalldatetime value.
> There must be something that we are missing here, as changing the users
> locale to the default 'English' (US English) corrects the problem.
> Thanks in advance
> Ian
|||thanks for the info...
Ian
"Andrew J. Kelly" wrote:

> Have a look at these:
>
> http://www.karaszi.com/SQLServer/info_datetime.asp Guide to Datetimes
> http://www.sqlservercentral.com/colu...qldatetime.asp
> Datetimes
> http://www.murach.com/books/sqls/article.htm Datetime Searching
>
> I would use the format of 'yyyymmdd' instead.
> --
> Andrew J. Kelly SQL MVP
>
> "Ian" <Ian@.discussions.microsoft.com> wrote in message
> news:77EB65F8-40FE-4BE6-9058-425CFA1F36B8@.microsoft.com...
>
>

British date conversation

I am new to VB and have a problem with returning a recordset from a sql string.

I am using visual studio 2005 and SQL server 2005.

I am attempting to return a recordset with the from a table using two date parameters which are held in another table both tables are on the same server. Both tables store their date values in the British “dd/mm/yyy/ HH:MMS AMPM” format when I attempt to create a ADODB dataset using the following code errors occur. I have attempted various different approaches to resolve this but without success. Can you point me in the right direction?

Regards

Joe

@.@.@.@. CODE @.@.@.@.

‘Sub1

Dim Sdate as string, Edate as string

‘ Both parameters are passed to sub2 from sub1

rstmons.movefirst

Sdate = rstMon.Fields("DateAndTimeofFix").Value

rstmon.movelast

Edate = rstMon.Fields("DateAndTimeofFix").Value

‘Sub2

Dim myCONN As String, MonSql As String

myCONN = ("Driver={SQL Server};Provider=SQLOLEDB;Server=midlaptop2;Database=customerlink;Trusted_Connection=Yes;")

MonSql = "SELECT *" & _

" from tblVsol" & _

"WHERE DateAndTimeofFix Between CONVERT(DATETIME,'" & Sdate & _

"') AND CONVERT(DATETIME,'" & Edate & _

"') and vehicleunit = " & iveh & ";"

use convert with style #103

Select Convert(datetime,'31/1/2007 10:10:00', 103) --Correct

Select Convert(datetime,'31/1/2007 10:10:00') -- Error

MonSql = "SELECT *" & _
" from tblVsol" & _
"WHERE DateAndTimeofFix Between CONVERT(DATETIME,'" & Sdate & _
"',103) AND CONVERT(DATETIME,'" & Edate & _
"',103) and vehicleunit = " & iveh & ";"

|||

Hi

Thanks for your prompt reply.

Thats exactly what I needed.

Many Thanks

Joe

|||

Hi JoeBo,

> Both tables store their date values in the British “dd/mm/yyy/ HH:MMS AMPM” format

Unless you are using a character data type (char / nchar / varchar / nvarchar) to store that data, SQL Server does not store datetime data type using any format. The representation for datatime is a two four bytes, 4 bytes for the date and four bytes for the time. SQL Server interprets datetime constant strings based on the settings of SET LANGUAGE / SET DATEFORMAT, but id you want that SQL Server interpret them correctly no matter of those settings, then use styles 112 or 126 (See function CONVERT in BOL). Also, you should parameterize the "select" statement to re-use cached plans, instead concatenating the values and sending the final statement to SQL Server.

Convert Dynamic SQL to Use SQLCommand Parameters Automatically

http://www.knowdotnet.com/articles/dynamicsqlparameters.html

SqlCommand.Parameters Property

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx

Simple Parameterization

http://msdn2.microsoft.com/en-us/library/ms186219.aspx

The ultimate guide to the datetime datatypes

http://www.karaszi.com/SQLServer/info_datetime.asp

AMB

British date conversation

I am new to VB and have a problem with returning a recordset from a sql string.

I am using visual studio 2005 and SQL server 2005.

I am attempting to return a recordset with the from a table using two date parameters which are held in another table both tables are on the same server. Both tables store their date values in the British “dd/mm/yyy/ HH:MMS AMPM” format when I attempt to create a ADODB dataset using the following code errors occur. I have attempted various different approaches to resolve this but without success. Can you point me in the right direction?

Regards

Joe

@.@.@.@. CODE @.@.@.@.

‘Sub1

Dim Sdate as string, Edate as string

‘ Both parameters are passed to sub2 from sub1

rstmons.movefirst

Sdate = rstMon.Fields("DateAndTimeofFix").Value

rstmon.movelast

Edate = rstMon.Fields("DateAndTimeofFix").Value

‘Sub2

Dim myCONN As String, MonSql As String

myCONN = ("Driver={SQL Server};Provider=SQLOLEDB;Server=midlaptop2;Database=customerlink;Trusted_Connection=Yes;")

MonSql = "SELECT *" & _

" from tblVsol" & _

"WHERE DateAndTimeofFix Between CONVERT(DATETIME,'" & Sdate & _

"') AND CONVERT(DATETIME,'" & Edate & _

"') and vehicleunit = " & iveh & ";"

use convert with style #103

Select Convert(datetime,'31/1/2007 10:10:00', 103) --Correct

Select Convert(datetime,'31/1/2007 10:10:00') -- Error

MonSql = "SELECT *" & _
" from tblVsol" & _
"WHERE DateAndTimeofFix Between CONVERT(DATETIME,'" & Sdate & _
"',103) AND CONVERT(DATETIME,'" & Edate & _
"',103) and vehicleunit = " & iveh & ";"

|||

Hi

Thanks for your prompt reply.

Thats exactly what I needed.

Many Thanks

Joe

|||

Hi JoeBo,

> Both tables store their date values in the British “dd/mm/yyy/ HH:MMS AMPM” format

Unless you are using a character data type (char / nchar / varchar / nvarchar) to store that data, SQL Server does not store datetime data type using any format. The representation for datatime is a two four bytes, 4 bytes for the date and four bytes for the time. SQL Server interprets datetime constant strings based on the settings of SET LANGUAGE / SET DATEFORMAT, but id you want that SQL Server interpret them correctly no matter of those settings, then use styles 112 or 126 (See function CONVERT in BOL). Also, you should parameterize the "select" statement to re-use cached plans, instead concatenating the values and sending the final statement to SQL Server.

Convert Dynamic SQL to Use SQLCommand Parameters Automatically

http://www.knowdotnet.com/articles/dynamicsqlparameters.html

SqlCommand.Parameters Property

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx

Simple Parameterization

http://msdn2.microsoft.com/en-us/library/ms186219.aspx

The ultimate guide to the datetime datatypes

http://www.karaszi.com/SQLServer/info_datetime.asp

AMB

British date conversation

I am new to VB and have a problem with returning a recordset from a sql string.

I am using visual studio 2005 and SQL server 2005.

I am attempting to return a recordset with the from a table using two date parameters which are held in another table both tables are on the same server. Both tables store their date values in the British “dd/mm/yyy/ HH:MMS AMPM” format when I attempt to create a ADODB dataset using the following code errors occur. I have attempted various different approaches to resolve this but without success. Can you point me in the right direction?

Regards

Joe

@.@.@.@. CODE @.@.@.@.

‘Sub1

Dim Sdate as string, Edate as string

‘ Both parameters are passed to sub2 from sub1

rstmons.movefirst

Sdate = rstMon.Fields("DateAndTimeofFix").Value

rstmon.movelast

Edate = rstMon.Fields("DateAndTimeofFix").Value

‘Sub2

Dim myCONN As String, MonSql As String

myCONN = ("Driver={SQL Server};Provider=SQLOLEDB;Server=midlaptop2;Database=customerlink;Trusted_Connection=Yes;")

MonSql = "SELECT *" & _

" from tblVsol" & _

"WHERE DateAndTimeofFix Between CONVERT(DATETIME,'" & Sdate & _

"') AND CONVERT(DATETIME,'" & Edate & _

"') and vehicleunit = " & iveh & ";"

use convert with style #103

Select Convert(datetime,'31/1/2007 10:10:00', 103) --Correct

Select Convert(datetime,'31/1/2007 10:10:00') -- Error

MonSql = "SELECT *" & _
" from tblVsol" & _
"WHERE DateAndTimeofFix Between CONVERT(DATETIME,'" & Sdate & _
"',103) AND CONVERT(DATETIME,'" & Edate & _
"',103) and vehicleunit = " & iveh & ";"

|||

Hi

Thanks for your prompt reply.

Thats exactly what I needed.

Many Thanks

Joe

|||

Hi JoeBo,

> Both tables store their date values in the British “dd/mm/yyy/ HH:MMS AMPM” format

Unless you are using a character data type (char / nchar / varchar / nvarchar) to store that data, SQL Server does not store datetime data type using any format. The representation for datatime is a two four bytes, 4 bytes for the date and four bytes for the time. SQL Server interprets datetime constant strings based on the settings of SET LANGUAGE / SET DATEFORMAT, but id you want that SQL Server interpret them correctly no matter of those settings, then use styles 112 or 126 (See function CONVERT in BOL). Also, you should parameterize the "select" statement to re-use cached plans, instead concatenating the values and sending the final statement to SQL Server.

Convert Dynamic SQL to Use SQLCommand Parameters Automatically

http://www.knowdotnet.com/articles/dynamicsqlparameters.html

SqlCommand.Parameters Property

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx

Simple Parameterization

http://msdn2.microsoft.com/en-us/library/ms186219.aspx

The ultimate guide to the datetime datatypes

http://www.karaszi.com/SQLServer/info_datetime.asp

AMB

British Airways

WHERE IS EVERYBODY?Right Here.

Took some time solving the password prob though|||...Exactly 2 weeks before I take their flight, to the foggy land :p|||Hey Brett, where's your Margarita function? Need it for another post!|||http://weblogs.sqlteam.com/brettk/archive/2004/02/27/1002.aspx

I think you can find it here.|||Hey guys don't you ever take week end offs??:D|||Do you ? I am seeing you online on a saturday ;)|||Originally posted by Enigma
Do you ? I am seeing you online on a saturday ;)
just logged on from a cafe to check mails, could not control myself checking out if anyone's around :D|||This pathetic...and I'm in the damn office...|||Originally posted by rdjabarov
Hey Brett, where's your Margarita function? Need it for another post!

Did'ja get it?

What post?

Show us the link...|||All work on Saturdays ?

yurkkk