Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Create view with date?

Author  Topic 

taunt
Posting Yak Master

128 Posts

Posted - 2013-02-21 : 15:14:37
Hello I'm trying to create a view that would auto named by the date it was made on. I tried this:

CREATE VIEW [GETDATE()] AS SELECT...

but that just made a view named "GETDATE()". Is there a way to do this?

Thanks

sanjnep
Posting Yak Master

191 Posts

Posted - 2013-02-21 : 15:46:20
Not clear...

some thing like this

CREATE VIEW [GETDATE()]
AS SELECT GETDATE() AS Today
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-02-24 : 08:18:46
You need to use dynamic sql.But why do you want to name that way?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-24 : 23:43:35
wow...view with name as current date..thats interesting
Can i ask reason for this?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

taunt
Posting Yak Master

128 Posts

Posted - 2013-02-25 : 12:26:31
Well I was trying to do a sale report for the buyer that would list it's by time between date received and last sold date. The issue was in Coldfusion it has issues with the coding that I was using (in sql it would add fine in cf it would be off). So I thought to do a create list view in Coldfusion, but I need it to create a view named with the vender number and date made. Then it wouldn't have an issue if multiple ones were made in a day. For instance it would make it to look like view would be titled 092252013 for one vendor and another one would be 2452252013.


quote:
Originally posted by visakh16

wow...view with name as current date..thats interesting
Can i ask reason for this?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/



Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-25 : 12:30:55
I am not familiar with Coldfusion, so this may or may not be applicable. In SQL server, the recommended way would be to query the table (or create a view and query it) that has two additional columns - 1. Date, 2. Vendor ID. Once you have that, you would query against that view/table using a where clause such as this:
.....
WHERE
DateColumn = '20130225'
AND VendorIDColumn = 245
Creating a view for each vendor and each date is not scalable and not a recommended practice, especially so for the dates.
Go to Top of Page

taunt
Posting Yak Master

128 Posts

Posted - 2013-02-25 : 13:00:16
Yep, but the original question was can sql make a view with todays date. I tried the getdate() and that makes a view titled "getdate()" not with today's date.

quote:
Originally posted by James K

I am not familiar with Coldfusion, so this may or may not be applicable. In SQL server, the recommended way would be to query the table (or create a view and query it) that has two additional columns - 1. Date, 2. Vendor ID. Once you have that, you would query against that view/table using a where clause such as this:
.....
WHERE
DateColumn = '20130225'
AND VendorIDColumn = 245
Creating a view for each vendor and each date is not scalable and not a recommended practice, especially so for the dates.

Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-25 : 13:08:16
You can do that, like this:
DECLARE @sql NVARCHAR(4000);
SET @sql = 'create view ' + QUOTENAME(CONVERT(CHAR(8),GETDATE(),112)) + '
as SELECT col1, col2 FROM YourTable';
EXEC (@sql);
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-26 : 01:13:17
quote:
Originally posted by taunt

Well I was trying to do a sale report for the buyer that would list it's by time between date received and last sold date. The issue was in Coldfusion it has issues with the coding that I was using (in sql it would add fine in cf it would be off). So I thought to do a create list view in Coldfusion, but I need it to create a view named with the vender number and date made. Then it wouldn't have an issue if multiple ones were made in a day. For instance it would make it to look like view would be titled 092252013 for one vendor and another one would be 2452252013.


quote:
Originally posted by visakh16

wow...view with name as current date..thats interesting
Can i ask reason for this?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/






Doesnt sound like best way to do this. Why cant all the data be in same table with vendornumber being added as a field to indicate which vendors data it represent and a date field to indicate date of data?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

taunt
Posting Yak Master

128 Posts

Posted - 2013-02-26 : 12:15:19
OK I tried this:
GO
DECLARE @sql NVARCHAR(4000);
SET @sql = 'create view ' + QUOTENAME(CONVERT(CHAR(8),GETDATE(),112)) + '
as SELECT DATEDIFF(d, Items.ReceiptDate, Items.Last_Sold) / 365 AS Years, DATEDIFF(d, Items.ReceiptDate, Items.Last_Sold) % 365 / 30 AS Months, DATEDIFF(d, Items.ReceiptDate, Items.Last_Sold)
% 365 % 30 AS Days, Items.UPC, Items.buyqty, Items.Attribute1, Items.ProductName, Items.Attribute2, Items.LastOrdDate, Items.ReceiptDate, Items.LAST_SOLD, Items.VendorID,
Items.SKU AS Expr1, Items.StockQty, Items.UsedQty, RTotals.NSTTL
FROM Products INNER JOIN
RTotals ON Items.PID = RTotals.PID
WHERE (Items.VendorID = '245') AND (Items.buyqty > 0);
EXEC (@sql);
go


and get this:

Msg 102, Level 15, State 1, Line 8
Incorrect syntax near '245'.
Msg 105, Level 15, State 1, Line 8
Unclosed quotation mark after the character string ') AND (Products.buyqty > 0);
EXEC (@sql);
go
'.


Let me know what's the issue.

Thanks

quote:
Originally posted by James K

You can do that, like this:
DECLARE @sql NVARCHAR(4000);
SET @sql = 'create view ' + QUOTENAME(CONVERT(CHAR(8),GETDATE(),112)) + '
as SELECT col1, col2 FROM YourTable';
EXEC (@sql);



Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-26 : 12:32:58
[code]DECLARE @sql NVARCHAR(4000);
SET @sql = 'create view ' + QUOTENAME(CONVERT(CHAR(8), GETDATE(), 112)) +
'
as SELECT DATEDIFF(d, Items.ReceiptDate, Items.Last_Sold) / 365 AS Years, DATEDIFF(d, Items.ReceiptDate, Items.Last_Sold) % 365 / 30 AS Months, DATEDIFF(d, Items.ReceiptDate, Items.Last_Sold)
% 365 % 30 AS Days, Items.UPC, Items.buyqty, Items.Attribute1, Items.ProductName, Items.Attribute2, Items.LastOrdDate, Items.ReceiptDate, Items.LAST_SOLD, Items.VendorID,
Items.SKU AS Expr1, Items.StockQty, Items.UsedQty, RTotals.NSTTL
FROM Products INNER JOIN
RTotals ON Items.PID = RTotals.PID
WHERE (Items.VendorID = ''245'') AND (Items.buyqty > 0);'
EXEC (@sql);
GO[/code]
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-26 : 12:33:05
[code]DECLARE @sql NVARCHAR(4000);
SET @sql = 'create view ' + QUOTENAME(CONVERT(CHAR(8), GETDATE(), 112)) +
'
as SELECT DATEDIFF(d, Items.ReceiptDate, Items.Last_Sold) / 365 AS Years, DATEDIFF(d, Items.ReceiptDate, Items.Last_Sold) % 365 / 30 AS Months, DATEDIFF(d, Items.ReceiptDate, Items.Last_Sold)
% 365 % 30 AS Days, Items.UPC, Items.buyqty, Items.Attribute1, Items.ProductName, Items.Attribute2, Items.LastOrdDate, Items.ReceiptDate, Items.LAST_SOLD, Items.VendorID,
Items.SKU AS Expr1, Items.StockQty, Items.UsedQty, RTotals.NSTTL
FROM Products INNER JOIN
RTotals ON Items.PID = RTotals.PID
WHERE (Items.VendorID = ''245'') AND (Items.buyqty > 0);'
EXEC (@sql);
GO[/code]
Go to Top of Page

taunt
Posting Yak Master

128 Posts

Posted - 2013-02-26 : 12:50:23
Beautiful! That worked thanks a lot.

quote:
Originally posted by James K

DECLARE @sql NVARCHAR(4000);
SET @sql = 'create view ' + QUOTENAME(CONVERT(CHAR(8), GETDATE(), 112)) +
'
as SELECT DATEDIFF(d, Items.ReceiptDate, Items.Last_Sold) / 365 AS Years, DATEDIFF(d, Items.ReceiptDate, Items.Last_Sold) % 365 / 30 AS Months, DATEDIFF(d, Items.ReceiptDate, Items.Last_Sold)
% 365 % 30 AS Days, Items.UPC, Items.buyqty, Items.Attribute1, Items.ProductName, Items.Attribute2, Items.LastOrdDate, Items.ReceiptDate, Items.LAST_SOLD, Items.VendorID,
Items.SKU AS Expr1, Items.StockQty, Items.UsedQty, RTotals.NSTTL
FROM Products INNER JOIN
RTotals ON Items.PID = RTotals.PID
WHERE (Items.VendorID = ''245'') AND (Items.buyqty > 0);'
EXEC (@sql);
GO


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-26 : 23:00:38
see

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/02/19/understanding-single-quotes.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

lucyming
Starting Member

4 Posts

Posted - 2013-04-30 : 03:03:08
i need to contact with you regular touch.


__________________________________________________
Diablo 3 Gold;Aion Kinah;Diablo 3 Gold Kaufen;Guild Wars 2 Gold
Go to Top of Page

chrisjacob
Starting Member

2 Posts

Posted - 2014-01-17 : 01:06:26
Thanks Taunt, for sharing this thread. I was also trying to get the date viewed in my software. I guess what the solution you got really worked for me too. I am very thankful to you guys. Keep sharing such useful info.



Thanks
Chris


http://www.outlookaddressbooks.com
Go to Top of Page
   

- Advertisement -