Copy database wizard. Invalid column name 'AccountCode'

I've been trying the copy database wizard for hours, each time getting one error or another. The most recent one is "Invalid column name 'AccountCode'". In the error message it tells me to change the MaximumErrorCount - I can't find any information on how to do that.

I'm using VPN to access the source server. The source is SQL Server 2000 from the client's server. The destination is SQL Server 2005 from the MSDN Premium subscription. The username has every server role checked. Part of the transfer is successful. I'm using SMO instead of Attach & Detach.

What's wrong with 'AccountCode'?

How do I change MaximumErrorCount in the SQL Server Management studio?

TIA

Mike

what is service pack applied on this machine. is there any particular reason you are going for copy database wizards. have u tried Backup/restore or dettach/attach method. You apply sp2 for sqlserver 2005 and try CDW

Madhu

|||

I applied SQL Server SP2 as part of my attempts to get it working yesterday. The OS is XP and has had XP SP 2 for a while.

I tried the backup / restore yesterday and the database was created but I could not see any tables, views, sp, etc. I just now I tried it again but this time I checked the "Overwrite existing database" selection and it seems to have worked just fine this time.

So I'm good to go now - I still don't know why CDW didn't work. I had deleted the database each time I tried it yesterday so it couldn't be that.

Thanks for the reply. At least it got me to try the restore option again.

Mike

Copy database question

Is there a way to copy a database on a scheduled basis and name the copy
different names every time ?
For example: database copy for today will be named mydb_061207, database
copy for tomorrow will be named mydb_061307, etc.
Thank you.
Craft a RESTORE statement using dynamic sql and a date variable formatted as
112 (see Convert in BOL).
TheSQLGuru
President
Indicium Resources, Inc.
"fniles" <fniles@.pfmail.com> wrote in message
news:eRmOKcSrHHA.3248@.TK2MSFTNGP03.phx.gbl...
> Is there a way to copy a database on a scheduled basis and name the copy
> different names every time ?
> For example: database copy for today will be named mydb_061207, database
> copy for tomorrow will be named mydb_061307, etc.
> Thank you.
>
|||Thank you.
I am sorry, I am new to 2005. What did you mean by crafting a restore
statement using dynamic sql ?
When I copy the database, in the SQL Server Management STudio I right click
on the database - Tasks - Copy database and use the copy wizard. This is
stored as a job. I would like the job to copy to a different file everyday.
How can I do that ?
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:OZa8uITrHHA.4624@.TK2MSFTNGP06.phx.gbl...
> Craft a RESTORE statement using dynamic sql and a date variable formatted
> as 112 (see Convert in BOL).
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "fniles" <fniles@.pfmail.com> wrote in message
> news:eRmOKcSrHHA.3248@.TK2MSFTNGP03.phx.gbl...
>
|||Hi,
You'll have to write you own BACKUP script that generates the filename
dynamically based on todays date. Then you will have to schedule this to
run every day.
A very simple example of a script to do this, could be something like:
DECLARE @.Filename VARCHAR(300)
DECLARE @.BackupPath VARCHAR(300)
DECLARE @.Device VARCHAR(600)
SET @.BackupPath = 'C:\MSSQL\BACKUP\'
SET @.FileName = 'YourDatabaseName'+CONVERT(NVARCHAR(16),GETDATE(), 112) +
'.BAK'
SET @.Device = @.BackupPath+@.FileName
BACKUP DATABASE YourDataBaseName TO DISK = @.Device
This will backup your database to a file named with the databasename and
todays date in the format YYYYMMDD.It will place the backup in the
Backuppath directory you specify. Please remember that the backup folder
has to exist before you run the script.
The script is just an example to get you started. You can then add on
extra "features" as you get the need.
Regards
Steen Schlter Persson
Database Administrator / System Administrator
fniles wrote:
> Thank you.
> I am sorry, I am new to 2005. What did you mean by crafting a restore
> statement using dynamic sql ?
> When I copy the database, in the SQL Server Management STudio I right click
> on the database - Tasks - Copy database and use the copy wizard. This is
> stored as a job. I would like the job to copy to a different file everyday.
> How can I do that ?
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:OZa8uITrHHA.4624@.TK2MSFTNGP06.phx.gbl...
>
|||Thank you, that's great !
Do you by any chance know the T-SQL to COPY the database, instead of BACKUP
? I need to copy.
Thanks.
""Steen Schlter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote in
message news:OFtrUrYrHHA.484@.TK2MSFTNGP06.phx.gbl...[vbcol=seagreen]
> Hi,
> You'll have to write you own BACKUP script that generates the filename
> dynamically based on todays date. Then you will have to schedule this to
> run every day.
> A very simple example of a script to do this, could be something like:
>
> DECLARE @.Filename VARCHAR(300)
> DECLARE @.BackupPath VARCHAR(300)
> DECLARE @.Device VARCHAR(600)
> SET @.BackupPath = 'C:\MSSQL\BACKUP\'
> SET @.FileName = 'YourDatabaseName'+CONVERT(NVARCHAR(16),GETDATE(), 112) +
> '.BAK'
> SET @.Device = @.BackupPath+@.FileName
> BACKUP DATABASE YourDataBaseName TO DISK = @.Device
>
> This will backup your database to a file named with the databasename and
> todays date in the format YYYYMMDD.It will place the backup in the
> Backuppath directory you specify. Please remember that the backup folder
> has to exist before you run the script.
> The script is just an example to get you started. You can then add on
> extra "features" as you get the need.
> --
> Regards
> Steen Schlter Persson
> Database Administrator / System Administrator
>
> fniles wrote:
|||There isn't any COPY command - you'll have to use BACKUP/RESTORE.
The closet you can get to a COPY command, is to first detach your
database, copy the files manually and then attach both the original and
the new database. The commands for this is sp_detach_db and CREATE
DATABASE FOR ATTACH which you can look up in Books On Line.
Personally I prefer the BACKUP/RESTORE though. With the BACKUP/RESTORE
task you'll keep your source database on-line and operational all the
time. This means that if the backup file is corrupted or damaged so it
can't be restored, you can just make a new copy. With the detach/attach
method your source database will be off-line for the time it takes to
copy the files and until it is being attached again. Theoretically
you'll also have the risk that something has damaged the files during
(or after) the detach so they can't be attached again. In that case
you've lost both the original and the new database and you'll have to
restore your most recent backup (...which will give you even more
downtime...:-)...).
Regards
Steen Schlter Persson
Database Administrator / System Administrator
fniles wrote:
> Thank you, that's great !
> Do you by any chance know the T-SQL to COPY the database, instead of BACKUP
> ? I need to copy.
> Thanks.
> ""Steen Schlter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote in
> message news:OFtrUrYrHHA.484@.TK2MSFTNGP06.phx.gbl...
>
|||Thank you very much. That's very helpful.
If I use BACKUP/RESTORE, when restoring, is it possible to restore it to a
different database name so that it does not override the original database ?
""Steen Schlter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote in
message news:udQr4VlrHHA.3228@.TK2MSFTNGP03.phx.gbl...[vbcol=seagreen]
> There isn't any COPY command - you'll have to use BACKUP/RESTORE.
> The closet you can get to a COPY command, is to first detach your
> database, copy the files manually and then attach both the original and
> the new database. The commands for this is sp_detach_db and CREATE
> DATABASE FOR ATTACH which you can look up in Books On Line.
> Personally I prefer the BACKUP/RESTORE though. With the BACKUP/RESTORE
> task you'll keep your source database on-line and operational all the
> time. This means that if the backup file is corrupted or damaged so it
> can't be restored, you can just make a new copy. With the detach/attach
> method your source database will be off-line for the time it takes to copy
> the files and until it is being attached again. Theoretically you'll also
> have the risk that something has damaged the files during (or after) the
> detach so they can't be attached again. In that case you've lost both the
> original and the new database and you'll have to restore your most recent
> backup (...which will give you even more downtime...:-)...).
> --
> Regards
> Steen Schlter Persson
> Database Administrator / System Administrator
> fniles wrote:
|||> If I use BACKUP/RESTORE, when restoring, is it possible to restore it to a different database name
> so that it does not override the original database ?
Yes. Make sure you read up on the MOVE option of the RESTORE command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"fniles" <fniles@.pfmail.com> wrote in message news:OH%23xOIprHHA.3896@.TK2MSFTNGP04.phx.gbl...
> Thank you very much. That's very helpful.
> If I use BACKUP/RESTORE, when restoring, is it possible to restore it to a different database name
> so that it does not override the original database ?
> ""Steen Schlter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote in message
> news:udQr4VlrHHA.3228@.TK2MSFTNGP03.phx.gbl...
>

Copy database question

Is there a way to copy a database on a scheduled basis and name the copy
different names every time ?
For example: database copy for today will be named mydb_061207, database
copy for tomorrow will be named mydb_061307, etc.
Thank you.Craft a RESTORE statement using dynamic sql and a date variable formatted as
112 (see Convert in BOL).
TheSQLGuru
President
Indicium Resources, Inc.
"fniles" <fniles@.pfmail.com> wrote in message
news:eRmOKcSrHHA.3248@.TK2MSFTNGP03.phx.gbl...
> Is there a way to copy a database on a scheduled basis and name the copy
> different names every time ?
> For example: database copy for today will be named mydb_061207, database
> copy for tomorrow will be named mydb_061307, etc.
> Thank you.
>|||Thank you.
I am sorry, I am new to 2005. What did you mean by crafting a restore
statement using dynamic sql ?
When I copy the database, in the SQL Server Management STudio I right click
on the database - Tasks - Copy database and use the copy wizard. This is
stored as a job. I would like the job to copy to a different file everyday.
How can I do that ?
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:OZa8uITrHHA.4624@.TK2MSFTNGP06.phx.gbl...
> Craft a RESTORE statement using dynamic sql and a date variable formatted
> as 112 (see Convert in BOL).
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "fniles" <fniles@.pfmail.com> wrote in message
> news:eRmOKcSrHHA.3248@.TK2MSFTNGP03.phx.gbl...
>|||Hi,
You'll have to write you own BACKUP script that generates the filename
dynamically based on todays date. Then you will have to schedule this to
run every day.
A very simple example of a script to do this, could be something like:
DECLARE @.Filename VARCHAR(300)
DECLARE @.BackupPath VARCHAR(300)
DECLARE @.Device VARCHAR(600)
SET @.BackupPath = 'C:\MSSQL\BACKUP'
SET @.FileName = 'YourDatabaseName'+CONVERT(NVARCHAR(16),
GETDATE(),112) +
'.BAK'
SET @.Device = @.BackupPath+@.FileName
BACKUP DATABASE YourDataBaseName TO DISK = @.Device
This will backup your database to a file named with the databasename and
todays date in the format YYYYMMDD.It will place the backup in the
Backuppath directory you specify. Please remember that the backup folder
has to exist before you run the script.
The script is just an example to get you started. You can then add on
extra "features" as you get the need.
Regards
Steen Schlter Persson
Database Administrator / System Administrator
fniles wrote:
> Thank you.
> I am sorry, I am new to 2005. What did you mean by crafting a restore
> statement using dynamic sql ?
> When I copy the database, in the SQL Server Management STudio I right clic
k
> on the database - Tasks - Copy database and use the copy wizard. This is
> stored as a job. I would like the job to copy to a different file everyday
.
> How can I do that ?
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:OZa8uITrHHA.4624@.TK2MSFTNGP06.phx.gbl...
>|||Thank you, that's great !
Do you by any chance know the T-SQL to COPY the database, instead of BACKUP
? I need to copy.
Thanks.
""Steen Schlter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote in
message news:OFtrUrYrHHA.484@.TK2MSFTNGP06.phx.gbl...[vbcol=seagreen]
> Hi,
> You'll have to write you own BACKUP script that generates the filename
> dynamically based on todays date. Then you will have to schedule this to
> run every day.
> A very simple example of a script to do this, could be something like:
>
> DECLARE @.Filename VARCHAR(300)
> DECLARE @.BackupPath VARCHAR(300)
> DECLARE @.Device VARCHAR(600)
> SET @.BackupPath = 'C:\MSSQL\BACKUP'
> SET @.FileName = 'YourDatabaseName'+CONVERT(NVARCHAR(16),
GETDATE(),112) +
> '.BAK'
> SET @.Device = @.BackupPath+@.FileName
> BACKUP DATABASE YourDataBaseName TO DISK = @.Device
>
> This will backup your database to a file named with the databasename and
> todays date in the format YYYYMMDD.It will place the backup in the
> Backuppath directory you specify. Please remember that the backup folder
> has to exist before you run the script.
> The script is just an example to get you started. You can then add on
> extra "features" as you get the need.
> --
> Regards
> Steen Schlter Persson
> Database Administrator / System Administrator
>
> fniles wrote:|||There isn't any COPY command - you'll have to use BACKUP/RESTORE.
The closet you can get to a COPY command, is to first detach your
database, copy the files manually and then attach both the original and
the new database. The commands for this is sp_detach_db and CREATE
DATABASE FOR ATTACH which you can look up in Books On Line.
Personally I prefer the BACKUP/RESTORE though. With the BACKUP/RESTORE
task you'll keep your source database on-line and operational all the
time. This means that if the backup file is corrupted or damaged so it
can't be restored, you can just make a new copy. With the detach/attach
method your source database will be off-line for the time it takes to
copy the files and until it is being attached again. Theoretically
you'll also have the risk that something has damaged the files during
(or after) the detach so they can't be attached again. In that case
you've lost both the original and the new database and you'll have to
restore your most recent backup (...which will give you even more
downtime...:-)...).
Regards
Steen Schlter Persson
Database Administrator / System Administrator
fniles wrote:
> Thank you, that's great !
> Do you by any chance know the T-SQL to COPY the database, instead of BACKU
P
> ? I need to copy.
> Thanks.
> ""Steen Schlter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote in
> message news:OFtrUrYrHHA.484@.TK2MSFTNGP06.phx.gbl...
>|||Thank you very much. That's very helpful.
If I use BACKUP/RESTORE, when restoring, is it possible to restore it to a
different database name so that it does not override the original database ?
""Steen Schlter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote in
message news:udQr4VlrHHA.3228@.TK2MSFTNGP03.phx.gbl...[vbcol=seagreen]
> There isn't any COPY command - you'll have to use BACKUP/RESTORE.
> The closet you can get to a COPY command, is to first detach your
> database, copy the files manually and then attach both the original and
> the new database. The commands for this is sp_detach_db and CREATE
> DATABASE FOR ATTACH which you can look up in Books On Line.
> Personally I prefer the BACKUP/RESTORE though. With the BACKUP/RESTORE
> task you'll keep your source database on-line and operational all the
> time. This means that if the backup file is corrupted or damaged so it
> can't be restored, you can just make a new copy. With the detach/attach
> method your source database will be off-line for the time it takes to copy
> the files and until it is being attached again. Theoretically you'll also
> have the risk that something has damaged the files during (or after) the
> detach so they can't be attached again. In that case you've lost both the
> original and the new database and you'll have to restore your most recent
> backup (...which will give you even more downtime...:-)...).
> --
> Regards
> Steen Schlter Persson
> Database Administrator / System Administrator
> fniles wrote:|||> If I use BACKUP/RESTORE, when restoring, is it possible to restore it to a different datab
ase name
> so that it does not override the original database ?
Yes. Make sure you read up on the MOVE option of the RESTORE command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"fniles" <fniles@.pfmail.com> wrote in message news:OH%23xOIprHHA.3896@.TK2MSFTNGP04.phx.gbl..
.
> Thank you very much. That's very helpful.
> If I use BACKUP/RESTORE, when restoring, is it possible to restore it to a
different database name
> so that it does not override the original database ?
> ""Steen Schlter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote in
message
> news:udQr4VlrHHA.3228@.TK2MSFTNGP03.phx.gbl...
>

Copy database question

Is there a way to copy a database on a scheduled basis and name the copy
different names every time ?
For example: database copy for today will be named mydb_061207, database
copy for tomorrow will be named mydb_061307, etc.
Thank you.Craft a RESTORE statement using dynamic sql and a date variable formatted as
112 (see Convert in BOL).
--
TheSQLGuru
President
Indicium Resources, Inc.
"fniles" <fniles@.pfmail.com> wrote in message
news:eRmOKcSrHHA.3248@.TK2MSFTNGP03.phx.gbl...
> Is there a way to copy a database on a scheduled basis and name the copy
> different names every time ?
> For example: database copy for today will be named mydb_061207, database
> copy for tomorrow will be named mydb_061307, etc.
> Thank you.
>|||Thank you.
I am sorry, I am new to 2005. What did you mean by crafting a restore
statement using dynamic sql ?
When I copy the database, in the SQL Server Management STudio I right click
on the database - Tasks - Copy database and use the copy wizard. This is
stored as a job. I would like the job to copy to a different file everyday.
How can I do that ?
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:OZa8uITrHHA.4624@.TK2MSFTNGP06.phx.gbl...
> Craft a RESTORE statement using dynamic sql and a date variable formatted
> as 112 (see Convert in BOL).
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "fniles" <fniles@.pfmail.com> wrote in message
> news:eRmOKcSrHHA.3248@.TK2MSFTNGP03.phx.gbl...
>> Is there a way to copy a database on a scheduled basis and name the copy
>> different names every time ?
>> For example: database copy for today will be named mydb_061207, database
>> copy for tomorrow will be named mydb_061307, etc.
>> Thank you.
>|||Hi,
You'll have to write you own BACKUP script that generates the filename
dynamically based on todays date. Then you will have to schedule this to
run every day.
A very simple example of a script to do this, could be something like:
DECLARE @.Filename VARCHAR(300)
DECLARE @.BackupPath VARCHAR(300)
DECLARE @.Device VARCHAR(600)
SET @.BackupPath = 'C:\MSSQL\BACKUP\'
SET @.FileName = 'YourDatabaseName'+CONVERT(NVARCHAR(16),GETDATE(),112) +
'.BAK'
SET @.Device = @.BackupPath+@.FileName
BACKUP DATABASE YourDataBaseName TO DISK = @.Device
This will backup your database to a file named with the databasename and
todays date in the format YYYYMMDD.It will place the backup in the
Backuppath directory you specify. Please remember that the backup folder
has to exist before you run the script.
The script is just an example to get you started. You can then add on
extra "features" as you get the need.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
fniles wrote:
> Thank you.
> I am sorry, I am new to 2005. What did you mean by crafting a restore
> statement using dynamic sql ?
> When I copy the database, in the SQL Server Management STudio I right click
> on the database - Tasks - Copy database and use the copy wizard. This is
> stored as a job. I would like the job to copy to a different file everyday.
> How can I do that ?
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:OZa8uITrHHA.4624@.TK2MSFTNGP06.phx.gbl...
>> Craft a RESTORE statement using dynamic sql and a date variable formatted
>> as 112 (see Convert in BOL).
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>> "fniles" <fniles@.pfmail.com> wrote in message
>> news:eRmOKcSrHHA.3248@.TK2MSFTNGP03.phx.gbl...
>> Is there a way to copy a database on a scheduled basis and name the copy
>> different names every time ?
>> For example: database copy for today will be named mydb_061207, database
>> copy for tomorrow will be named mydb_061307, etc.
>> Thank you.
>>
>|||Thank you, that's great !
Do you by any chance know the T-SQL to COPY the database, instead of BACKUP
? I need to copy.
Thanks.
""Steen Schlüter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote in
message news:OFtrUrYrHHA.484@.TK2MSFTNGP06.phx.gbl...
> Hi,
> You'll have to write you own BACKUP script that generates the filename
> dynamically based on todays date. Then you will have to schedule this to
> run every day.
> A very simple example of a script to do this, could be something like:
>
> DECLARE @.Filename VARCHAR(300)
> DECLARE @.BackupPath VARCHAR(300)
> DECLARE @.Device VARCHAR(600)
> SET @.BackupPath = 'C:\MSSQL\BACKUP\'
> SET @.FileName = 'YourDatabaseName'+CONVERT(NVARCHAR(16),GETDATE(),112) +
> '.BAK'
> SET @.Device = @.BackupPath+@.FileName
> BACKUP DATABASE YourDataBaseName TO DISK = @.Device
>
> This will backup your database to a file named with the databasename and
> todays date in the format YYYYMMDD.It will place the backup in the
> Backuppath directory you specify. Please remember that the backup folder
> has to exist before you run the script.
> The script is just an example to get you started. You can then add on
> extra "features" as you get the need.
> --
> Regards
> Steen Schlüter Persson
> Database Administrator / System Administrator
>
> fniles wrote:
>> Thank you.
>> I am sorry, I am new to 2005. What did you mean by crafting a restore
>> statement using dynamic sql ?
>> When I copy the database, in the SQL Server Management STudio I right
>> click on the database - Tasks - Copy database and use the copy wizard.
>> This is stored as a job. I would like the job to copy to a different file
>> everyday. How can I do that ?
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:OZa8uITrHHA.4624@.TK2MSFTNGP06.phx.gbl...
>> Craft a RESTORE statement using dynamic sql and a date variable
>> formatted as 112 (see Convert in BOL).
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>> "fniles" <fniles@.pfmail.com> wrote in message
>> news:eRmOKcSrHHA.3248@.TK2MSFTNGP03.phx.gbl...
>> Is there a way to copy a database on a scheduled basis and name the
>> copy different names every time ?
>> For example: database copy for today will be named mydb_061207,
>> database copy for tomorrow will be named mydb_061307, etc.
>> Thank you.
>>|||There isn't any COPY command - you'll have to use BACKUP/RESTORE.
The closet you can get to a COPY command, is to first detach your
database, copy the files manually and then attach both the original and
the new database. The commands for this is sp_detach_db and CREATE
DATABASE FOR ATTACH which you can look up in Books On Line.
Personally I prefer the BACKUP/RESTORE though. With the BACKUP/RESTORE
task you'll keep your source database on-line and operational all the
time. This means that if the backup file is corrupted or damaged so it
can't be restored, you can just make a new copy. With the detach/attach
method your source database will be off-line for the time it takes to
copy the files and until it is being attached again. Theoretically
you'll also have the risk that something has damaged the files during
(or after) the detach so they can't be attached again. In that case
you've lost both the original and the new database and you'll have to
restore your most recent backup (...which will give you even more
downtime...:-)...).
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
fniles wrote:
> Thank you, that's great !
> Do you by any chance know the T-SQL to COPY the database, instead of BACKUP
> ? I need to copy.
> Thanks.
> ""Steen Schlüter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote in
> message news:OFtrUrYrHHA.484@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> You'll have to write you own BACKUP script that generates the filename
>> dynamically based on todays date. Then you will have to schedule this to
>> run every day.
>> A very simple example of a script to do this, could be something like:
>>
>> DECLARE @.Filename VARCHAR(300)
>> DECLARE @.BackupPath VARCHAR(300)
>> DECLARE @.Device VARCHAR(600)
>> SET @.BackupPath = 'C:\MSSQL\BACKUP\'
>> SET @.FileName = 'YourDatabaseName'+CONVERT(NVARCHAR(16),GETDATE(),112) +
>> '.BAK'
>> SET @.Device = @.BackupPath+@.FileName
>> BACKUP DATABASE YourDataBaseName TO DISK = @.Device
>>
>> This will backup your database to a file named with the databasename and
>> todays date in the format YYYYMMDD.It will place the backup in the
>> Backuppath directory you specify. Please remember that the backup folder
>> has to exist before you run the script.
>> The script is just an example to get you started. You can then add on
>> extra "features" as you get the need.
>> --
>> Regards
>> Steen Schlüter Persson
>> Database Administrator / System Administrator
>>
>> fniles wrote:
>> Thank you.
>> I am sorry, I am new to 2005. What did you mean by crafting a restore
>> statement using dynamic sql ?
>> When I copy the database, in the SQL Server Management STudio I right
>> click on the database - Tasks - Copy database and use the copy wizard.
>> This is stored as a job. I would like the job to copy to a different file
>> everyday. How can I do that ?
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:OZa8uITrHHA.4624@.TK2MSFTNGP06.phx.gbl...
>> Craft a RESTORE statement using dynamic sql and a date variable
>> formatted as 112 (see Convert in BOL).
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>> "fniles" <fniles@.pfmail.com> wrote in message
>> news:eRmOKcSrHHA.3248@.TK2MSFTNGP03.phx.gbl...
>> Is there a way to copy a database on a scheduled basis and name the
>> copy different names every time ?
>> For example: database copy for today will be named mydb_061207,
>> database copy for tomorrow will be named mydb_061307, etc.
>> Thank you.
>|||Thank you very much. That's very helpful.
If I use BACKUP/RESTORE, when restoring, is it possible to restore it to a
different database name so that it does not override the original database ?
""Steen Schlüter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote in
message news:udQr4VlrHHA.3228@.TK2MSFTNGP03.phx.gbl...
> There isn't any COPY command - you'll have to use BACKUP/RESTORE.
> The closet you can get to a COPY command, is to first detach your
> database, copy the files manually and then attach both the original and
> the new database. The commands for this is sp_detach_db and CREATE
> DATABASE FOR ATTACH which you can look up in Books On Line.
> Personally I prefer the BACKUP/RESTORE though. With the BACKUP/RESTORE
> task you'll keep your source database on-line and operational all the
> time. This means that if the backup file is corrupted or damaged so it
> can't be restored, you can just make a new copy. With the detach/attach
> method your source database will be off-line for the time it takes to copy
> the files and until it is being attached again. Theoretically you'll also
> have the risk that something has damaged the files during (or after) the
> detach so they can't be attached again. In that case you've lost both the
> original and the new database and you'll have to restore your most recent
> backup (...which will give you even more downtime...:-)...).
> --
> Regards
> Steen Schlüter Persson
> Database Administrator / System Administrator
> fniles wrote:
>> Thank you, that's great !
>> Do you by any chance know the T-SQL to COPY the database, instead of
>> BACKUP ? I need to copy.
>> Thanks.
>> ""Steen Schlüter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote
>> in message news:OFtrUrYrHHA.484@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> You'll have to write you own BACKUP script that generates the filename
>> dynamically based on todays date. Then you will have to schedule this to
>> run every day.
>> A very simple example of a script to do this, could be something like:
>>
>> DECLARE @.Filename VARCHAR(300)
>> DECLARE @.BackupPath VARCHAR(300)
>> DECLARE @.Device VARCHAR(600)
>> SET @.BackupPath = 'C:\MSSQL\BACKUP\'
>> SET @.FileName = 'YourDatabaseName'+CONVERT(NVARCHAR(16),GETDATE(),112) +
>> '.BAK'
>> SET @.Device = @.BackupPath+@.FileName
>> BACKUP DATABASE YourDataBaseName TO DISK = @.Device
>>
>> This will backup your database to a file named with the databasename and
>> todays date in the format YYYYMMDD.It will place the backup in the
>> Backuppath directory you specify. Please remember that the backup folder
>> has to exist before you run the script.
>> The script is just an example to get you started. You can then add on
>> extra "features" as you get the need.
>> --
>> Regards
>> Steen Schlüter Persson
>> Database Administrator / System Administrator
>>
>> fniles wrote:
>> Thank you.
>> I am sorry, I am new to 2005. What did you mean by crafting a restore
>> statement using dynamic sql ?
>> When I copy the database, in the SQL Server Management STudio I right
>> click on the database - Tasks - Copy database and use the copy wizard.
>> This is stored as a job. I would like the job to copy to a different
>> file everyday. How can I do that ?
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:OZa8uITrHHA.4624@.TK2MSFTNGP06.phx.gbl...
>> Craft a RESTORE statement using dynamic sql and a date variable
>> formatted as 112 (see Convert in BOL).
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>> "fniles" <fniles@.pfmail.com> wrote in message
>> news:eRmOKcSrHHA.3248@.TK2MSFTNGP03.phx.gbl...
>> Is there a way to copy a database on a scheduled basis and name the
>> copy different names every time ?
>> For example: database copy for today will be named mydb_061207,
>> database copy for tomorrow will be named mydb_061307, etc.
>> Thank you.
>>|||> If I use BACKUP/RESTORE, when restoring, is it possible to restore it to a different database name
> so that it does not override the original database ?
Yes. Make sure you read up on the MOVE option of the RESTORE command.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"fniles" <fniles@.pfmail.com> wrote in message news:OH%23xOIprHHA.3896@.TK2MSFTNGP04.phx.gbl...
> Thank you very much. That's very helpful.
> If I use BACKUP/RESTORE, when restoring, is it possible to restore it to a different database name
> so that it does not override the original database ?
> ""Steen Schlüter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote in message
> news:udQr4VlrHHA.3228@.TK2MSFTNGP03.phx.gbl...
>> There isn't any COPY command - you'll have to use BACKUP/RESTORE.
>> The closet you can get to a COPY command, is to first detach your database, copy the files
>> manually and then attach both the original and the new database. The commands for this is
>> sp_detach_db and CREATE DATABASE FOR ATTACH which you can look up in Books On Line.
>> Personally I prefer the BACKUP/RESTORE though. With the BACKUP/RESTORE task you'll keep your
>> source database on-line and operational all the time. This means that if the backup file is
>> corrupted or damaged so it can't be restored, you can just make a new copy. With the
>> detach/attach method your source database will be off-line for the time it takes to copy the
>> files and until it is being attached again. Theoretically you'll also have the risk that
>> something has damaged the files during (or after) the detach so they can't be attached again. In
>> that case you've lost both the original and the new database and you'll have to restore your most
>> recent backup (...which will give you even more downtime...:-)...).
>> --
>> Regards
>> Steen Schlüter Persson
>> Database Administrator / System Administrator
>> fniles wrote:
>> Thank you, that's great !
>> Do you by any chance know the T-SQL to COPY the database, instead of BACKUP ? I need to copy.
>> Thanks.
>> ""Steen Schlüter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote in message
>> news:OFtrUrYrHHA.484@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> You'll have to write you own BACKUP script that generates the filename dynamically based on
>> todays date. Then you will have to schedule this to run every day.
>> A very simple example of a script to do this, could be something like:
>>
>> DECLARE @.Filename VARCHAR(300)
>> DECLARE @.BackupPath VARCHAR(300)
>> DECLARE @.Device VARCHAR(600)
>> SET @.BackupPath = 'C:\MSSQL\BACKUP\'
>> SET @.FileName = 'YourDatabaseName'+CONVERT(NVARCHAR(16),GETDATE(),112) + '.BAK'
>> SET @.Device = @.BackupPath+@.FileName
>> BACKUP DATABASE YourDataBaseName TO DISK = @.Device
>>
>> This will backup your database to a file named with the databasename and todays date in the
>> format YYYYMMDD.It will place the backup in the Backuppath directory you specify. Please
>> remember that the backup folder has to exist before you run the script.
>> The script is just an example to get you started. You can then add on extra "features" as you
>> get the need.
>> --
>> Regards
>> Steen Schlüter Persson
>> Database Administrator / System Administrator
>>
>> fniles wrote:
>> Thank you.
>> I am sorry, I am new to 2005. What did you mean by crafting a restore statement using dynamic
>> sql ?
>> When I copy the database, in the SQL Server Management STudio I right click on the database -
>> Tasks - Copy database and use the copy wizard. This is stored as a job. I would like the job
>> to copy to a different file everyday. How can I do that ?
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:OZa8uITrHHA.4624@.TK2MSFTNGP06.phx.gbl...
>> Craft a RESTORE statement using dynamic sql and a date variable formatted as 112 (see Convert
>> in BOL).
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>> "fniles" <fniles@.pfmail.com> wrote in message news:eRmOKcSrHHA.3248@.TK2MSFTNGP03.phx.gbl...
>>> Is there a way to copy a database on a scheduled basis and name the copy different names
>>> every time ?
>>> For example: database copy for today will be named mydb_061207, database copy for tomorrow
>>> will be named mydb_061307, etc.
>>>
>>> Thank you.
>>>
>

Cool Query Analyzer Trick

In Query Analyzer you can save a lot of time by using this trick instead of
typing all the column names of a table
Hit F8, this will open Object Browser
Navigate to DatabaseName/TableName/Columns
Click on the column folder and drag the column folder into the Code Window
Upon release you will see that all the column names are in the Code Window
I work with people who are certified, have 10 years experience and none of
them knew this trick
Also if you know of any other not well known QA tricks let me know and I
will update my blog
http://sqlservercode.blogspot.com/
Here are some other nice shortcuts (at least in SQL2k and below)
Ctrl-R
Ctrl-E
F6
Highlight some text and hit Ctrl-U or Ctrl-L
Highlight some text and hit Shift+Ctrl+C
now try Shift+Ctrl+R
Keith
"SQL" <SQL@.discussions.microsoft.com> wrote in message
news:CF26A028-8083-49E0-8CEE-8858A65D8C3F@.microsoft.com...
> In Query Analyzer you can save a lot of time by using this trick instead
> of
> typing all the column names of a table
> Hit F8, this will open Object Browser
> Navigate to DatabaseName/TableName/Columns
> Click on the column folder and drag the column folder into the Code Window
> Upon release you will see that all the column names are in the Code Window
> I work with people who are certified, have 10 years experience and none of
> them knew this trick
> Also if you know of any other not well known QA tricks let me know and I
> will update my blog
> http://sqlservercode.blogspot.com/
>
|||On Thu, 22 Sep 2005 13:28:03 -0500, Keith Kratochvil wrote:

>Here are some other nice shortcuts (at least in SQL2k and below)
>Ctrl-R
>Ctrl-E
>F6
>Highlight some text and hit Ctrl-U or Ctrl-L
Hi Keith,
I assume that you meeant Ctrl-Shift-U and Ctrl-Shift-L?
(Ctrl-U is change database and Ctrl-L is display estimated execution
plan).

>Highlight some text and hit Shift+Ctrl+C
>now try Shift+Ctrl+R
And to get to know all keyboard shortcuts, study the menu's. All
shortcuts are listed in the menu's.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||You can also right click on the table and generate a Select, insert , Update
or Delete statement as well.
Andrew J. Kelly SQL MVP
"SQL" <SQL@.discussions.microsoft.com> wrote in message
news:CF26A028-8083-49E0-8CEE-8858A65D8C3F@.microsoft.com...
> In Query Analyzer you can save a lot of time by using this trick instead
> of
> typing all the column names of a table
> Hit F8, this will open Object Browser
> Navigate to DatabaseName/TableName/Columns
> Click on the column folder and drag the column folder into the Code Window
> Upon release you will see that all the column names are in the Code Window
> I work with people who are certified, have 10 years experience and none of
> them knew this trick
> Also if you know of any other not well known QA tricks let me know and I
> will update my blog
> http://sqlservercode.blogspot.com/
>
|||As a follow on to Andrews tip, after generating an insert, delete or
update statement, press Ctrl-Shift-M...
Jacko

Converting varchar to DateTime

Hi,

I wanted to convert the varchar to date time and here is what i am doing

DECLARE @.dt VARCHAR(20)

SET @.dt = '20070111' -- YYYYMMDD format

select CONVERT(datetime, @.dt, 120)

This works perfectly fine and the result would be- 2007-01-11 00:00:00.000

But if i changed my datetime format from YYYYMMDD to YYYYMMDDHHMM then this is failing and throwing

"Conversion failed when converting datetime from character string."

Can any one please let me know how do we achieve this?

~Mohan

YYYYMMDDHHMM is not recognized as a valid datetime string. For example, YYYMMDD HH:MM works.

|||

This is the Convert sintax:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

The first parameter data_type is the required convertion type, of course including the length if required. The second parameter is the expression to convert, and to endding the last parameter is used to define the style in that you are passing the "expression" parameter.

In your code, the style parameter says 120 that corresponds to a ODBC Canonical format in this format: yyyy-mm-dd hh:mis.

Then, ?Does because a string with the format yyyymmdd can be converted to string?:

The YYYYMMDD is widely recognized ODBC String Format that can be used to convert a string to a SQL Server DateTime format. When you use this format, the convert function ignores the last parameter, because it's a standard format. Instead, YYYMMDDHHMM is not a SQL Server recognized format, by this you can not use this.

I recommend to you, to pass strings in the yyyy-mm-dd hh:mis format to be recognized by the CONVERT or CAST functions in SQL server.

Converting unix time (PLEASE HELP!)

Oh please put me out of my misery and help me … I am pulling dates from my database which are in unix time. This means the time is displayed as seconds since 01/01/1970. I am trying to convert this to a date so I can create parameters for my report.

I have tried using convert and dateadd but nothing seems to work.

Any ideas?

Have you triedthis

Converting to datetime...

Hello..
I am having a difficult time trying to get SQL Server to convert the
following date:
22-08-2004 00:00:00
I have tried to convert and cast and I get the following error message:
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value.
Any help would be most appreciative.
Thank you,
Brett
P.S.
I am using SQL Server 2000How are you doing this convert ? This works for me:
Select convert(varchar(20),'22-08-2004 00:00:00',102)
Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Brett Davis" <bdavis123@.cox.net> schrieb im Newsbeitrag
news:eHEz6r1SFHA.2432@.TK2MSFTNGP12.phx.gbl...
> Hello..
> I am having a difficult time trying to get SQL Server to convert the
> following date:
> 22-08-2004 00:00:00
> I have tried to convert and cast and I get the following error message:
> Server: Msg 242, Level 16, State 3, Line 1
> The conversion of a char data type to a datetime data type resulted in
> an out-of-range datetime value.
> Any help would be most appreciative.
> Thank you,
> Brett
> P.S.
> I am using SQL Server 2000
>|||You need to tell SQL Server more about the pattern used for the datetime. Tr
y
this:
Select Convert(DateTime, '22-08-2004 00:00:00', 105)
Without specifying the date format, I believe that SQL uses the format from
the
database's collation or perhaps the server's regional settings.
HTH
Thomas
"Brett Davis" <bdavis123@.cox.net> wrote in message
news:eHEz6r1SFHA.2432@.TK2MSFTNGP12.phx.gbl...
> Hello..
> I am having a difficult time trying to get SQL Server to convert the follo
wing
> date:
> 22-08-2004 00:00:00
> I have tried to convert and cast and I get the following error message:
> Server: Msg 242, Level 16, State 3, Line 1
> The conversion of a char data type to a datetime data type resulted in
an
> out-of-range datetime value.
> Any help would be most appreciative.
> Thank you,
> Brett
> P.S.
> I am using SQL Server 2000
>|||Use styles 112 or 126. See CONVERT in BOL.
Example:
select cast('20040822' as datetime)
select cast('2004-08-22T00:00:00.000' as datetime)
go
AMB
"Brett Davis" wrote:

> Hello..
> I am having a difficult time trying to get SQL Server to convert the
> following date:
> 22-08-2004 00:00:00
> I have tried to convert and cast and I get the following error message:
> Server: Msg 242, Level 16, State 3, Line 1
> The conversion of a char data type to a datetime data type resulted in
> an out-of-range datetime value.
> Any help would be most appreciative.
> Thank you,
> Brett
> P.S.
> I am using SQL Server 2000
>
>|||Thomas,
You use the style parameter when converting from datetime to varchar / char
and not the opposite. All these statements will give same result.
select convert(datetime, '20050427', 105)
select convert(datetime, '20050427', 112)
select convert(datetime, '20050427', 126)
select convert(datetime, '20050427')
AMB
"Thomas" wrote:

> You need to tell SQL Server more about the pattern used for the datetime.
Try
> this:
> Select Convert(DateTime, '22-08-2004 00:00:00', 105)
> Without specifying the date format, I believe that SQL uses the format fro
m the
> database's collation or perhaps the server's regional settings.
>
> HTH
>
> Thomas
>
> "Brett Davis" <bdavis123@.cox.net> wrote in message
> news:eHEz6r1SFHA.2432@.TK2MSFTNGP12.phx.gbl...
>
>|||I think we might be saying the same thing. By passing the style parameter, y
ou
are giving SQL information about the format of the string.

>All these statements will give same result.
> select convert(datetime, '20050427', 105)
> select convert(datetime, '20050427', 112)
> select convert(datetime, '20050427', 126)
> select convert(datetime, '20050427')
But these do not:
1. select convert(datetime, '22-08-2004 00:00:00', 105)
2. select convert(datetime, '22-08-2004 00:00:00', 112)
3. select convert(datetime, '22-08-2004 00:00:00', 126)
4. select convert(datetime, '22-08-2004 00:00:00',)
Only the first one successfully parses the string into a datetime. The other
s
fail with a conversion error because the system thinks that the first digits
are
the month instead of the day.
I'll grant you that passing the ISO format (yyyymmdd) would be the best way
to
avoid all of these problems.
Thomas|||You are right.
AMB
"Thomas" wrote:

> I think we might be saying the same thing. By passing the style parameter,
you
> are giving SQL information about the format of the string.
>
> But these do not:
> 1. select convert(datetime, '22-08-2004 00:00:00', 105)
> 2. select convert(datetime, '22-08-2004 00:00:00', 112)
> 3. select convert(datetime, '22-08-2004 00:00:00', 126)
> 4. select convert(datetime, '22-08-2004 00:00:00',)
> Only the first one successfully parses the string into a datetime. The oth
ers
> fail with a conversion error because the system thinks that the first digi
ts are
> the month instead of the day.
> I'll grant you that passing the ISO format (yyyymmdd) would be the best wa
y to
> avoid all of these problems.
>
> Thomas
>
>

Converting time to day

Hi All,
I am pretty new to sql server. I have a table with a column which has
data in form of time,e.g.:
2006-10-15 08:06:29.000
I want to select data from the table using only date part of the time
(I want to group the data by day)
Can you please help me?
Thanks.You can use the Floor() function.
http://msdn2.microsoft.com/en-us/library/ms178531.aspx
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
<jack.smith.sam@.gmail.com> wrote:
| Hi All,
|
| I am pretty new to sql server. I have a table with a column which has
| data in form of time,e.g.:
| 2006-10-15 08:06:29.000
|
| I want to select data from the table using only date part of the time
| (I want to group the data by day)
| Can you please help me?
|
| Thanks.
||||You can use CONVERT to change datetime formats. To return or
group by just the date, something along the lines of:
convert(char(10), YourDateColumn, 101)
-Sue
On 15 Oct 2006 17:55:47 -0700, jack.smith.sam@.gmail.com
wrote:

>Hi All,
>I am pretty new to sql server. I have a table with a column which has
>data in form of time,e.g.:
>2006-10-15 08:06:29.000
>I want to select data from the table using only date part of the time
>(I want to group the data by day)
>Can you please help me?
>Thanks.|||One method is with DATEADD/DATEDIFF:
SELECT DATEADD(day,0,DATEDIFF(day,0,('20061015 08:06:29.000')))
Hope this helps.
Dan Guzman
SQL Server MVP
<jack.smith.sam@.gmail.com> wrote in message
news:1160960147.761712.173580@.i42g2000cwa.googlegroups.com...
> Hi All,
> I am pretty new to sql server. I have a table with a column which has
> data in form of time,e.g.:
> 2006-10-15 08:06:29.000
> I want to select data from the table using only date part of the time
> (I want to group the data by day)
> Can you please help me?
> Thanks.
>

Converting time to day

Hi All,
I am pretty new to sql server. I have a table with a column which has
data in form of time,e.g.:
2006-10-15 08:06:29.000
I want to select data from the table using only date part of the time
(I want to group the data by day)
Can you please help me?
Thanks.
You can use the Floor() function.
http://msdn2.microsoft.com/en-us/library/ms178531.aspx
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
<jack.smith.sam@.gmail.com> wrote:
| Hi All,
|
| I am pretty new to sql server. I have a table with a column which has
| data in form of time,e.g.:
| 2006-10-15 08:06:29.000
|
| I want to select data from the table using only date part of the time
| (I want to group the data by day)
| Can you please help me?
|
| Thanks.
|
|||You can use CONVERT to change datetime formats. To return or
group by just the date, something along the lines of:
convert(char(10), YourDateColumn, 101)
-Sue
On 15 Oct 2006 17:55:47 -0700, jack.smith.sam@.gmail.com
wrote:

>Hi All,
>I am pretty new to sql server. I have a table with a column which has
>data in form of time,e.g.:
>2006-10-15 08:06:29.000
>I want to select data from the table using only date part of the time
>(I want to group the data by day)
>Can you please help me?
>Thanks.
|||One method is with DATEADD/DATEDIFF:
SELECT DATEADD(day,0,DATEDIFF(day,0,('20061015 08:06:29.000')))
Hope this helps.
Dan Guzman
SQL Server MVP
<jack.smith.sam@.gmail.com> wrote in message
news:1160960147.761712.173580@.i42g2000cwa.googlegr oups.com...
> Hi All,
> I am pretty new to sql server. I have a table with a column which has
> data in form of time,e.g.:
> 2006-10-15 08:06:29.000
> I want to select data from the table using only date part of the time
> (I want to group the data by day)
> Can you please help me?
> Thanks.
>

Converting time to day

Hi All,
I am pretty new to sql server. I have a table with a column which has
data in form of time,e.g.:
2006-10-15 08:06:29.000
I want to select data from the table using only date part of the time
(I want to group the data by day)
Can you please help me?
Thanks.You can use the Floor() function.
http://msdn2.microsoft.com/en-us/library/ms178531.aspx
--
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
<jack.smith.sam@.gmail.com> wrote:
| Hi All,
|
| I am pretty new to sql server. I have a table with a column which has
| data in form of time,e.g.:
| 2006-10-15 08:06:29.000
|
| I want to select data from the table using only date part of the time
| (I want to group the data by day)
| Can you please help me?
|
| Thanks.
||||You can use CONVERT to change datetime formats. To return or
group by just the date, something along the lines of:
convert(char(10), YourDateColumn, 101)
-Sue
On 15 Oct 2006 17:55:47 -0700, jack.smith.sam@.gmail.com
wrote:
>Hi All,
>I am pretty new to sql server. I have a table with a column which has
>data in form of time,e.g.:
>2006-10-15 08:06:29.000
>I want to select data from the table using only date part of the time
>(I want to group the data by day)
>Can you please help me?
>Thanks.|||One method is with DATEADD/DATEDIFF:
SELECT DATEADD(day,0,DATEDIFF(day,0,('20061015 08:06:29.000')))
--
Hope this helps.
Dan Guzman
SQL Server MVP
<jack.smith.sam@.gmail.com> wrote in message
news:1160960147.761712.173580@.i42g2000cwa.googlegroups.com...
> Hi All,
> I am pretty new to sql server. I have a table with a column which has
> data in form of time,e.g.:
> 2006-10-15 08:06:29.000
> I want to select data from the table using only date part of the time
> (I want to group the data by day)
> Can you please help me?
> Thanks.
>