SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Create view with date?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

taunt
Posting Yak Master

116 Posts

Posted - 02/21/2013 :  15:14:37  Show Profile  Reply with Quote
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

USA
191 Posts

Posted - 02/21/2013 :  15:46:20  Show Profile  Visit sanjnep's Homepage  Reply with Quote
Not clear...

some thing like this

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

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 02/24/2013 :  08:18:46  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

India
52323 Posts

Posted - 02/24/2013 :  23:43:35  Show Profile  Reply with Quote
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

116 Posts

Posted - 02/25/2013 :  12:26:31  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 02/25/2013 :  12:30:55  Show Profile  Reply with Quote
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

116 Posts

Posted - 02/25/2013 :  13:00:16  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 02/25/2013 :  13:08:16  Show Profile  Reply with Quote
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

India
52323 Posts

Posted - 02/26/2013 :  01:13:17  Show Profile  Reply with Quote
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

116 Posts

Posted - 02/26/2013 :  12:15:19  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 02/26/2013 :  12:32:58  Show Profile  Reply with Quote
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

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 02/26/2013 :  12:33:05  Show Profile  Reply with Quote
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

taunt
Posting Yak Master

116 Posts

Posted - 02/26/2013 :  12:50:23  Show Profile  Reply with Quote
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

India
52323 Posts

Posted - 02/26/2013 :  23:00:38  Show Profile  Reply with Quote
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

Austria
4 Posts

Posted - 04/30/2013 :  03:03:08  Show Profile  Reply with Quote
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 - 01/17/2014 :  01:06:26  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000