Use:
declare @date datetime
set @date = getdate()
exec ('SELECT * INTO [table_Backup_'+cast(@date as varchar(100))+'] FROM table' )
First, you need to declare @date as datetime, not timestamp. (Look up timestamp in Books Online. It's misnamed and MicroSoft is recommending not using it any more, but it has nothing to do with dates and times.)
Second, you need to cast/convert it to varchar instead of datetime in the exec statement.
If you want to avoid those steps, declare @date as varchar in the first place, then set it to getdate. If you do that, you get the default format. If you declare it as datetime, then convert it later, you can set the format you want. (See "CAST and CONVERT" in Books Online, it will tell you the format choices you have.)
You also will need to add braces "[" and "]" around the table name, or the exec command will give you an error. Table names with spaces in them (date and time have spaces), or special characters (pretty much, punctuation other than "_"), need to have square-braces around them.
You can also use smalldatetime, instead of datetime, for the @date, which will record the minutes, but not the seconds and fractions of a second. If that works, use it.
(I know that's a bit much, but it all matters.)
Let me know if you have any further questions on this.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
