Create Table with current date as part of the table name

Afternoon all,

Is it possible from within SQL Server Management Studio to create a table based upon an existing table using the current date as part of the table name?

I.E; SELECT * FROM TABLENAME INTO TABLENAMEWITHDATE - if this query was setup as a SSMS Agent Job we could create a daily snapshot of data in this table.

I've tried many times but always get an incorrect syntax message when I try to excecute the query. I'm not sure what syntax I should use to create the tablename with current date included?

Any help would be appreciated.

Thanks,

Chris

Though I am wary of what you are trying to do (a permanent table with a column fro the load date is usually easier to work with,) you could use dynamic SQL:

declare @.tableName varchar(8), @.query nvarchar(1000)

set @.tableName = convert(varchar(8), getdate(),112)

select @.query = 'select name into ' + quotename(@.tableName) + ' from sys.objects'

exec (@.query)

select *
from sys.objects
where name = @.tableName

|||

Thanks, Louis, you've been a great help.

If you ever find yourself lost in Chepstow I'll definately be buying your drinks.

Chris

sql

Create Table with current date as part of the table name

Afternoon all,

Is it possible from within SQL Server Management Studio to create a table based upon an existing table using the current date as part of the table name?

I.E; SELECT * FROM TABLENAME INTO TABLENAMEWITHDATE - if this query was setup as a SSMS Agent Job we could create a daily snapshot of data in this table.

I've tried many times but always get an incorrect syntax message when I try to excecute the query. I'm not sure what syntax I should use to create the tablename with current date included?

Any help would be appreciated.

Thanks,

Chris

Though I am wary of what you are trying to do (a permanent table with a column fro the load date is usually easier to work with,) you could use dynamic SQL:

declare @.tableName varchar(8), @.query nvarchar(1000)

set @.tableName = convert(varchar(8), getdate(),112)

select @.query = 'select name into ' + quotename(@.tableName) + ' from sys.objects'

exec (@.query)

select *
from sys.objects
where name = @.tableName

|||

Thanks, Louis, you've been a great help.

If you ever find yourself lost in Chepstow I'll definately be buying your drinks.

Chris

create report incrementing date on left and results based on that date in subsequent

I would like to create a report in SQL Analyzer. Is there a For Next
construct or equivalent?
declare @.now datetime
set @.now = '2005-09-19 17:57:00.00'
for i = 1 to 60 -- pseudocode
select @.now
select count(Create_DT) from customer where EmailSent_DT > @.now and
EmailSent_IN = 1
select count(Create_DT) from customer where EmailSent_DT > @.now and
EmailSent_IN = 2
next
********** output results *****************
Col1 Col2
Col3
date Count(query result based on date)
Count(query result based on date)
date + 1 day Count(query result based on date + 1)
Count(query result based on date + 1 )
date + 2 day Count(query result based on date + 2)
Count(query result based on date + 2)
date + 3 day Count(query result based on date + 3)
Count(query result based on date + 3)
******************************
thank you - gregThere is a WHILE loop construct in SQL. However, for general application
related tasks, it is seldom needed. If you post your table structures,
sample schema & expected results ( www.aspfaq.com/5006 ) someone here can
perhaps show you how to generate the required resultset without iteration.
One general trick employed in SQL for such requirements is using a table of
sequentially incrementing numbers. You can find several solutions related to
this, if you search the archives of this newsgroup.
Anith|||Try using WHILE with a counter and then increment the counter in the code.
HTH
Jerry
"hazz" <hazz@.sonic_net> wrote in message
news:eMk64gN0FHA.2064@.TK2MSFTNGP09.phx.gbl...
>I would like to create a report in SQL Analyzer. Is there a For Next
>construct or equivalent?
> declare @.now datetime
> set @.now = '2005-09-19 17:57:00.00'
> for i = 1 to 60 -- pseudocode
> select @.now
> select count(Create_DT) from customer where EmailSent_DT > @.now and
> EmailSent_IN = 1
> select count(Create_DT) from customer where EmailSent_DT > @.now and
> EmailSent_IN = 2
> next
> ********** output results *****************
> Col1 Col2 Col3
> date Count(query result based on date) Count(query
> result based on date)
> date + 1 day Count(query result based on date + 1) Count(query
> result based on date + 1 )
> date + 2 day Count(query result based on date + 2) Count(query
> result based on date + 2)
> date + 3 day Count(query result based on date + 3) Count(query
> result based on date + 3)
> ******************************
> thank you - greg
>|||Beautiful. Thank you.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OgT8xoN0FHA.3904@.TK2MSFTNGP15.phx.gbl...
> Try using WHILE with a counter and then increment the counter in the code.
> HTH
> Jerry
> "hazz" <hazz@.sonic_net> wrote in message
> news:eMk64gN0FHA.2064@.TK2MSFTNGP09.phx.gbl...
>

create other index

Hello:
I create a key in one table, the field is uniqueidentifier, but I search
very frequently for one [date] field, and I like to index this field with
datetime datatype to optimize the search, how can I do that?
Best regards,
Owen."Owen" <anibal@.prensa-latina.cu> wrote in message
news:uxQMR9vEGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Hello:
> I create a key in one table, the field is uniqueidentifier, but I search
> very frequently for one [date] field, and I like to index this field with
> datetime datatype to optimize the search, how can I do that?
> Best regards,
> Owen.
>
CREATE INDEX <indexname> ON <TableName> (<column1>, <column2>, ...)
Example:
CREATE INDEX IX_Frogs_BirthDate ON Frogs (BirthDate)
As a side note.. If you are frequently searching on a range of dates using
statements like BETWEEN, then you may find a CLUSTERED index on this date
column to be far more effective. Clustered indexes on GUIDs can be clumsy
at best.
Rick Sawtell
MCT, MCSD, MCDBA|||hi, thanks for answer:
the problem is that I need keep this two index on the same table, the guid
and the dates, but only one can be CLUSTERED, how can optimize this to all
index work faster?
Best regards,
Owen.
"Rick Sawtell" <Quickening@.msn.com> wrote in message
news:eWCHVDwEGHA.644@.TK2MSFTNGP09.phx.gbl...
> "Owen" <anibal@.prensa-latina.cu> wrote in message
> news:uxQMR9vEGHA.1032@.TK2MSFTNGP11.phx.gbl...
with
> CREATE INDEX <indexname> ON <TableName> (<column1>, <column2>, ...)
> Example:
> CREATE INDEX IX_Frogs_BirthDate ON Frogs (BirthDate)
>
> As a side note.. If you are frequently searching on a range of dates
using
> statements like BETWEEN, then you may find a CLUSTERED index on this date
> column to be far more effective. Clustered indexes on GUIDs can be
clumsy
> at best.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Owen as you say only one index can be clustered, but one of the significant
benefits of a clustered index is for querioes based on a range of values
since all those rows will be found near each other (all else being equal).
Now a GUID is *extremely unlikely* (probably nearly safe to say never)
likely to be used in a query like
WHERE guidcol between 'yuckyguidvalue1' and 'yuckyguidvalue2'
So makeing the CI on the date will have the benefit (if you retrieve rows
based on a date range) of being faster than retrieving the same range when
the supporting index is non-clustered. Hence Rick's suggestion that you
consider making the CI on the date column.
To go much further needs an understanding of the type of queries on the
table, and the approx size of it, and is frequently best checked by running
testst on your configuration.
You may have guessed I fall in to the camp of disliking guids for any sort
of identifiers unless there is an absolute cast-iron reason for needing them
(like a distributed db that has to have surrogate keys generated uniquely,
or complex replication) .
Mike John
"Owen" <anibal@.prensa-latina.cu> wrote in message
news:eNcVeIwEGHA.3064@.TK2MSFTNGP10.phx.gbl...
> hi, thanks for answer:
> the problem is that I need keep this two index on the same table, the guid
> and the dates, but only one can be CLUSTERED, how can optimize this to all
> index work faster?
> Best regards,
> Owen.
>
> "Rick Sawtell" <Quickening@.msn.com> wrote in message
> news:eWCHVDwEGHA.644@.TK2MSFTNGP09.phx.gbl...
> with
> using
> clumsy
>|||Just don't forget that the GUID may be an FK where the "=" searches would
return many rows. In that case depending on how many queries against each
column and what columns you fetch we still may consider using the GUID as
the clustered index.
/ Tobias|||True - apologies - i was falling into an assumption that the guid was going
to be unique!
Mike
"Tobias Thernstrm" <ttnospam@.rbam.se> wrote in message
news:O%23qfmNCFGHA.3632@.TK2MSFTNGP10.phx.gbl...
> Just don't forget that the GUID may be an FK where the "=" searches would
> return many rows. In that case depending on how many queries against each
> column and what columns you fetch we still may consider using the GUID as
> the clustered index.
> / Tobias
>

create new table based on union of range data

Hi, I was hoping someone could help me out.
Is it possible for SQL to take a table which has date range data (start and
end date indicating the contract period of the client) and create a new
table which creates a union of 'unionizable' range data for each specific
client. For example (here i am using numbers to indicate date order):
client startdate enddate
A 2 5
A 3 7
A 7 10
A 11 12
B 4 6
B 8 14
B 5 7
C 2 3
C 3 10
C 1 20
The table operation would give (for example {2..5} U {3..7} U {7..10} =
(2..10} in the resultant table. But {4..6} U {8..14} does not have a common
union, so I just leave them as {4..6} and {8..14} in the resultant table:
client startdate enddate
A 2 10
A 11 12
B 4 7
B 8 14
B 15 21
C 1 20
I am unable to determine how to do this. I was thinking to move towards
implementing cursors, but that in itself will be a complex algorithm. Is
there some easier method to use? I was also thinking of cross joining the
initial table with itself on the condition that t1.client = t2.client (yes
this is not a cross join, jut results in an inner join). Then deriving a new
table from this based upon a comparison between t1.startdate , t2.startdate
and t1.enddate, t2.enddate
Would anyone have any insight into this?
any help most appreciated!
thanks!
CathyHi Cathy
You may want to check out Itzik's articles in SQL Server Magazine
http://www.windowsitpro.com/Article...4570/44570.html
You may need to undo the current ranges such as (using your sample data,
plus a few more test cases)
CREATE TABLE #values ( Client char(1), Num int )
INSERT INTO #values ( Client , Num )
SELECT DISTINCT C.[Client], N.[Num]
FROM
( SELECT 'A' AS [client], 2 as [start], 5 as [end]
UNION ALL SELECT 'A', 3, 7
UNION ALL SELECT 'A', 7, 10
UNION ALL SELECT 'A', 11, 12
UNION ALL SELECT 'B', 4, 6
UNION ALL SELECT 'B', 8, 14
UNION ALL SELECT 'B', 5, 7
UNION ALL SELECT 'B', 16, 24
UNION ALL SELECT 'C', 2, 3
UNION ALL SELECT 'C', 3, 10
UNION ALL SELECT 'C', 1, 20
UNION ALL SELECT 'D', 2, 2
) C
JOIN (
SELECT 1 AS Num
UNION SELECT 2
UNION SELECT 3
UNION SELECT 4
UNION SELECT 5
UNION SELECT 6
UNION SELECT 7
UNION SELECT 8
UNION SELECT 9
UNION SELECT 10
UNION SELECT 11
UNION SELECT 12
UNION SELECT 13
UNION SELECT 14
UNION SELECT 15
UNION SELECT 16
UNION SELECT 17
UNION SELECT 18
UNION SELECT 19
UNION SELECT 20
UNION SELECT 21
UNION SELECT 22
UNION SELECT 23
UNION SELECT 24
UNION SELECT 25
UNION SELECT 26
UNION SELECT 27
UNION SELECT 28
UNION SELECT 29
) N ON C.[Start] <= N.Num and C.[end] >= n.num
John
"Cathy Smith" <cs@.cs.com.au> wrote in message
news:%23FPXb08EGHA.2856@.TK2MSFTNGP12.phx.gbl...
> Hi, I was hoping someone could help me out.
> Is it possible for SQL to take a table which has date range data (start
> and end date indicating the contract period of the client) and create a
> new table which creates a union of 'unionizable' range data for each
> specific client. For example (here i am using numbers to indicate date
> order):
> client startdate enddate
> A 2 5
> A 3 7
> A 7 10
> A 11 12
> B 4 6
> B 8 14
> B 5 7
> C 2 3
> C 3 10
> C 1 20
> The table operation would give (for example {2..5} U {3..7} U {7..10} =
> (2..10} in the resultant table. But {4..6} U {8..14} does not have a
> common union, so I just leave them as {4..6} and {8..14} in the resultant
> table:
> client startdate enddate
> A 2 10
> A 11 12
> B 4 7
> B 8 14
> B 15 21
> C 1 20
> I am unable to determine how to do this. I was thinking to move towards
> implementing cursors, but that in itself will be a complex algorithm. Is
> there some easier method to use? I was also thinking of cross joining the
> initial table with itself on the condition that t1.client = t2.client (yes
> this is not a cross join, jut results in an inner join). Then deriving a
> new table from this based upon a comparison between t1.startdate ,
> t2.startdate and t1.enddate, t2.enddate
> Would anyone have any insight into this?
> any help most appreciated!
> thanks!
> Cathy
>|||Cathy Smith (cs@.cs.com.au) writes:
> Is it possible for SQL to take a table which has date range data (start
> and end date indicating the contract period of the client) and create a
> new table which creates a union of 'unionizable' range data for each
> specific client. For example (here i am using numbers to indicate date
> order):
Have a look at
http://groups.google.com/group/comp...48dda4c48fb808b
your problem reminds me of the problem in that thread.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||1) Look up the Rick Snodgrass book at University of Arizona.
2) Look up the use of a Calendar Auxiliary table.
3) Look up SQL FOR SMARTIES for this kind of query using a calendar
table. I have to go to bed now, but the idea is to see what ranges each
calendar dates falls inside of. Make a list of cal_dates by client,
such that there is a missing date before the MIN() and after the MAX()
of the list.
It is a very simple set of joins and you do not need elaborate
subqueries.
--CELKO--
Please post DDL in a human-readable format and not a machine-generated
one. This way people do not have to guess what the keys, constraints,
DRI, datatypes, etc. in your schema are. Sample data is also a good
idea, along with clear specifications.
*** Sent via Developersdex http://www.examnotes.net ***|||-- If you are using SQL Server 2005, you can use
-- recursive CTEs to get the results
create table Contracts(client char(1), startdate int, enddate int)
insert into Contracts(client,startdate,enddate) values ('A', 2 , 5)
insert into Contracts(client,startdate,enddate) values ('A', 3 , 7)
insert into Contracts(client,startdate,enddate) values ('A', 7 , 10)
insert into Contracts(client,startdate,enddate) values ('A', 11 , 12)
insert into Contracts(client,startdate,enddate) values ('B', 4 , 6)
insert into Contracts(client,startdate,enddate) values ('B', 8 , 14)
insert into Contracts(client,startdate,enddate) values ('B', 5 , 7)
insert into Contracts(client,startdate,enddate) values ('C', 2 , 3)
insert into Contracts(client,startdate,enddate) values ('C', 3 , 10)
insert into Contracts(client,startdate,enddate) values ('C', 1 , 20);
with cte_contracts(client,startdate,enddate,m
instartdate,maxenddate) as
(
select A.client,A.startdate,A.enddate,A.startdate,A.enddate
from Contracts A
union all
select A.client,A.startdate,A.enddate,B.startdate,C.enddate
from cte_contracts A
inner join Contracts B on B.client=A.client
and B.enddate >= A.minstartdate and B.startdate <= A.maxenddate
inner join Contracts C on C.client=A.client
and C.enddate >= A.minstartdate and C.startdate <= A.maxenddate
where (B.startdate < A.minstartdate and C.enddate >= A.maxenddate)
or (B.startdate <= A.minstartdate and C.enddate > A.maxenddate)
)
select distinct client,min(minstartdate),max(maxenddate)
from cte_contracts
group by client,startdate,enddate
drop table Contracts|||Cathy,
what about something like this:
-- DROP TABLE #tmp
CREATE TABLE #tmp ( client CHAR(1), startdate INT, enddate INT )
SET NOCOUNT ON
INSERT INTO #tmp VALUES ( 'A', 2, 5 )
INSERT INTO #tmp VALUES ( 'A', 3, 7 )
INSERT INTO #tmp VALUES ( 'A', 7, 10 )
INSERT INTO #tmp VALUES ( 'A', 11, 12 )
INSERT INTO #tmp VALUES ( 'B', 4, 6 )
INSERT INTO #tmp VALUES ( 'B', 8, 14 )
INSERT INTO #tmp VALUES ( 'B', 5, 7 )
INSERT INTO #tmp VALUES ( 'C', 2, 3 )
INSERT INTO #tmp VALUES ( 'C', 3, 10 )
INSERT INTO #tmp VALUES ( 'C', 1, 20 )
SET NOCOUNT OFF
-- SELECT * FROM #tmp
SELECT t1.client, MIN( t1.startdate ), MAX( t1.enddate )
FROM #tmp t1, #tmp t2
WHERE t1.client = t2.client
AND t2.startdate > t1.startdate
AND t2.startdate Between t1.startdate And t2.startdate
GROUP BY t1.client
UNION
SELECT t1.client, MIN( t1.startdate ), MAX( t1.enddate )
FROM #tmp t1
WHERE NOT EXISTS
(
SELECT *
FROM #tmp t2
WHERE t1.client = t2.client
AND t2.startdate > t1.startdate
AND t2.startdate Between t1.startdate And t2.startdate
)
GROUP BY t1.client
If the code doesn't quite do what you want, perhaps the theory is good, ie a
UNION of records which have range matches, and those that don't.
Let me know how you get on.
Damien
"Cathy Smith" wrote:

> Hi, I was hoping someone could help me out.
> Is it possible for SQL to take a table which has date range data (start an
d
> end date indicating the contract period of the client) and create a new
> table which creates a union of 'unionizable' range data for each specific
> client. For example (here i am using numbers to indicate date order):
> client startdate enddate
> A 2 5
> A 3 7
> A 7 10
> A 11 12
> B 4 6
> B 8 14
> B 5 7
> C 2 3
> C 3 10
> C 1 20
> The table operation would give (for example {2..5} U {3..7} U {7..10} =
> (2..10} in the resultant table. But {4..6} U {8..14} does not have a commo
n
> union, so I just leave them as {4..6} and {8..14} in the resultant table:
> client startdate enddate
> A 2 10
> A 11 12
> B 4 7
> B 8 14
> B 15 21
> C 1 20
> I am unable to determine how to do this. I was thinking to move towards
> implementing cursors, but that in itself will be a complex algorithm. Is
> there some easier method to use? I was also thinking of cross joining the
> initial table with itself on the condition that t1.client = t2.client (yes
> this is not a cross join, jut results in an inner join). Then deriving a n
ew
> table from this based upon a comparison between t1.startdate , t2.startdat
e
> and t1.enddate, t2.enddate
> Would anyone have any insight into this?
> any help most appreciated!
> thanks!
> Cathy
>
>|||-- If you are using SQL Server 2005, you can use
-- recursive CTEs to get the results
create table Contracts(client char(1), startdate int, enddate int)
insert into Contracts(client,startdate,enddate) values ('A', 2 , 5)
insert into Contracts(client,startdate,enddate) values ('A', 3 , 7)
insert into Contracts(client,startdate,enddate) values ('A', 7 , 10)
insert into Contracts(client,startdate,enddate) values ('A', 11 , 12)
insert into Contracts(client,startdate,enddate) values ('B', 4 , 6)
insert into Contracts(client,startdate,enddate) values ('B', 8 , 14)
insert into Contracts(client,startdate,enddate) values ('B', 5 , 7)
insert into Contracts(client,startdate,enddate) values ('C', 2 , 3)
insert into Contracts(client,startdate,enddate) values ('C', 3 , 10)
insert into Contracts(client,startdate,enddate) values ('C', 1 , 20);
with cte_contracts(client,startdate,enddate,m
instartdate,maxenddate) as
(
select A.client,A.startdate,A.enddate,A.startdate,A.enddate
from Contracts A
union all
select A.client,A.startdate,A.enddate,B.startdate,C.enddate
from cte_contracts A
inner join Contracts B on B.client=A.client
and B.enddate >= A.minstartdate and B.startdate <= A.maxenddate
inner join Contracts C on C.client=A.client
and C.enddate >= A.minstartdate and C.startdate <= A.maxenddate
where (B.startdate < A.minstartdate and C.enddate >= A.maxenddate)
or (B.startdate <= A.minstartdate and C.enddate > A.maxenddate)
)
select distinct client,min(minstartdate),max(maxenddate)
from cte_contracts
group by client,startdate,enddate
drop table Contracts|||Thanks everyone! I really appreciate the wonderful feedback!!!
I took everyone's suggestions into perspective and finally came up with a
solution based on two views and a select statement, taken from the following
article I found at:
http://groups.google.com.au/group/c...e3dba76e3bc5d57
I modified it to encompass an additional column called client.
Thanks so much everyone for your wonderful solutions!!!
Cathy
"Damien" <Damien@.discussions.microsoft.com> wrote in message
news:BC80A0E1-86A5-4EB5-83D3-821FEC1D0765@.microsoft.com...
> Cathy,
> what about something like this:
> -- DROP TABLE #tmp
> CREATE TABLE #tmp ( client CHAR(1), startdate INT, enddate INT )
> SET NOCOUNT ON
> INSERT INTO #tmp VALUES ( 'A', 2, 5 )
> INSERT INTO #tmp VALUES ( 'A', 3, 7 )
> INSERT INTO #tmp VALUES ( 'A', 7, 10 )
> INSERT INTO #tmp VALUES ( 'A', 11, 12 )
> INSERT INTO #tmp VALUES ( 'B', 4, 6 )
> INSERT INTO #tmp VALUES ( 'B', 8, 14 )
> INSERT INTO #tmp VALUES ( 'B', 5, 7 )
> INSERT INTO #tmp VALUES ( 'C', 2, 3 )
> INSERT INTO #tmp VALUES ( 'C', 3, 10 )
> INSERT INTO #tmp VALUES ( 'C', 1, 20 )
> SET NOCOUNT OFF
>
> -- SELECT * FROM #tmp
>
> SELECT t1.client, MIN( t1.startdate ), MAX( t1.enddate )
> FROM #tmp t1, #tmp t2
> WHERE t1.client = t2.client
> AND t2.startdate > t1.startdate
> AND t2.startdate Between t1.startdate And t2.startdate
> GROUP BY t1.client
> UNION
> SELECT t1.client, MIN( t1.startdate ), MAX( t1.enddate )
> FROM #tmp t1
> WHERE NOT EXISTS
> (
> SELECT *
> FROM #tmp t2
> WHERE t1.client = t2.client
> AND t2.startdate > t1.startdate
> AND t2.startdate Between t1.startdate And t2.startdate
> )
> GROUP BY t1.client
> If the code doesn't quite do what you want, perhaps the theory is good, ie
> a
> UNION of records which have range matches, and those that don't.
> Let me know how you get on.
>
> Damien
> "Cathy Smith" wrote:
>

Create new Date table with a stored proceedure

Hi,

I have a table from which I need to create a report via MSRS2005, however the data in the table is awful in its construction and I was hoping to be able to use a stored proceedure to create a new table in which I can manupulate the data, but my T-SQL programming skills aren't that clever, so if anyone can offer any advice I'd be most grateful:

In the existing table there are two columns; StartDate and EndDate which is pretty self explanitory - what I would like to do is create a new table with only one date column and if there is more than one day between StartDate and EndDate I would like it to fill in every date in between.

For example, if the StartDate is 01/06/2007 and the EndDate 10/06/2007 I'd like the new table to list dates 01/06/2007 through 10/06/2007 inclusive in one column.

Is this possible? All suggestions welcome.

Thanks in advance,

Paul

Might be the below code will help you.

Code Snippet

CREATE PROCEDURE FillDates
@.StartDate DATETIME,
@.EndDate DATETIME
AS
BEGIN
IF datediff(day,@.StartDate,@.EndDate) >0
BEGIN
CREATE TABLE #Calander(CalanderDay DateTime)
WHILE(@.StartDate<=@.EndDate)
BEGIN
INSERT #Calander SELECT @.StartDate
SET @.StartDate = DATEADD(day,1,@.StartDate)
END
SELECT CONVERT(VARCHAR,CalanderDay,103) FROM #Calander
END
ELSE
PRINT 'No'
END
GO

EXEC FillDates '06/01/2007', '06/10/2007'
GO

Thanks,

R@.j

Create default

Hi All,
Can someone tell me how to create a default that put the current date into a record on insert and current date + 1 year into another record!?
Cheers Wimmouse getdate() in the field where date field is used in insert.|||Originally posted by nhariharan
use getdate() in the field where date field is used in insert.

I tried it, but i keeps the null value.|||use pubs
go
create table #abc
(
fname varchar(10),
joindate datetime default getdate(),
joinyear int default datepart(yyyy,getdate())
)
go
insert into #abc
(
fname
)
select
'Enigma'
go
select
*
from
#abc
go
drop table #abc
go|||Originally posted by Enigma

use pubs
go
create table #abc
(
fname varchar(10),
joindate datetime default getdate(),
joinyear int default datepart(yyyy,getdate())
)
go
insert into #abc
(
fname
)
select
'Enigma'
go
select
*
from
#abc
go
drop table #abc
go


Thanx the getdate() works.
I use 2 columns 1 named join date and 1 named enddate ,standard users get 1 year acces to the application so when a new user register the enddate must be automatically set 1 year after the joindate,
do you know how to manage that?

Thanx already.

Cheers Wim

I|||Originally posted by Wimmo
Thanx the getdate() works.
I use 2 columns 1 named join date and 1 named enddate ,standard users get 1 year acces to the application so when a new user register the enddate must be automatically set 1 year after the joindate,
do you know how to manage that?

Thanx already.

Cheers Wim

I
create table #abc
(
fname varchar(10),
joindate datetime default getdate(),
Enddate datetime default dateadd(yy,1,getdatE())
)|||Originally posted by harshal_in
create table #abc
(
fname varchar(10),
joindate datetime default getdate(),
Enddate datetime default dateadd(yy,1,getdatE())
)

I tried this but the result seems strange:

joindate 13-2-2004 11:48:45 enddate Feb 13 200|||Originally posted by Wimmo
I tried this but the result seems strange:

joindate 13-2-2004 11:48:45 enddate Feb 13 200

create table #abc
(
fname varchar(10),
joindate datetime default getdate(),
endate datetime default dateadd(yy,1,getdate())
)
go
insert into #abc
(
fname
)
select
'Enigma'
go
select
*
from
#abc
go
drop table #abc
go

Create date Function using SQL

Do anyone know any functions in SQL to create a new date using numeric numbers eg.
day=1, month=2 , year=2003
final = 01/02/2003
please help thanksto_date(lpad(day,2,'0')||'/'||lpad(month,2,'0')||'/'||year,'dd/mm/yyyy')

Originally posted by Twinki
Do anyone know any functions in SQL to create a new date using numeric numbers eg.

day=1, month=2 , year=2003

final = 01/02/2003

please help thanks

Create date field from substring of text field

I am trying to populate a field in a SQL table based on the values
returned from using substring on a text field.

Example:

Field Name = RecNum
Field Value = 024071023

The 7th and 8th character of this number is the year. I am able to
get those digits by saying substring(recnum,7,2) and I get '02'. Now
what I need to do is determine if this is >= 50 then concatenate a
'19' to the front of it or if it is less that '50' concatenate a '20'.
This particular example should return '2002'. Then I want to take the
result of this and populate a field called TaxYear.

Any help would be greatly apprecaietd.

MarkMark,

Assuming both RecNum and TaxYear fields are in the same table, you can use
this script to populate TaxYear:

update YourTable
set TaxYear = case
when SubString(RecNum,7,2) >= '50' then '19' +
SubString(RecNum,7,2)
else '20' + SubString(RecNum,7,2)
end

Shervin

"Mark" <markcash@.Hotmail.com> wrote in message
news:57bdc737.0310151257.1dc4d0a9@.posting.google.c om...
> I am trying to populate a field in a SQL table based on the values
> returned from using substring on a text field.
> Example:
> Field Name = RecNum
> Field Value = 024071023
> The 7th and 8th character of this number is the year. I am able to
> get those digits by saying substring(recnum,7,2) and I get '02'. Now
> what I need to do is determine if this is >= 50 then concatenate a
> '19' to the front of it or if it is less that '50' concatenate a '20'.
> This particular example should return '2002'. Then I want to take the
> result of this and populate a field called TaxYear.
> Any help would be greatly apprecaietd.
> Mark|||This work exaclty like I was wanting!!!

Thanks for the advice Shervin!!

Mark

"Shervin Shapourian" <ShShapourian@.hotmail.com> wrote in message news:<vorem27pdlp2a9@.corp.supernews.com>...
> Mark,
> Assuming both RecNum and TaxYear fields are in the same table, you can use
> this script to populate TaxYear:
> update YourTable
> set TaxYear = case
> when SubString(RecNum,7,2) >= '50' then '19' +
> SubString(RecNum,7,2)
> else '20' + SubString(RecNum,7,2)
> end
> Shervin
> "Mark" <markcash@.Hotmail.com> wrote in message
> news:57bdc737.0310151257.1dc4d0a9@.posting.google.c om...
> > I am trying to populate a field in a SQL table based on the values
> > returned from using substring on a text field.
> > Example:
> > Field Name = RecNum
> > Field Value = 024071023
> > The 7th and 8th character of this number is the year. I am able to
> > get those digits by saying substring(recnum,7,2) and I get '02'. Now
> > what I need to do is determine if this is >= 50 then concatenate a
> > '19' to the front of it or if it is less that '50' concatenate a '20'.
> > This particular example should return '2002'. Then I want to take the
> > result of this and populate a field called TaxYear.
> > Any help would be greatly apprecaietd.
> > Mark