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
>>
>

DB Backup jobs

Hello,
Recently we had a hardware failure and I had to move SQLserver
databases to another hardware. Installed SQLServer 2000 and SP4 (OS Windows
Server 2003 R2 SE SP1). Restored all the databases. Restored msdb to get all
the Mainteance plans and jobs as well. None of the jobs run now. Even if I
kick them off, manually they sit at " Executing Job step1", for ever and it
never completes. I went ahead and deleted all the jobs, deleted all the
maintenance plans. Created a brand new maintenance plan. Still having the
same issue. Can somebody help me point to what could be causing this issue.
--
RKThat is not much to go on. What account is SQL Agent running under? Is
there anything in the error logs?
--
Andrew J. Kelly SQL MVP
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:ADF8D241-013E-425E-86C2-6B6D9E0F36BF@.microsoft.com...
> Hello,
> Recently we had a hardware failure and I had to move SQLserver
> databases to another hardware. Installed SQLServer 2000 and SP4 (OS
> Windows
> Server 2003 R2 SE SP1). Restored all the databases. Restored msdb to get
> all
> the Mainteance plans and jobs as well. None of the jobs run now. Even if I
> kick them off, manually they sit at " Executing Job step1", for ever and
> it
> never completes. I went ahead and deleted all the jobs, deleted all the
> maintenance plans. Created a brand new maintenance plan. Still having the
> same issue. Can somebody help me point to what could be causing this
> issue.
> --
> RK|||SQLAgent was running as Local System and I changed it to a user(local user)
who is part of the administrator group. So is MSSQLServer service.
When I login as that user (local user) I can create files and folders in the
backup directory.
I tried right clicking the database -> all Tasks -> bcakup : this works fine.
--
RK
"RK73" wrote:
> Hello,
> Recently we had a hardware failure and I had to move SQLserver
> databases to another hardware. Installed SQLServer 2000 and SP4 (OS Windows
> Server 2003 R2 SE SP1). Restored all the databases. Restored msdb to get all
> the Mainteance plans and jobs as well. None of the jobs run now. Even if I
> kick them off, manually they sit at " Executing Job step1", for ever and it
> never completes. I went ahead and deleted all the jobs, deleted all the
> maintenance plans. Created a brand new maintenance plan. Still having the
> same issue. Can somebody help me point to what could be causing this issue.
> --
> RK|||1) make a VERY simple sql agent job (like all it does is SELECT 1) and make
sure that simple thing works. If not, there could be a serious problem with
the install since you put the agent service account as local admin.
2) if your maintenance plans interact with ANY resource outside the server
sql is installed on, your service accounts need to be domain oriented and
have sufficient permissions to do the interaction.
3) do you get ANY log entries for the job starting/failing? add a few steps
in the job that somehow tell you that certain steps have started/finished to
see if you can isolate the problem.
--
TheSQLGuru
President
Indicium Resources, Inc.
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:5B0F58E6-138C-42B1-A987-E28EA2529F02@.microsoft.com...
> SQLAgent was running as Local System and I changed it to a user(local
> user)
> who is part of the administrator group. So is MSSQLServer service.
> When I login as that user (local user) I can create files and folders in
> the
> backup directory.
> I tried right clicking the database -> all Tasks -> bcakup : this works
> fine.
> --
> RK
>
> "RK73" wrote:
>> Hello,
>> Recently we had a hardware failure and I had to move SQLserver
>> databases to another hardware. Installed SQLServer 2000 and SP4 (OS
>> Windows
>> Server 2003 R2 SE SP1). Restored all the databases. Restored msdb to get
>> all
>> the Mainteance plans and jobs as well. None of the jobs run now. Even if
>> I
>> kick them off, manually they sit at " Executing Job step1", for ever and
>> it
>> never completes. I went ahead and deleted all the jobs, deleted all the
>> maintenance plans. Created a brand new maintenance plan. Still having the
>> same issue. Can somebody help me point to what could be causing this
>> issue.
>> --
>> RK

Daylight Savings Time error SQL SERVER 2000

Hi all.
I used tzedit to update the daylight savings time setting on my windows 2000
server.. Now, when I run any of my reports, I get this error:
"An internal error occurred on the report server. See the error log for more
details. (rsInternalError) Get Online Help
Specified argument was out of the range of valid values. Parameter name:
date "
Does anyone have or heard of a solution?Maybe there's a patch for sql 2000, i fixed my win 2003 server and didn't
encounter this issue with my SQL 2005.
"jereviscious" <here@.there.com> wrote in message
news:eEmN81vaHHA.4396@.TK2MSFTNGP06.phx.gbl...
> Hi all.
> I used tzedit to update the daylight savings time setting on my windows
> 2000 server.. Now, when I run any of my reports, I get this error:
> "An internal error occurred on the report server. See the error log for
> more details. (rsInternalError) Get Online Help
> Specified argument was out of the range of valid values. Parameter name:
> date "
> Does anyone have or heard of a solution?
>|||Since Windows 2000 server isn't officially supported any more, microsoft
didn't release a patch. They did, however, issue this knowledge base
article, which I followed..
http://support.microsoft.com/kb/914387
and everything else appears to be working fine. All of my servers are
reporting the correct time and date. However, SQL SERVER is giving me this
problem, and I have no idea how to go about fixing it...
"Julien Bonnier" <julien@.m0851.com> wrote in message
news:OM3DXKwaHHA.3584@.TK2MSFTNGP02.phx.gbl...
> Maybe there's a patch for sql 2000, i fixed my win 2003 server and didn't
> encounter this issue with my SQL 2005.
>
> "jereviscious" <here@.there.com> wrote in message
> news:eEmN81vaHHA.4396@.TK2MSFTNGP06.phx.gbl...
>> Hi all.
>> I used tzedit to update the daylight savings time setting on my windows
>> 2000 server.. Now, when I run any of my reports, I get this error:
>> "An internal error occurred on the report server. See the error log for
>> more details. (rsInternalError) Get Online Help
>> Specified argument was out of the range of valid values. Parameter name:
>> date "
>> Does anyone have or heard of a solution?
>

Daylight Savings Date Change

I have a Windows 2003 Standard server running MS SQL 2000 Server on it. I
read that if I have MS SQL Server Notification Services running on it I will
need to apply an update to it for the daylight savings time date change. Ho
w
do I tell if I have Notification Services running on this server. I don't
see it in Services. Is there anywhere else I need to look or is that it?
Thank you in advance.
--
Mike StevensYou would see it as a separately installed program in "Add/Remove Programs"
if you are on SQL Server
2000.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Stevens" <Stevens@.discussions.microsoft.com> wrote in message
news:D66848A1-E298-4A6E-AB6A-2085835741B0@.microsoft.com...
>I have a Windows 2003 Standard server running MS SQL 2000 Server on it. I
> read that if I have MS SQL Server Notification Services running on it I wi
ll
> need to apply an update to it for the daylight savings time date change.
How
> do I tell if I have Notification Services running on this server. I don't
> see it in Services. Is there anywhere else I need to look or is that it?
> Thank you in advance.
> --
> Mike Stevens|||In addition to the note from Tibor, you will have some databases like
NSMain* and NS*. The KB article that has the fix includes a section on
detecting if Notification Services is installed on SQL Server 2000:
http://support.microsoft.com/kb/931815
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||That answered my question. Thank you for your help.
--
Mike Stevens
"Tibor Karaszi" wrote:

> You would see it as a separately installed program in "Add/Remove Programs
" if you are on SQL Server
> 2000.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Stevens" <Stevens@.discussions.microsoft.com> wrote in message
> news:D66848A1-E298-4A6E-AB6A-2085835741B0@.microsoft.com...
>
>|||Thank you for your help. It doesn't look like I have it. One less thing to
do.
--
Mike Stevens
"Plamen Ratchev" wrote:

> In addition to the note from Tibor, you will have some databases like
> NSMain* and NS*. The KB article that has the fix includes a section on
> detecting if Notification Services is installed on SQL Server 2000:
> http://support.microsoft.com/kb/931815
> HTH,
> Plamen Ratchev
> http://www.SQLStudio.com
>
>|||Plamen,
Can you install SQL2005 Notification Services but not configure it? If
that's so then this is what we have done so we should not have to run the
script in the KB article.
Chris
"Plamen Ratchev" <Plamen@.SQLStudio.com> wrote in message
news:OBOgOXuSHHA.5016@.TK2MSFTNGP05.phx.gbl...
> In addition to the note from Tibor, you will have some databases like
> NSMain* and NS*. The KB article that has the fix includes a section on
> detecting if Notification Services is installed on SQL Server 2000:
> http://support.microsoft.com/kb/931815
> HTH,
> Plamen Ratchev
> http://www.SQLStudio.com
>
>|||You are correct Chris. When you install Notification Services only the
binary files are installed. You have to worry about applying the fix only
when you configure and deploy instances of Notification Services, which host
notification applications. Also, note in the KB that new instances created
after SP2 will not need to be fixed, as they will have the correct
information.
Regards,
Plamen Ratchev
http://www.SQLStudio.com|||Thanks Plamen.
That's what I had hoped for.
Chris
"Plamen Ratchev" <Plamen@.SQLStudio.com> wrote in message
news:%239ANydwSHHA.4260@.TK2MSFTNGP06.phx.gbl...
> You are correct Chris. When you install Notification Services only the
> binary files are installed. You have to worry about applying the fix only
> when you configure and deploy instances of Notification Services, which
> host notification applications. Also, note in the KB that new instances
> created after SP2 will not need to be fixed, as they will have the correct
> information.
> Regards,
> Plamen Ratchev
> http://www.SQLStudio.com
>

Daylight Savings Date Change

I have a Windows 2003 Standard server running MS SQL 2000 Server on it. I
read that if I have MS SQL Server Notification Services running on it I will
need to apply an update to it for the daylight savings time date change. How
do I tell if I have Notification Services running on this server. I don't
see it in Services. Is there anywhere else I need to look or is that it?
Thank you in advance.
Mike Stevens
In addition to the note from Tibor, you will have some databases like
NSMain* and NS*. The KB article that has the fix includes a section on
detecting if Notification Services is installed on SQL Server 2000:
http://support.microsoft.com/kb/931815
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||That answered my question. Thank you for your help.
Mike Stevens
"Tibor Karaszi" wrote:

> You would see it as a separately installed program in "Add/Remove Programs" if you are on SQL Server
> 2000.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Stevens" <Stevens@.discussions.microsoft.com> wrote in message
> news:D66848A1-E298-4A6E-AB6A-2085835741B0@.microsoft.com...
>
>
|||Thank you for your help. It doesn't look like I have it. One less thing to
do.
Mike Stevens
"Plamen Ratchev" wrote:

> In addition to the note from Tibor, you will have some databases like
> NSMain* and NS*. The KB article that has the fix includes a section on
> detecting if Notification Services is installed on SQL Server 2000:
> http://support.microsoft.com/kb/931815
> HTH,
> Plamen Ratchev
> http://www.SQLStudio.com
>
>
|||Plamen,
Can you install SQL2005 Notification Services but not configure it? If
that's so then this is what we have done so we should not have to run the
script in the KB article.
Chris
"Plamen Ratchev" <Plamen@.SQLStudio.com> wrote in message
news:OBOgOXuSHHA.5016@.TK2MSFTNGP05.phx.gbl...
> In addition to the note from Tibor, you will have some databases like
> NSMain* and NS*. The KB article that has the fix includes a section on
> detecting if Notification Services is installed on SQL Server 2000:
> http://support.microsoft.com/kb/931815
> HTH,
> Plamen Ratchev
> http://www.SQLStudio.com
>
>
|||You are correct Chris. When you install Notification Services only the
binary files are installed. You have to worry about applying the fix only
when you configure and deploy instances of Notification Services, which host
notification applications. Also, note in the KB that new instances created
after SP2 will not need to be fixed, as they will have the correct
information.
Regards,
Plamen Ratchev
http://www.SQLStudio.com
|||Thanks Plamen.
That's what I had hoped for.
Chris
"Plamen Ratchev" <Plamen@.SQLStudio.com> wrote in message
news:%239ANydwSHHA.4260@.TK2MSFTNGP06.phx.gbl...
> You are correct Chris. When you install Notification Services only the
> binary files are installed. You have to worry about applying the fix only
> when you configure and deploy instances of Notification Services, which
> host notification applications. Also, note in the KB that new instances
> created after SP2 will not need to be fixed, as they will have the correct
> information.
> Regards,
> Plamen Ratchev
> http://www.SQLStudio.com
>
sql

Daylight Savings Date Change

I have a Windows 2003 Standard server running MS SQL 2000 Server on it. I
read that if I have MS SQL Server Notification Services running on it I will
need to apply an update to it for the daylight savings time date change. How
do I tell if I have Notification Services running on this server. I don't
see it in Services. Is there anywhere else I need to look or is that it?
Thank you in advance.
--
Mike StevensYou would see it as a separately installed program in "Add/Remove Programs" if you are on SQL Server
2000.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Stevens" <Stevens@.discussions.microsoft.com> wrote in message
news:D66848A1-E298-4A6E-AB6A-2085835741B0@.microsoft.com...
>I have a Windows 2003 Standard server running MS SQL 2000 Server on it. I
> read that if I have MS SQL Server Notification Services running on it I will
> need to apply an update to it for the daylight savings time date change. How
> do I tell if I have Notification Services running on this server. I don't
> see it in Services. Is there anywhere else I need to look or is that it?
> Thank you in advance.
> --
> Mike Stevens|||In addition to the note from Tibor, you will have some databases like
NSMain* and NS*. The KB article that has the fix includes a section on
detecting if Notification Services is installed on SQL Server 2000:
http://support.microsoft.com/kb/931815
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||That answered my question. Thank you for your help.
--
Mike Stevens
"Tibor Karaszi" wrote:
> You would see it as a separately installed program in "Add/Remove Programs" if you are on SQL Server
> 2000.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Stevens" <Stevens@.discussions.microsoft.com> wrote in message
> news:D66848A1-E298-4A6E-AB6A-2085835741B0@.microsoft.com...
> >I have a Windows 2003 Standard server running MS SQL 2000 Server on it. I
> > read that if I have MS SQL Server Notification Services running on it I will
> > need to apply an update to it for the daylight savings time date change. How
> > do I tell if I have Notification Services running on this server. I don't
> > see it in Services. Is there anywhere else I need to look or is that it?
> >
> > Thank you in advance.
> > --
> > Mike Stevens
>
>|||Thank you for your help. It doesn't look like I have it. One less thing to
do.
--
Mike Stevens
"Plamen Ratchev" wrote:
> In addition to the note from Tibor, you will have some databases like
> NSMain* and NS*. The KB article that has the fix includes a section on
> detecting if Notification Services is installed on SQL Server 2000:
> http://support.microsoft.com/kb/931815
> HTH,
> Plamen Ratchev
> http://www.SQLStudio.com
>
>|||Plamen,
Can you install SQL2005 Notification Services but not configure it? If
that's so then this is what we have done so we should not have to run the
script in the KB article.
Chris
"Plamen Ratchev" <Plamen@.SQLStudio.com> wrote in message
news:OBOgOXuSHHA.5016@.TK2MSFTNGP05.phx.gbl...
> In addition to the note from Tibor, you will have some databases like
> NSMain* and NS*. The KB article that has the fix includes a section on
> detecting if Notification Services is installed on SQL Server 2000:
> http://support.microsoft.com/kb/931815
> HTH,
> Plamen Ratchev
> http://www.SQLStudio.com
>
>|||You are correct Chris. When you install Notification Services only the
binary files are installed. You have to worry about applying the fix only
when you configure and deploy instances of Notification Services, which host
notification applications. Also, note in the KB that new instances created
after SP2 will not need to be fixed, as they will have the correct
information.
Regards,
Plamen Ratchev
http://www.SQLStudio.com|||Thanks Plamen.
That's what I had hoped for.
Chris
"Plamen Ratchev" <Plamen@.SQLStudio.com> wrote in message
news:%239ANydwSHHA.4260@.TK2MSFTNGP06.phx.gbl...
> You are correct Chris. When you install Notification Services only the
> binary files are installed. You have to worry about applying the fix only
> when you configure and deploy instances of Notification Services, which
> host notification applications. Also, note in the KB that new instances
> created after SP2 will not need to be fixed, as they will have the correct
> information.
> Regards,
> Plamen Ratchev
> http://www.SQLStudio.com
>

Daylight saving time SQL

As you know the daylight saving time will change in 2007
http://www.microsoft.com/windows/timezone/dst2007.mspx
Microsoft realease patches for windows. But i cannot find any information
about if Microsoft SQL 2000 & 2005 are affected by this change.
Thank you
SQL Server gets its date and time from the operating system. Effectively,
it doesn't need to know anything about daylight savings time.
"Destin" <Destin@.discussions.microsoft.com> wrote in message
news:3739B03C-E71F-4CA6-8DAF-5189A22A7162@.microsoft.com...
> As you know the daylight saving time will change in 2007
> http://www.microsoft.com/windows/timezone/dst2007.mspx
> Microsoft realease patches for windows. But i cannot find any information
> about if Microsoft SQL 2000 & 2005 are affected by this change.
> Thank you
|||Ok, but i just want to be sure, because SharePoint is using the time of the
OS but SharePoint will need a patch (so maybe is the same thing for
SQL)(http://www.microsoft.com/windows/timezone/wss.mspx)
Do you know where i can find the official information from Microsoft.
Regards,
"Aaron Bertrand [SQL Server MVP]" wrote:

> SQL Server gets its date and time from the operating system. Effectively,
> it doesn't need to know anything about daylight savings time.
>
>
> "Destin" <Destin@.discussions.microsoft.com> wrote in message
> news:3739B03C-E71F-4CA6-8DAF-5189A22A7162@.microsoft.com...
>
>
|||> Ok, but i just want to be sure, because SharePoint is using the time of
> the
> OS but SharePoint will need a patch (so maybe is the same thing for
> SQL)(http://www.microsoft.com/windows/timezone/wss.mspx)
I believe there must be some data somewhere in sharepoint that needs to be
changed.
Anyway, the update is not released yet.
If there IS a forthcoming update to SQL Server (which I can't imagine) I
believe it will be released in much the same way, and you will hear about
it.

> Do you know where i can find the official information from Microsoft.
I don't think there will be any official information. I drive a Touareg and
I don't get notified when there is a recall on a Jetta. :-)
sql

Daylight saving time SQL

As you know the daylight saving time will change in 2007
http://www.microsoft.com/windows/timezone/dst2007.mspx
Microsoft realease patches for windows. But i cannot find any information
about if Microsoft SQL 2000 & 2005 are affected by this change.
Thank youSQL Server gets its date and time from the operating system. Effectively,
it doesn't need to know anything about daylight savings time.
"Destin" <Destin@.discussions.microsoft.com> wrote in message
news:3739B03C-E71F-4CA6-8DAF-5189A22A7162@.microsoft.com...
> As you know the daylight saving time will change in 2007
> http://www.microsoft.com/windows/timezone/dst2007.mspx
> Microsoft realease patches for windows. But i cannot find any information
> about if Microsoft SQL 2000 & 2005 are affected by this change.
> Thank you|||Ok, but i just want to be sure, because SharePoint is using the time of the
OS but SharePoint will need a patch (so maybe is the same thing for
SQL)(http://www.microsoft.com/windows/timezone/wss.mspx)
Do you know where i can find the official information from Microsoft.
Regards,
"Aaron Bertrand [SQL Server MVP]" wrote:

> SQL Server gets its date and time from the operating system. Effectively,
> it doesn't need to know anything about daylight savings time.
>
>
> "Destin" <Destin@.discussions.microsoft.com> wrote in message
> news:3739B03C-E71F-4CA6-8DAF-5189A22A7162@.microsoft.com...
>
>|||> Ok, but i just want to be sure, because SharePoint is using the time of
> the
> OS but SharePoint will need a patch (so maybe is the same thing for
> SQL)(http://www.microsoft.com/windows/timezone/wss.mspx)
I believe there must be some data somewhere in sharepoint that needs to be
changed.
Anyway, the update is not released yet.
If there IS a forthcoming update to SQL Server (which I can't imagine) I
believe it will be released in much the same way, and you will hear about
it.

> Do you know where i can find the official information from Microsoft.
I don't think there will be any official information. I drive a Touareg and
I don't get notified when there is a recall on a Jetta. :-)

Daylight Saving Time DST 2007 Problems

I believe my machine is fully patched. The SQL Server believes that it is not yet DST, whereas the windows interface has switched. So what should I do here? (I'm in EST/EDT time zone -- New York City -- commands were run at 1:13 am.)

select current_timestamp,getutcdate()-getdate()

-- --

2007-03-13 00:13:24.843 1900-01-01 05:00:00.000

(1 row(s) affected)

SQL Server gets tim (using all three of these functions) from the Windows OS.

This is a quote from Books Online for getutcdate()-

Returns the datetime value that represents the current UTC time (Coordinated Universal Time or Greenwich Mean Time). The current UTC time is derived from the current local time and the time zone setting in the operating system of the computer on which the instance of Microsoft SQL Server is running.

|||The windows gui gives me the right time. A cmd shell "time/t" command gives me the right time, but the T-SQL I quoted gets the wrong time and all my agent jobs are running an hour late, so something is up here...|||

so, instead of a 5 hour difference between the two you should see a 4 hour difference?

|||Yes. That's right.|||If you go to a cmd prompt and type "time" and open a query window and run "SELECT GETDATE()" Do you get the same hour?

When was the last time you rebooted the server?

It use to be, I don't know if it still works this way, Windows would fudge the Windows clock after DST and would reset the RTC clock when the computer rebooted.

So your RTC could say 1:13pm and Windows would report 2:13pm, until you rebooted.

|||We noticed that about 50% of the time the DLS Patch that we applied to Windows 2003 did not update the registry values for daylight and daylight end. We found that the time would update and display correctly on the OS, but anything that capture time via some time/date.dll would be an hour off.

Daylight Saving Time DST 2007 Problems

I believe my machine is fully patched. The SQL Server believes that it is not yet DST, whereas the windows interface has switched. So what should I do here? (I'm in EST/EDT time zone -- New York City -- commands were run at 1:13 am.)

select current_timestamp,getutcdate()-getdate()

-- --

2007-03-13 00:13:24.843 1900-01-01 05:00:00.000

(1 row(s) affected)

SQL Server gets tim (using all three of these functions) from the Windows OS.

This is a quote from Books Online for getutcdate()-

Returns the datetime value that represents the current UTC time (Coordinated Universal Time or Greenwich Mean Time). The current UTC time is derived from the current local time and the time zone setting in the operating system of the computer on which the instance of Microsoft SQL Server is running.

|||The windows gui gives me the right time. A cmd shell "time/t" command gives me the right time, but the T-SQL I quoted gets the wrong time and all my agent jobs are running an hour late, so something is up here...|||

so, instead of a 5 hour difference between the two you should see a 4 hour difference?

|||Yes. That's right.|||If you go to a cmd prompt and type "time" and open a query window and run "SELECT GETDATE()" Do you get the same hour?

When was the last time you rebooted the server?

It use to be, I don't know if it still works this way, Windows would fudge the Windows clock after DST and would reset the RTC clock when the computer rebooted.

So your RTC could say 1:13pm and Windows would report 2:13pm, until you rebooted.|||We noticed that about 50% of the time the DLS Patch that we applied to Windows 2003 did not update the registry values for daylight and daylight end. We found that the time would update and display correctly on the OS, but anything that capture time via some time/date.dll would be an hour off.

DAVID/ASPNET Sql connection problem!

I am having a problem with .net ms sql connections...

My situation: I log onto my computer (Windows XP Pro) using the administrator account.
I have MSDE installed, with NT authorization.
I created a quick connection in ASP.net Web Matrix, and ran the code using the ASP.NET web matrix server - It ran perfectly, so i know the connection string is good, and so is all the other code i used for the connection... I know that it used the administrator account to access the sql server.

Then i copied and pasted the exact working code i had in web matrix to Visual Studio.net, and tried to run it using IIS, but it gave the following error:

Server Error in '/' Application.

Login failed for user 'DAVID\ASPNET'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'DAVID\ASPNET'.

Source Error:

Line 11: Dim myCommand As New SqlCommand(CommandText, myConnection)
Line 12:
Line 13: myConnection.Open()
Line 14:
Line 15: ' Create Instance of Connection and Command Object

I only have MSDE installed on my PC, so i ran the following in DOS:

cd "c:\Program Files\Microsoft SQL Server\80\Tools\Binn"
osql -E -S DAVID -Q "sp_grantlogin 'DAVID\ASPNET'"
osql -E -S DAVID -d myDB -Q "sp_grantdbaccess 'DAVID\ASPNET'"
osql -E -S DAVID -d myDB -Q "sp_addrolemember 'db_owner', 'DAVID\ASPNET'"
pause

So now ASPNET should have full access to the sql server. (Stored Procedures ran fine, and the roles and accounts were created successfully.)

So i ran the ASP.net code in IIS again, but still the same error!

I have even tried re-installing asp.net!
I havent tried re-installing MSDE because i dont want to loose my data, but if i have to, then i guess i have to... :(

What do i do??I'm sure you understood, that your SQL Server did not accept DAVID\ASPNET as a login name?! Either there is indeed not such a user, or maybe you took the back-slash instead the slash. Could you post your connection string?|||I will get the connection string to you asap - its at home, not at work, so only tomorrow...

When installing the .NET framework, the user ASPNET is automatically added. ASP.NET uses this user for all the interaction it makes with the server (in most cases). So anyway - the ASPNET user is there, and my computer name is DAVID, and so is my SQL server instance called DAVID.

Ill try with the forward slash instead of using a back slash, but im pretty sure that i did it the right way... I copied it exactly as the asp error page had it...

Dattime format problems when retrieved on FreeBSD server

I'm using MSSQL with PHP and this works fine on a Windows server.

When i move to a FreeBSD server, the date formatting is not working.

FreeBSD retrieves the date as: mon dd yyyy hh:mi:ss:mmmAM - and php's functions for formatting date fails.

I've tried using: Convert(varchar(10), Date, 103) AS Date, and the date is formatted fine - BUT sorting on date does NOT work.

Are there any way i can do changes to datetime behaviour on server side? I NEVER wants the date in mon dd yyyy hh:mi:ss:mmmAM. I don't need milliseconds, and i want 24h format - not AM/PM. Are there any settings on the SQL server for this?

Where are you doing your sorting? With T-SQL CONVERT-style affects sort:

ORDERBYConvert(nvarchar(30), OrderDate, 109)

|||

I'm not sure what you mean. I SELECT Convert(varchar(10), Date, 103) AS Date, and last sentence is ORDER BY Date DESC.

Anyway - i would prefer to fix this server-side if possible, so that i don't need to rewrite all queries adding convert functions etc...

|||If you want to ORDER BY the output of Convert(varchar(10), Date, 103) then you have to put that expression in the ODER BY clause not simply the column named Date.|||

I'll try that, but i don't think it will work, because it returns the date as a string, and thus sorting the strings from highest numbers, to lower, which does not not necessarily need to be correct(?)

Is this really the only reason to make it work?

Dattime format problems when retrieved on FreeBSD server

I'm using MSSQL with PHP and this works fine on a Windows server.

When i move to a FreeBSD server, the date formatting is not working.

FreeBSD retrieves the date as: mon dd yyyy hh:mi:ss:mmmAM - and php's functions for formatting date fails.

I've tried using: Convert(varchar(10), Date, 103) AS Date, and the date is formatted fine - BUT sorting on date does NOT work.

Are there any way i can do changes to datetime behaviour on server side? I NEVER wants the date in mon dd yyyy hh:mi:ss:mmmAM. I don't need milliseconds, and i want 24h format - not AM/PM. Are there any settings on the SQL server for this?

Where are you doing your sorting? With T-SQL CONVERT-style affects sort:

ORDERBYConvert(nvarchar(30), OrderDate, 109)

|||

I'm not sure what you mean. I SELECT Convert(varchar(10), Date, 103) AS Date, and last sentence is ORDER BY Date DESC.

Anyway - i would prefer to fix this server-side if possible, so that i don't need to rewrite all queries adding convert functions etc...

|||If you want to ORDER BY the output of Convert(varchar(10), Date, 103) then you have to put that expression in the ODER BY clause not simply the column named Date.|||

I'll try that, but i don't think it will work, because it returns the date as a string, and thus sorting the strings from highest numbers, to lower, which does not not necessarily need to be correct(?)

Is this really the only reason to make it work?

datetime question

Hi,
I am using SQL Server 2000 SP3 on Windows 2000 Server. I have tried the
following
declare @.d1 as datetime, @.d2 as datetime
--Query Set#1
set @.d1='03/26/1995 01:00:00.000'
set @.d2='03/26/1995 01:00:00.001'
if @.d1 = @.d2 print 'yes' else print 'no'
if @.d1 like @.d2 print 'yes' else print 'no'
--Query Set#1 Results
yes
yes
--Query Set#2
set @.d1='03/26/1995 01:00:00.000'
set @.d2='03/26/1995 01:00:00.002'
if @.d1 = @.d2 print 'yes' else print 'no'
if @.d1 like @.d2 print 'yes' else print 'no'
--Query Set#2 Results
no
yes
--Query Set#3
set @.d1='03/26/1995 01:00:00.000'
set @.d2='03/26/1995 01:01:00.000'
if @.d1 = @.d2 print 'yes' else print 'no'
if @.d1 like @.d2 print 'yes' else print 'no'
--Query Set#3 Results
no
no
I am positive that these are not anomalies. So, if someone could explain
these results, it would be great.Hi
LIKE from BOL:
"If any of the arguments are not of character string data type, Microsoft
SQL ServerT converts them to character string data type, if possible."
A LIKE does string comparisons. It uses the default CONVERT to String data
type so:
declare @.d1 as datetime, @.d2 as datetime
set @.d1='03/26/1995 01:00:00.000'
set @.d2='03/26/1995 01:00:00.002'
select convert(char(30), @.d1)
select convert(char(30), @.d2)
Results in:
Mar 26 1995 1:00AM
Mar 26 1995 1:00AM
If you need to do like on non-string datatypes, tell SQL exactly how to
convert the data.
select convert(char(30), @.d1, 113)
select convert(char(30), @.d2, 113)
Results in:
26 Mar 1995 01:00:00:000
26 Mar 1995 01:00:00:003
That will give correct results.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"payyans" <payyans@.discussions.microsoft.com> wrote in message
news:54E90AE2-D1FD-42A1-A4AE-D19F9A0F3A93@.microsoft.com...
> Hi,
> I am using SQL Server 2000 SP3 on Windows 2000 Server. I have tried the
> following
> declare @.d1 as datetime, @.d2 as datetime
> --Query Set#1
> set @.d1='03/26/1995 01:00:00.000'
> set @.d2='03/26/1995 01:00:00.001'
> if @.d1 = @.d2 print 'yes' else print 'no'
> if @.d1 like @.d2 print 'yes' else print 'no'
> --Query Set#1 Results
> yes
> yes
> --Query Set#2
> set @.d1='03/26/1995 01:00:00.000'
> set @.d2='03/26/1995 01:00:00.002'
> if @.d1 = @.d2 print 'yes' else print 'no'
> if @.d1 like @.d2 print 'yes' else print 'no'
> --Query Set#2 Results
> no
> yes
> --Query Set#3
> set @.d1='03/26/1995 01:00:00.000'
> set @.d2='03/26/1995 01:01:00.000'
> if @.d1 = @.d2 print 'yes' else print 'no'
> if @.d1 like @.d2 print 'yes' else print 'no'
> --Query Set#3 Results
> no
> no
> I am positive that these are not anomalies. So, if someone could explain
> these results, it would be great.|||Thanks Mike.
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> LIKE from BOL:
> "If any of the arguments are not of character string data type, Microsoft
> SQL ServerT converts them to character string data type, if possible."
> A LIKE does string comparisons. It uses the default CONVERT to String data
> type so:
> declare @.d1 as datetime, @.d2 as datetime
> set @.d1='03/26/1995 01:00:00.000'
> set @.d2='03/26/1995 01:00:00.002'
> select convert(char(30), @.d1)
> select convert(char(30), @.d2)
> Results in:
> Mar 26 1995 1:00AM
> Mar 26 1995 1:00AM
> If you need to do like on non-string datatypes, tell SQL exactly how to
> convert the data.
> select convert(char(30), @.d1, 113)
> select convert(char(30), @.d2, 113)
> Results in:
> 26 Mar 1995 01:00:00:000
> 26 Mar 1995 01:00:00:003
> That will give correct results.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "payyans" <payyans@.discussions.microsoft.com> wrote in message
> news:54E90AE2-D1FD-42A1-A4AE-D19F9A0F3A93@.microsoft.com...
>
>

DateTime Format -

I have installed the trial version of windows server 2003 on the second hard drive on my computer. I set up IIS and ran my website on it but the problem is when I do something on the site, which has a sqlinsert statement regarding datetime.now it says, "conversion failed when converting datetime from character string"

I think it's to do with the clock on server 2003, the format is like: 11/07/2007 2:39:59 a.m.

I think it should be in formatAM and not a.m.

Any ideas on how to change the time format on a computer?

Or should I just change the Columns in my table to a Nvarcher value or something?

thanks

how is the value coming through? from your application? via now() ?

|||

Hi,

Thanks for your reply

What do you mean via now()?

I'm using VB and if I use something like. sqldatasource1.insertparameters.add("enddate", datetime.now()) it will give the format: 11/07/2007 2:39:59a.m.(which gives the incorrect string error.) when it should be 11/07/2007 2:39:59AM,

It must be to do with the computer clocks date time format, on server 2003 ?

Any ideas?

|||

Hi,

Please run the "Regional and Language Options" in your Control Panel. Click on "Customize", and switch to the Time tab, just to modify the "AM symbol" and "PM symbol" and hava a try.

Good Luck.

|||

store the datetime column in international format or use now.tostring("format eg. MM/dd/yyyy hh:mm:ss etc ")

|||

If the SqlDbType = DateTime then format should not come into it as the output string display is just a human readable format for display use that is not used by SQL when feeding DateTime values into it.

Do you have a snippet of the code? Something like this is what I would expect for a successful date insertion: (example routine)

public static bool InsertDateIntoRandomTable() {bool blSuccess =false;string strComm ="INSERT INTO [RandomTable] " +"(One_Date) VALUES (@.One_Date)"; SqlConnection sqlConn =new SqlConnection(strGlobalSQLConnection); SqlCommand sqlComm =new SqlCommand(strComm, sqlConn); sqlComm.Parameters.Add("@.One_Date", SqlDbType.DateTime).Value = DateTime.Now; sqlConn.Open();if (sqlComm.ExecuteNonQuery() > 0) blSuccess =true; sqlConn.Close();return blSuccess; }

Hope this helps

Mark

|||

Hi,

Thanks for the help guys

I tried what you said and it changed the clock on the computer OK. But strangley, on the website; it is still doing the format 11/11/2006 12:07a.m. instead of 11/11/2006 12:07AM

Is it something to do with IIS settings?

Thanks

|||

Hi,

After you change the time format in Regional and Language Options, you shouldrestartthe Visual Studio and open your application project, build and run the application again. Then check it and explorer the page in your IIS.

Thanks.

|||

Thanks a lot for your help. I tried restarting my computer etc. but, no luck...

DATEDIFF question

Uses: SQL Server 2000 Personal Edition with SP3, Windows XP Pro;

Hi,

Is there any possible way that I can differentiate 2 different dates and get in a format inclusive of hh, mm, ss part altogether, rather than only getting one part?

Like this:

if

StartDateTime = 10:15:01 AM and

LastDateTime = 11:20:01 AM

then the difference of LastDateTime and StartDateTime will be = 1hour and 05 Minutes and 00 seconds (I need like this). So using DATEDIFF function I will be only getting one part either hour, Minute and Second. I would like to know is there anyway either by using DATEDIFF function or other to get in the format inclusive of hours, Minutes and Seconds.

Regards,

Hifni

You can do something like below. Note that the smalldatetime or datetime data types all contain a date value. So you just need to use same date value.

declare @.newstrt datetime, @.newend datetime

-- the expression below strips date part out and sets it to 1900/01/01

set @.newstart = cast('1900-01-01' + right(convert(varchar, @.start, 126), 13) as datetime)

set @.newend = cast('1900-01-01' + right(convert(varchar, @.end, 126), 13) as datetime)

-- now you can just use - operator like:

select convert(varchar, @.newend - @.newstart, 114)

Note that this does assume that the time interval is within a day.

|||

Hi,

Thanks for the reply, but that is not what I'm looking for. Actually my emphasis is on the time rather than the date. Actually it's like this:

Assume that there is a person, who called at Time X of a day and he spoked until time'Y'. So the duration of the call he took would be = Y - X. I want the result to be how many hours, minutes and seconds he took to the call. So the duration should illustrated as = hh: mm: ss. In DATEDIFF, you only get either hours part, minutes part or the seconds parts, however I want it in all inclusive. Is there a way to get it?

Regards,

|||

declare @.date1 datetime
declare @.date2 datetime
select @.date1 = '2002-03-07 04:15:03.000'
select @.date2= '2002-03-07 13:21:04.000'
declare @.hour int
declare @.min int
declare @.s int

select @.hour=datediff(hour,@.date1,@.date2)
select @.min=datediff(mi,@.date1,@.date2)-@.hour*60
select @.s=datediff(s,@.date1,@.date2)-@.hour*60*60-@.min*60

select @.hour ,@.min,@.s

|||The last line in the script I posted will give you exactly the output you are looking for i.e, only time formatted as hh:mm:ss. As long as you have the date part same between the values you can simply store it in smalldatetime and datetime. I am not using DATEDIFF instead I am using the - operator which produces different results. Run the script with some input and see.|||

Hi

There is no built in method in SQL Server which can produce the result u need.

u can do it using T-SQL by programmatically.

just create your own function in SQL Server and pass two dates to that function

just like this

Create function dbo.fun_TimeDiff(@.StDate datetime, @.EnDate datetime)
Returns Varchar(10) AS
Begin
declare @.hor int
declare @.min int
declare @.sec int

Set @.hor=datediff(hour,@.StDate,@.EnDate)
Set @.min=datediff(mi,@.StDate,@.EnDate)-@.hor*60
Set @.sec=datediff(s,@.StDate,@.EnDate)-@.hor*60*60-@.min*60

Return convert(char(2),@.hor) +':'+ convert(char(2),@.min) + ':'+ convert(char(2),@.sec)
End

select dbo.Fun_TimeDiff('2006/01/03 07:30:00.000','2006/01/03 17:41:10.000')

it will give u the result

10:11:10

DATEDIFF question

Uses: SQL Server 2000 Personal Edition with SP3, Windows XP Pro;

Hi,

Is there any possible way that I can differentiate 2 different dates and get in a format inclusive of hh, mm, ss part altogether, rather than only getting one part?

Like this:

if

StartDateTime = 10:15:01 AM and

LastDateTime = 11:20:01 AM

then the difference of LastDateTime and StartDateTime will be = 1hour and 05 Minutes and 00 seconds (I need like this). So using DATEDIFF function I will be only getting one part either hour, Minute and Second. I would like to know is there anyway either by using DATEDIFF function or other to get in the format inclusive of hours, Minutes and Seconds.

Regards,

Hifni

You can do something like below. Note that the smalldatetime or datetime data types all contain a date value. So you just need to use same date value.

declare @.newstrt datetime, @.newend datetime

-- the expression below strips date part out and sets it to 1900/01/01

set @.newstart = cast('1900-01-01' + right(convert(varchar, @.start, 126), 13) as datetime)

set @.newend = cast('1900-01-01' + right(convert(varchar, @.end, 126), 13) as datetime)

-- now you can just use - operator like:

select convert(varchar, @.newend - @.newstart, 114)

Note that this does assume that the time interval is within a day.

|||

Hi,

Thanks for the reply, but that is not what I'm looking for. Actually my emphasis is on the time rather than the date. Actually it's like this:

Assume that there is a person, who called at Time X of a day and he spoked until time'Y'. So the duration of the call he took would be = Y - X. I want the result to be how many hours, minutes and seconds he took to the call. So the duration should illustrated as = hh: mm: ss. In DATEDIFF, you only get either hours part, minutes part or the seconds parts, however I want it in all inclusive. Is there a way to get it?

Regards,

|||

declare @.date1 datetime
declare @.date2 datetime
select @.date1 = '2002-03-07 04:15:03.000'
select @.date2= '2002-03-07 13:21:04.000'
declare @.hour int
declare @.min int
declare @.s int

select @.hour=datediff(hour,@.date1,@.date2)
select @.min=datediff(mi,@.date1,@.date2)-@.hour*60
select @.s=datediff(s,@.date1,@.date2)-@.hour*60*60-@.min*60

select @.hour ,@.min,@.s

|||The last line in the script I posted will give you exactly the output you are looking for i.e, only time formatted as hh:mm:ss. As long as you have the date part same between the values you can simply store it in smalldatetime and datetime. I am not using DATEDIFF instead I am using the - operator which produces different results. Run the script with some input and see.|||

Hi

There is no built in method in SQL Server which can produce the result u need.

u can do it using T-SQL by programmatically.

just create your own function in SQL Server and pass two dates to that function

just like this

Create function dbo.fun_TimeDiff(@.StDate datetime, @.EnDate datetime)
Returns Varchar(10) AS
Begin
declare @.hor int
declare @.min int
declare @.sec int

Set @.hor=datediff(hour,@.StDate,@.EnDate)
Set @.min=datediff(mi,@.StDate,@.EnDate)-@.hor*60
Set @.sec=datediff(s,@.StDate,@.EnDate)-@.hor*60*60-@.min*60

Return convert(char(2),@.hor) +':'+ convert(char(2),@.min) + ':'+ convert(char(2),@.sec)
End

select dbo.Fun_TimeDiff('2006/01/03 07:30:00.000','2006/01/03 17:41:10.000')

it will give u the result

10:11:10