DB backup to a mapped drive on another server?

In SQL 2000, is it possible to perform a DB backup where the backup is
written to a disk drive on a different server. Someone had mentioned that
this is possible using backup devices (instead of backup files).
I tried adding a dump device with the following command:
sp_addumpdevice @.devtype = 'disk',
@.logicalname = 'SorClinTest_E',
@.physicalname = '\\RemoteServerName\e$\Backuptest\FromProd.ext'
When i tried backing up to that device i got an error that the backup failed
(no details). I am guessing that it failed because of the remote device.
If anyone can shed some light on this - I would appreciate it.
Thanks,
Tom
See if this helps: http://vyaskn.tripod.com/administration_faq.htm#q17
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"TJT" <TJT@.nospam.com> wrote in message
news:OTIQNZTNFHA.1176@.TK2MSFTNGP12.phx.gbl...
In SQL 2000, is it possible to perform a DB backup where the backup is
written to a disk drive on a different server. Someone had mentioned that
this is possible using backup devices (instead of backup files).
I tried adding a dump device with the following command:
sp_addumpdevice @.devtype = 'disk',
@.logicalname = 'SorClinTest_E',
@.physicalname = '\\RemoteServerName\e$\Backuptest\FromProd.ext'
When i tried backing up to that device i got an error that the backup failed
(no details). I am guessing that it failed because of the remote device.
If anyone can shed some light on this - I would appreciate it.
Thanks,
Tom
|||You don't need to add a dump device if you just want to backup the database
to another server.
BACKUP DATABASE dbname TO DISK = '\\server\share\filename'
"TJT" wrote:

> In SQL 2000, is it possible to perform a DB backup where the backup is
> written to a disk drive on a different server. Someone had mentioned that
> this is possible using backup devices (instead of backup files).
> I tried adding a dump device with the following command:
> sp_addumpdevice @.devtype = 'disk',
> @.logicalname = 'SorClinTest_E',
> @.physicalname = '\\RemoteServerName\e$\Backuptest\FromProd.ext'
> When i tried backing up to that device i got an error that the backup failed
> (no details). I am guessing that it failed because of the remote device.
> If anyone can shed some light on this - I would appreciate it.
> Thanks,
> Tom
>
>
|||Hello Tom,
Check the login id the SQLServer service runs as and login to the server
with that ID and try to copy a file
into that share. If you cannot copy the file then you have a
access/permission problem ... etc.
ie, Can you copy a file to this location " \RemoteServerName\e$\Backuptest\
" using the login id that SQLServer
service runs with. Try this from command prompt.
Gopi
"TJT" <TJT@.nospam.com> wrote in message
news:OTIQNZTNFHA.1176@.TK2MSFTNGP12.phx.gbl...
> In SQL 2000, is it possible to perform a DB backup where the backup is
> written to a disk drive on a different server. Someone had mentioned that
> this is possible using backup devices (instead of backup files).
> I tried adding a dump device with the following command:
> sp_addumpdevice @.devtype = 'disk',
> @.logicalname = 'SorClinTest_E',
> @.physicalname = '\\RemoteServerName\e$\Backuptest\FromProd.ext'
> When i tried backing up to that device i got an error that the backup
> failed
> (no details). I am guessing that it failed because of the remote device.
> If anyone can shed some light on this - I would appreciate it.
> Thanks,
> Tom
>
|||All The above are correct. I will add the idea of getting backup in a dump
device with Complete, differential, transactional backups to another server.
1. Create a map network drive
2. Restart your sql server in order to see the drive
3. Create a dump device via EM (It's easier) pointing the drive
4. Create the jobs you want.
Andreas
"Jack" wrote:
[vbcol=seagreen]
> You don't need to add a dump device if you just want to backup the database
> to another server.
> BACKUP DATABASE dbname TO DISK = '\\server\share\filename'
> "TJT" wrote:
|||Yes , follow the below steps.
There are Few Pre requisites to do backup remotely;
1. You Should start SQL server using Domain user who got access to remote machine Share
2. Should have share in the remote machine
3. If you need to schedule this as a job then SQL Agent should use the same Domain user in which SQL server was started
4. Restart the services
Now you can execute the Backup script with UNC path
BACKUP Database <dbname> to disk='\\computername\sharenameX\dbname.bak' with init
Note:
Backup to remote machine will not work if you start SQL server using Local system account
Thanks
Hari
SQL Server MVP
____________________________________
TJT Wrote:
In SQL 2000, is it possible to perform a DB backup where the backup is
written to a disk drive on a different server. Someone had mentioned that
this is possible using backup devices (instead of backup files).
I tried adding a dump device with the following command:
sp_addumpdevice @.devtype = 'disk',
@.logicalname = 'SorClinTest_E',
@.physicalname = '\\RemoteServerName\e$\Backuptest\FromProd.ext'
When i tried backing up to that device i got an error that the backup failed
(no details). I am guessing that it failed because of the remote device.
If anyone can shed some light on this - I would appreciate it.
Thanks,
Tom
Sent via SreeSharp NewsReader http://www.SreeSharp.com
sql

DB backup to a mapped drive on another server?

In SQL 2000, is it possible to perform a DB backup where the backup is
written to a disk drive on a different server. Someone had mentioned that
this is possible using backup devices (instead of backup files).
I tried adding a dump device with the following command:
sp_addumpdevice @.devtype = 'disk',
@.logicalname = 'SorClinTest_E',
@.physicalname = '\\RemoteServerName\e$\Backuptest\FromProd.ext'
When i tried backing up to that device i got an error that the backup failed
(no details). I am guessing that it failed because of the remote device.
If anyone can shed some light on this - I would appreciate it.
Thanks,
TomSee if this helps: http://vyaskn.tripod.com/administration_faq.htm#q17
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"TJT" <TJT@.nospam.com> wrote in message
news:OTIQNZTNFHA.1176@.TK2MSFTNGP12.phx.gbl...
In SQL 2000, is it possible to perform a DB backup where the backup is
written to a disk drive on a different server. Someone had mentioned that
this is possible using backup devices (instead of backup files).
I tried adding a dump device with the following command:
sp_addumpdevice @.devtype = 'disk',
@.logicalname = 'SorClinTest_E',
@.physicalname = '\\RemoteServerName\e$\Backuptest\FromProd.ext'
When i tried backing up to that device i got an error that the backup failed
(no details). I am guessing that it failed because of the remote device.
If anyone can shed some light on this - I would appreciate it.
Thanks,
Tom|||You don't need to add a dump device if you just want to backup the database
to another server.
BACKUP DATABASE dbname TO DISK = '\\server\share\filename'
"TJT" wrote:
> In SQL 2000, is it possible to perform a DB backup where the backup is
> written to a disk drive on a different server. Someone had mentioned that
> this is possible using backup devices (instead of backup files).
> I tried adding a dump device with the following command:
> sp_addumpdevice @.devtype = 'disk',
> @.logicalname = 'SorClinTest_E',
> @.physicalname = '\\RemoteServerName\e$\Backuptest\FromProd.ext'
> When i tried backing up to that device i got an error that the backup failed
> (no details). I am guessing that it failed because of the remote device.
> If anyone can shed some light on this - I would appreciate it.
> Thanks,
> Tom
>
>|||Hello Tom,
Check the login id the SQLServer service runs as and login to the server
with that ID and try to copy a file
into that share. If you cannot copy the file then you have a
access/permission problem ... etc.
ie, Can you copy a file to this location " \RemoteServerName\e$\Backuptest\
" using the login id that SQLServer
service runs with. Try this from command prompt.
Gopi
"TJT" <TJT@.nospam.com> wrote in message
news:OTIQNZTNFHA.1176@.TK2MSFTNGP12.phx.gbl...
> In SQL 2000, is it possible to perform a DB backup where the backup is
> written to a disk drive on a different server. Someone had mentioned that
> this is possible using backup devices (instead of backup files).
> I tried adding a dump device with the following command:
> sp_addumpdevice @.devtype = 'disk',
> @.logicalname = 'SorClinTest_E',
> @.physicalname = '\\RemoteServerName\e$\Backuptest\FromProd.ext'
> When i tried backing up to that device i got an error that the backup
> failed
> (no details). I am guessing that it failed because of the remote device.
> If anyone can shed some light on this - I would appreciate it.
> Thanks,
> Tom
>|||All The above are correct. I will add the idea of getting backup in a dump
device with Complete, differential, transactional backups to another server.
1. Create a map network drive
2. Restart your sql server in order to see the drive
3. Create a dump device via EM (It's easier) pointing the drive
4. Create the jobs you want.
Andreas
"Jack" wrote:
> You don't need to add a dump device if you just want to backup the database
> to another server.
> BACKUP DATABASE dbname TO DISK = '\\server\share\filename'
> "TJT" wrote:
> > In SQL 2000, is it possible to perform a DB backup where the backup is
> > written to a disk drive on a different server. Someone had mentioned that
> > this is possible using backup devices (instead of backup files).
> >
> > I tried adding a dump device with the following command:
> > sp_addumpdevice @.devtype = 'disk',
> > @.logicalname = 'SorClinTest_E',
> > @.physicalname = '\\RemoteServerName\e$\Backuptest\FromProd.ext'
> >
> > When i tried backing up to that device i got an error that the backup failed
> > (no details). I am guessing that it failed because of the remote device.
> >
> > If anyone can shed some light on this - I would appreciate it.
> >
> > Thanks,
> > Tom
> >
> >
> >

DB backup to a mapped drive on another server?

In SQL 2000, is it possible to perform a DB backup where the backup is
written to a disk drive on a different server. Someone had mentioned that
this is possible using backup devices (instead of backup files).
I tried adding a dump device with the following command:
sp_addumpdevice @.devtype = 'disk',
@.logicalname = 'SorClinTest_E',
@.physicalname = '\\RemoteServerName\e$\Backuptest\FromPr
od.ext'
When i tried backing up to that device i got an error that the backup failed
(no details). I am guessing that it failed because of the remote device.
If anyone can shed some light on this - I would appreciate it.
Thanks,
TomSee if this helps: http://vyaskn.tripod.com/administration_faq.htm#q17
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"TJT" <TJT@.nospam.com> wrote in message
news:OTIQNZTNFHA.1176@.TK2MSFTNGP12.phx.gbl...
In SQL 2000, is it possible to perform a DB backup where the backup is
written to a disk drive on a different server. Someone had mentioned that
this is possible using backup devices (instead of backup files).
I tried adding a dump device with the following command:
sp_addumpdevice @.devtype = 'disk',
@.logicalname = 'SorClinTest_E',
@.physicalname = '\\RemoteServerName\e$\Backuptest\FromPr
od.ext'
When i tried backing up to that device i got an error that the backup failed
(no details). I am guessing that it failed because of the remote device.
If anyone can shed some light on this - I would appreciate it.
Thanks,
Tom|||You don't need to add a dump device if you just want to backup the database
to another server.
BACKUP DATABASE dbname TO DISK = '\\server\share\filename'
"TJT" wrote:

> In SQL 2000, is it possible to perform a DB backup where the backup is
> written to a disk drive on a different server. Someone had mentioned that
> this is possible using backup devices (instead of backup files).
> I tried adding a dump device with the following command:
> sp_addumpdevice @.devtype = 'disk',
> @.logicalname = 'SorClinTest_E',
> @.physicalname = '\\RemoteServerName\e$\Backuptest\FromPr
od.ext'
> When i tried backing up to that device i got an error that the backup fail
ed
> (no details). I am guessing that it failed because of the remote device.
> If anyone can shed some light on this - I would appreciate it.
> Thanks,
> Tom
>
>|||Hello Tom,
Check the login id the SQLServer service runs as and login to the server
with that ID and try to copy a file
into that share. If you cannot copy the file then you have a
access/permission problem ... etc.
ie, Can you copy a file to this location " \RemoteServerName\e$\Backuptest\
" using the login id that SQLServer
service runs with. Try this from command prompt.
Gopi
"TJT" <TJT@.nospam.com> wrote in message
news:OTIQNZTNFHA.1176@.TK2MSFTNGP12.phx.gbl...
> In SQL 2000, is it possible to perform a DB backup where the backup is
> written to a disk drive on a different server. Someone had mentioned that
> this is possible using backup devices (instead of backup files).
> I tried adding a dump device with the following command:
> sp_addumpdevice @.devtype = 'disk',
> @.logicalname = 'SorClinTest_E',
> @.physicalname = '\\RemoteServerName\e$\Backuptest\FromPr
od.ext'
> When i tried backing up to that device i got an error that the backup
> failed
> (no details). I am guessing that it failed because of the remote device.
> If anyone can shed some light on this - I would appreciate it.
> Thanks,
> Tom
>|||All The above are correct. I will add the idea of getting backup in a dump
device with Complete, differential, transactional backups to another server.
1. Create a map network drive
2. Restart your sql server in order to see the drive
3. Create a dump device via EM (It's easier) pointing the drive
4. Create the jobs you want.
Andreas
"Jack" wrote:
[vbcol=seagreen]
> You don't need to add a dump device if you just want to backup the databas
e
> to another server.
> BACKUP DATABASE dbname TO DISK = '\\server\share\filename'
> "TJT" wrote:
>|||Yes , follow the below steps.
There are Few Pre requisites to do backup remotely;
1. You Should start SQL server using Domain user who got access to remote ma
chine Share
2. Should have share in the remote machine
3. If you need to schedule this as a job then SQL Agent should use the same
Domain user in which SQL server was started
4. Restart the services
Now you can execute the Backup script with UNC path
BACKUP Database <dbname> to disk='\\computername\sharename_\dbname.bak' wit
h init
Note:
Backup to remote machine will not work if you start SQL server using Local s
ystem account
Thanks
Hari
SQL Server MVP
____________________________________
TJT Wrote:
In SQL 2000, is it possible to perform a DB backup where the backup is
written to a disk drive on a different server. Someone had mentioned that
this is possible using backup devices (instead of backup files).
I tried adding a dump device with the following command:
sp_addumpdevice @.devtype = 'disk',
@.logicalname = 'SorClinTest_E',
@.physicalname = '\\RemoteServerName\e$\Backuptest\FromPr
od.ext'
When i tried backing up to that device i got an error that the backup failed
(no details). I am guessing that it failed because of the remote device.
If anyone can shed some light on this - I would appreciate it.
Thanks,
Tom
Sent via SreeSharp NewsReader http://www.SreeSharp.com

days between dates from a list

Hi,
I am trying to perform an interpolation of counts between event dates...my
data looks like this:
Event Date Count
1/1/06 13
1/17/06 9
2/3/06 7 etc...
The spacing of event date is not always equal thus I need to be able to do
something like this: (date1-nextdate). I don't know how to select the next
date. Any help if greatly appreciated.
Jen...learning
This might work and be fast if event date is a PK or indexed:
SELECT
E.[EventDate], E.[CountOfThings], dbo.ufn_NextEvent(E.[EventDate]) AS
NextDate
FROM
Events E
Where dbo.ufn_NextEvent is a user defined function like:
CREATE FUNCTION [dbo].[ufn_NextEvent]
(
@.ThisEvent DATETIME
)
RETURNS DATETIME
AS
BEGIN
DECLARE @.result DATETIME
SELECT TOP 1 @.result = [EventDate] FROM Events WHERE [EventDate] > @.ThisEvent
RETURN (@.result)
END
Result set is:
2006-01-01 00:00:00.000132006-01-17 00:00:00.000
2006-01-17 00:00:00.00092006-02-03 00:00:00.000
2006-02-03 00:00:00.0007NULL
Regards,
JayAchTee
"jennifer.heintz" wrote:

> Hi,
> I am trying to perform an interpolation of counts between event dates...my
> data looks like this:
> Event Date Count
> 1/1/06 13
> 1/17/06 9
> 2/3/06 7 etc...
> The spacing of event date is not always equal thus I need to be able to do
> something like this: (date1-nextdate). I don't know how to select the next
> date. Any help if greatly appreciated.
> --
> Jen...learning

datetime vs varchar

If I insert datatime values in a varchar datatype as opposed to datetime,
what am I losing out on ?
Would I be able to perform the same functions against a varchar datatype as
opposed to datetime such as datepart, datediff,etc. ?
Hi Hassan
Why don't you try it and see? Something like this should give you a start:
declare @.today varchar(30)
select @.today = getdate()
select @.today
select dateadd(mm, 1, @.today)
And then read Tibor's excellent article:
http://www.karaszi.com/sqlserver/info_datetime.asp
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Hassan" <hassan@.test.com> wrote in message
news:uDdP0$EbIHA.748@.TK2MSFTNGP04.phx.gbl...
> If I insert datatime values in a varchar datatype as opposed to datetime,
> what am I losing out on ?
> Would I be able to perform the same functions against a varchar datatype
> as opposed to datetime such as datepart, datediff,etc. ?
>
>
|||Kalen,
Does not seem to be any difference with that example.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ODKGXIFbIHA.5980@.TK2MSFTNGP04.phx.gbl...
> Hi Hassan
> Why don't you try it and see? Something like this should give you a start:
> declare @.today varchar(30)
> select @.today = getdate()
> select @.today
> select dateadd(mm, 1, @.today)
> And then read Tibor's excellent article:
> http://www.karaszi.com/sqlserver/info_datetime.asp
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Hassan" <hassan@.test.com> wrote in message
> news:uDdP0$EbIHA.748@.TK2MSFTNGP04.phx.gbl...
>
|||Hassan would have found all that out if he read the article I pointed him
to.
;-)
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uB0OLMIbIHA.1376@.TK2MSFTNGP02.phx.gbl...
> Downsides of using varchar include:
> There's nothing stopping you from inserting invalid date (like Feb 30), or
> time values.
> Performing various datetime calculation might mean bad performance, since
> you might end up with a convert on the column side in your predicate.
> You need to decide on a format. If you want to retrieve the value and
> present it in a different format from what it is stored with you have more
> work to do.
> ...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Hassan" <hassan@.test.com> wrote in message
> news:evp1jaFbIHA.4180@.TK2MSFTNGP06.phx.gbl...
>
|||On Feb 11, 6:46Xam, "Hassan" <has...@.test.com> wrote:
> If I insert datatime values in a varchar datatype as opposed to datetime,
> what am I losing out on ?
> Would I be able to perform the same functions against a varchar datatype as
> opposed to datetime such as datepart, datediff,etc. ?
Nothing but you would end up with too much convertions if you want to
manipulate varchars
(ex order by, usage of datediff, dateadd,etc)
Always use proper DATETIME datatype to store dates and let your front
end application do the formation
sql

datetime vs varchar

If I insert datatime values in a varchar datatype as opposed to datetime,
what am I losing out on ?
Would I be able to perform the same functions against a varchar datatype as
opposed to datetime such as datepart, datediff,etc. ?Hi Hassan
Why don't you try it and see? Something like this should give you a start:
declare @.today varchar(30)
select @.today = getdate()
select @.today
select dateadd(mm, 1, @.today)
And then read Tibor's excellent article:
http://www.karaszi.com/sqlserver/info_datetime.asp
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Hassan" <hassan@.test.com> wrote in message
news:uDdP0$EbIHA.748@.TK2MSFTNGP04.phx.gbl...
> If I insert datatime values in a varchar datatype as opposed to datetime,
> what am I losing out on ?
> Would I be able to perform the same functions against a varchar datatype
> as opposed to datetime such as datepart, datediff,etc. ?
>
>|||Kalen,
Does not seem to be any difference with that example.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ODKGXIFbIHA.5980@.TK2MSFTNGP04.phx.gbl...
> Hi Hassan
> Why don't you try it and see? Something like this should give you a start:
> declare @.today varchar(30)
> select @.today = getdate()
> select @.today
> select dateadd(mm, 1, @.today)
> And then read Tibor's excellent article:
> http://www.karaszi.com/sqlserver/info_datetime.asp
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Hassan" <hassan@.test.com> wrote in message
> news:uDdP0$EbIHA.748@.TK2MSFTNGP04.phx.gbl...
>> If I insert datatime values in a varchar datatype as opposed to datetime,
>> what am I losing out on ?
>> Would I be able to perform the same functions against a varchar datatype
>> as opposed to datetime such as datepart, datediff,etc. ?
>>
>|||Downsides of using varchar include:
There's nothing stopping you from inserting invalid date (like Feb 30), or time values.
Performing various datetime calculation might mean bad performance, since you might end up with a
convert on the column side in your predicate.
You need to decide on a format. If you want to retrieve the value and present it in a different
format from what it is stored with you have more work to do.
...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Hassan" <hassan@.test.com> wrote in message news:evp1jaFbIHA.4180@.TK2MSFTNGP06.phx.gbl...
> Kalen,
> Does not seem to be any difference with that example.
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:ODKGXIFbIHA.5980@.TK2MSFTNGP04.phx.gbl...
>> Hi Hassan
>> Why don't you try it and see? Something like this should give you a start:
>> declare @.today varchar(30)
>> select @.today = getdate()
>> select @.today
>> select dateadd(mm, 1, @.today)
>> And then read Tibor's excellent article:
>> http://www.karaszi.com/sqlserver/info_datetime.asp
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Hassan" <hassan@.test.com> wrote in message news:uDdP0$EbIHA.748@.TK2MSFTNGP04.phx.gbl...
>> If I insert datatime values in a varchar datatype as opposed to datetime, what am I losing out
>> on ?
>> Would I be able to perform the same functions against a varchar datatype as opposed to datetime
>> such as datepart, datediff,etc. ?
>>
>>
>|||Hassan would have found all that out if he read the article I pointed him
to.
;-)
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uB0OLMIbIHA.1376@.TK2MSFTNGP02.phx.gbl...
> Downsides of using varchar include:
> There's nothing stopping you from inserting invalid date (like Feb 30), or
> time values.
> Performing various datetime calculation might mean bad performance, since
> you might end up with a convert on the column side in your predicate.
> You need to decide on a format. If you want to retrieve the value and
> present it in a different format from what it is stored with you have more
> work to do.
> ...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Hassan" <hassan@.test.com> wrote in message
> news:evp1jaFbIHA.4180@.TK2MSFTNGP06.phx.gbl...
>> Kalen,
>> Does not seem to be any difference with that example.
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:ODKGXIFbIHA.5980@.TK2MSFTNGP04.phx.gbl...
>> Hi Hassan
>> Why don't you try it and see? Something like this should give you a
>> start:
>> declare @.today varchar(30)
>> select @.today = getdate()
>> select @.today
>> select dateadd(mm, 1, @.today)
>> And then read Tibor's excellent article:
>> http://www.karaszi.com/sqlserver/info_datetime.asp
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Hassan" <hassan@.test.com> wrote in message
>> news:uDdP0$EbIHA.748@.TK2MSFTNGP04.phx.gbl...
>> If I insert datatime values in a varchar datatype as opposed to
>> datetime, what am I losing out on ?
>> Would I be able to perform the same functions against a varchar
>> datatype as opposed to datetime such as datepart, datediff,etc. ?
>>
>>
>>
>|||On Feb 11, 6:46=A0am, "Hassan" <has...@.test.com> wrote:
> If I insert datatime values in a varchar datatype as opposed to datetime,
> what am I losing out on ?
> Would I be able to perform the same functions against a varchar datatype a=s
> opposed to datetime such as datepart, datediff,etc. ?
Nothing but you would end up with too much convertions if you want to
manipulate varchars
(ex order by, usage of datediff, dateadd,etc)
Always use proper DATETIME datatype to store dates and let your front
end application do the formation|||> Hassan would have found all that out if he read the article I pointed him to.
Ah, that ol' article ;-)
Thanks Kalen :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OkINH5MbIHA.1376@.TK2MSFTNGP02.phx.gbl...
> Hassan would have found all that out if he read the article I pointed him to.
> ;-)
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:uB0OLMIbIHA.1376@.TK2MSFTNGP02.phx.gbl...
>> Downsides of using varchar include:
>> There's nothing stopping you from inserting invalid date (like Feb 30), or time values.
>> Performing various datetime calculation might mean bad performance, since you might end up with a
>> convert on the column side in your predicate.
>> You need to decide on a format. If you want to retrieve the value and present it in a different
>> format from what it is stored with you have more work to do.
>> ...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Hassan" <hassan@.test.com> wrote in message news:evp1jaFbIHA.4180@.TK2MSFTNGP06.phx.gbl...
>> Kalen,
>> Does not seem to be any difference with that example.
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:ODKGXIFbIHA.5980@.TK2MSFTNGP04.phx.gbl...
>> Hi Hassan
>> Why don't you try it and see? Something like this should give you a start:
>> declare @.today varchar(30)
>> select @.today = getdate()
>> select @.today
>> select dateadd(mm, 1, @.today)
>> And then read Tibor's excellent article:
>> http://www.karaszi.com/sqlserver/info_datetime.asp
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Hassan" <hassan@.test.com> wrote in message news:uDdP0$EbIHA.748@.TK2MSFTNGP04.phx.gbl...
>> If I insert datatime values in a varchar datatype as opposed to datetime, what am I losing out
>> on ?
>> Would I be able to perform the same functions against a varchar datatype as opposed to
>> datetime such as datepart, datediff,etc. ?
>>
>>
>>
>>
>

Datetime and Indexes

Hi,
I have a table which i need to perform ranging and ordering queries on
a datetime column. Sql server doesn't seem to use nonclustered indexes
on datetime columns for range queries. Would it be better if i create a
clustered index on the datetime column and have a non clustered index
on my primary identity key?Yeah. Anytime you need to perform range selection and ordering you
should consider creating an clustered index for that column.

Dates

Hi,

I have date columns in my tables defined as smalldatetime. How do I perform a Select that will retrieve records with dates equal to the date selected in a calendar control as follows:

<asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:ReservationsConnectionString %>"

SelectCommand="SELECT [TIM_Time], [TIM_ID], [TIM_Valid_From] FROM [Times] WHERE ([TIM_Time] = @.TIM_Time)">

<SelectParameters>

<asp:ControlParameter ControlID="Calendar1" Name="TIM_Time" PropertyName="SelectedDate"

Type="DateTime" />

</SelectParameters>

I get no records when I know they are there - do I have to put in additional checks to cater for the time component of the column?

Thanks in advance.

Assuming you can guarantee no time value on the @.TIM_Time parameter

SELECT [TIM_Time], [TIM_ID], [TIM_Valid_From]
FROM [Times]
WHERE ([TIM_Time] >= @.TIM_Time and [TIM_Time] < dateadd(day,1,@.TIM_Time))">

This gets all times from midnight on the day, to anything before midnight on the next day.

<light advice>I would seriously reconsider your naming convention of including a table prefix for every column, especially one that is an abbreviation. That will get seriously old over time having to type SOMETHING_ in front of every column, and then having to remove it with an alias everytime you want to display it to a user. </light advice>

|||

If TIM_Time contain only date (without time) then you can use this code

SELECT [TIM_Time], [TIM_ID], [TIM_Valid_From] FROM [Times] WHERE ([TIM_Time] = convert(varchar, @.TIM_Time, 112)

|||

Thaks for this but how does one hold only a date?

Is there a simple way of selecting the date part and the time part?

|||

you also can use Functions for this. You can do it in T-SQL (see following code), or write an equivalent with code behind.

declare @.dt as datetime, @.tm as datetime

select @.dt = getdate(), @.tm = getdate()

select @.tm, @.dt

select @.dt = dbo.FN_DATETIME_AS_HMS(@.dt)
select @.tm = dbo.FN_DATETIME_AS_DATE(@.tm)

select @.dt, @.tm

Here the functions code :

CREATE FUNCTION FN_DATETIME_AS_HMS (@.DT DATETIME)
RETURNS CHAR(8) AS
BEGIN
IF @.DT IS NULL RETURN NULL
DECLARE @.H INT
DECLARE @.M INT
DECLARE @.S INT
SET @.H = DATEPART(HOUR, @.DT)
SET @.M = DATEPART(MINUTE, @.DT)
SET @.S = DATEPART(SECOND, @.DT)
DECLARE @.RETVAL VARCHAR(8)
IF @.H < 10
SET @.RETVAL = '0' + CAST(@.H AS CHAR(1))+':'
ELSE
SET @.RETVAL = CAST(@.H AS CHAR(2))+':'
IF @.M < 10
SET @.RETVAL = @.RETVAL + '0' + CAST(@.M AS CHAR(1))+':'
ELSE
SET @.RETVAL = @.RETVAL + CAST(@.M AS CHAR(2))+':'
IF @.S < 10
SET @.RETVAL = @.RETVAL + '0' + CAST(@.S AS CHAR(1))
ELSE
SET @.RETVAL = @.RETVAL + CAST(@.S AS CHAR(2))
RETURN CAST(@.RETVAL AS CHAR(8))
END

--

CREATE FUNCTION FN_DATETIME_AS_DATE (@.DT DATETIME)
RETURNS DATETIME AS
BEGIN
RETURN CAST(FLOOR(CAST(@.DT AS FLOAT)) AS DATETIME)
END

ps : sources http://sqlpro.developpez.com/cours/sqlserver/udf

|||

Stephane,

Many thanks for this and sorry to be so ignorant but where exactly do I place the first part of your code? I created the 2 functions but I tried to place the first part it in a method but received an error message saying that the declare statement is not valid in a method.

|||

this is T-SQL only. it was just a sample to hava a look at the result. to run this code, execute it in Query Analyzer.

the important part is :

select dbo.FN_DATETIME_AS_HMS([Put a dateTime here])

and you'll have you time, with a 'null' date, fixed to 01/01/1900 and a valid hour. You also could choos another default value for null date. Some use 01/01/1753 with SQL Server.

select dbo.FN_DATETIME_AS_DATE([Put a DateTime here])

here, you'll have a date time with your correct date, and time set to 00:00:00.

|||

Stephane,

Thanks a lot, that was very helpful - I'm getting there - slowly!

Dates

Hi,

I have date columns in my tables defined as smalldatetime. How do I perform a Select that will retrieve records with dates equal to the date selected in a calendar control as follows:

<asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:ReservationsConnectionString %>"

SelectCommand="SELECT [TIM_Time], [TIM_ID], [TIM_Valid_From] FROM [Times] WHERE ([TIM_Time] = @.TIM_Time)">

<SelectParameters>

<asp:ControlParameter ControlID="Calendar1" Name="TIM_Time" PropertyName="SelectedDate"

Type="DateTime" />

</SelectParameters>

I get no records when I know they are there - do I have to put in additional checks to cater for the time component of the column?

Thanks in advance.

Assuming you can guarantee no time value on the @.TIM_Time parameter

SELECT [TIM_Time], [TIM_ID], [TIM_Valid_From]
FROM [Times]
WHERE ([TIM_Time] >= @.TIM_Time and [TIM_Time] < dateadd(day,1,@.TIM_Time))">

This gets all times from midnight on the day, to anything before midnight on the next day.

<light advice>I would seriously reconsider your naming convention of including a table prefix for every column, especially one that is an abbreviation. That will get seriously old over time having to type SOMETHING_ in front of every column, and then having to remove it with an alias everytime you want to display it to a user. </light advice>

|||

If TIM_Time contain only date (without time) then you can use this code

SELECT [TIM_Time], [TIM_ID], [TIM_Valid_From] FROM [Times] WHERE ([TIM_Time] = convert(varchar, @.TIM_Time, 112)

|||

Thaks for this but how does one hold only a date?

Is there a simple way of selecting the date part and the time part?

|||

you also can use Functions for this. You can do it in T-SQL (see following code), or write an equivalent with code behind.

declare @.dt as datetime, @.tm as datetime

select @.dt = getdate(), @.tm = getdate()

select @.tm, @.dt

select @.dt = dbo.FN_DATETIME_AS_HMS(@.dt)
select @.tm = dbo.FN_DATETIME_AS_DATE(@.tm)

select @.dt, @.tm

Here the functions code :

CREATE FUNCTION FN_DATETIME_AS_HMS (@.DT DATETIME)
RETURNS CHAR(8) AS
BEGIN
IF @.DT IS NULL RETURN NULL
DECLARE @.H INT
DECLARE @.M INT
DECLARE @.S INT
SET @.H = DATEPART(HOUR, @.DT)
SET @.M = DATEPART(MINUTE, @.DT)
SET @.S = DATEPART(SECOND, @.DT)
DECLARE @.RETVAL VARCHAR(8)
IF @.H < 10
SET @.RETVAL = '0' + CAST(@.H AS CHAR(1))+':'
ELSE
SET @.RETVAL = CAST(@.H AS CHAR(2))+':'
IF @.M < 10
SET @.RETVAL = @.RETVAL + '0' + CAST(@.M AS CHAR(1))+':'
ELSE
SET @.RETVAL = @.RETVAL + CAST(@.M AS CHAR(2))+':'
IF @.S < 10
SET @.RETVAL = @.RETVAL + '0' + CAST(@.S AS CHAR(1))
ELSE
SET @.RETVAL = @.RETVAL + CAST(@.S AS CHAR(2))
RETURN CAST(@.RETVAL AS CHAR(8))
END

--

CREATE FUNCTION FN_DATETIME_AS_DATE (@.DT DATETIME)
RETURNS DATETIME AS
BEGIN
RETURN CAST(FLOOR(CAST(@.DT AS FLOAT)) AS DATETIME)
END

ps : sources http://sqlpro.developpez.com/cours/sqlserver/udf

|||

Stephane,

Many thanks for this and sorry to be so ignorant but where exactly do I place the first part of your code? I created the 2 functions but I tried to place the first part it in a method but received an error message saying that the declare statement is not valid in a method.

|||

this is T-SQL only. it was just a sample to hava a look at the result. to run this code, execute it in Query Analyzer.

the important part is :

select dbo.FN_DATETIME_AS_HMS([Put a dateTime here])

and you'll have you time, with a 'null' date, fixed to 01/01/1900 and a valid hour. You also could choos another default value for null date. Some use 01/01/1753 with SQL Server.

select dbo.FN_DATETIME_AS_DATE([Put a DateTime here])

here, you'll have a date time with your correct date, and time set to 00:00:00.

|||

Stephane,

Thanks a lot, that was very helpful - I'm getting there - slowly!

Dateadd Function in ReportViewer

Hi,

I need to perform dateadd function on one of my columns in my report.

So I assigned an expression to that column which goes like this.

=Dateadd(DateInterval.Hour,Fields!InitialDrive.Value,Fields!EarliestArrivalTime.Value)

It doesnt look like working... It comes out in the report as #ERROR

Where am i going wrong?

Regards,

Lavanya.

you need 3 arguments

DATEADD(datepart,number,date)

Arguments

DatepartAbbreviationsYearyy, yyyyquarterqq, qMonthmm, mdayofyeardy, yDaydd, dWeekwk, wwHourhhminutemi, nsecondss, smillisecondms

|||

Can you do the DATEADD in the stored procedure that you run?

|||

Yeah... I have 3 arguments

|||

Yeah... I can go with Stored procedures... But I am not sure how to add decimal number

DATEADD(hh,1.25,10/22/2007 8:00 AM) and it shud give me 10/22/2007 9:15 AM

Any idea how to do it...

|||

The commands
DECLARE @.TEST DATETIME
SET @.TEST = CONVERT(DATETIME,'10/22/2007 8:00 AM')
PRINT @.TEST
PRINT DATEADD(hh, 1.25, @.TEST)
PRINT DATEADD(mi, (1.25 * 60), @.TEST) -- note use of mi (orminute)
give
Oct 22 2007 8:00AM
Oct 22 2007 9:00AM
Oct 22 2007 9:15AM -- The answer you require!

|||

Yes! the DateInterval.xxx is units of the integer of the second argument (Tricky)