appending to datefield - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

appending to datefield

Author

Topic

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2008-05-15 : 10:45:23

hi

i have a field in a table customer
as customerid values ex- 112,113
datefield stored in this format 03/31/2008 etc

i want to insert a field in another table called
report date in this manner
042008112(04 is the month,2008 is the year and 112 is customer id)
when i pass a date range as '03/01/2008' and '03/31/2008'
thanks

jason7655
Starting Member

24 Posts

Posted - 2008-05-15 : 11:30:16

I would think you would need to do the following:
1. Select customerid and datefield from customer where datefield between your range.
2. Play around with the output to get it into the format that you are wanting. Get some ideas from here (http://www.tek-tips.com/viewthread.cfm?qid=1424163&page=1) about taking out the / and adding the customerid to the end of it.
3. Update your report date table with this new info.

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2008-05-15 : 11:52:50

thanks for the reply

but get you tel me how to make getdate() in this format

042008 removing the day

jason7655
Starting Member

24 Posts

Posted - 2008-05-15 : 12:05:27

See if this will get you going:


DECLARE @customerid varchar(255)
DECLARE @datefield varchar(255)
DECLARE @dateMonth varchar(255)
DECLARE @dateYear varchar(255)
DECLARE @finalProduct varchar(255)

SET @customerid = '112'
SET @datefield = '03/31/2008'
SET @dateMonth = LEFT(@datefield, 2)
SET @dateYear = RIGHT(@datefield, 4)

SELECT @finalProduct = @dateMonth + @dateYear + @customerid
PRINT (@finalProduct)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-15 : 13:05:37

If your datefield is of datetime datatype. You can even use MONTH(),YEAR() etc function to get the month and year numbers.

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2008-05-15 : 13:35:50

Have you thought about creating a view? If you're creating a table just to format data, it might be a lot better to use a view.

An infinite universe is the ultimate cartesian product.

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2008-05-16 : 12:33:30

thansk for your replies and i am done with it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources