sql server query help

Hi,

i have a table which is supposed to give customer_id to each person who joins on the internet

the customerid should be autogenerated each time
the customerid is in this fashion

05-01-18-001

(05 is the year,01 is the month,18 is the day)
the last three digits are auto incremented numbers

so if there are 5 members who join on the 18th january 2005 it will be

05-01-18-001
05-01-18-002
05-01-18-003
05-01-18-004
05-01-18-005

now a new member joins on the 19th january 2005 it will be

05-01-19-001
05-01-19-002
05-01-19-003
05-01-19-004
05-01-19-005
05-01-19-006
05-01-19-007


can someone tell me how to do this in sql

thanks,

sam

You would probably want to do this with an ASP script rather than SQL. And you would need to set up a unique id in the table. Not sure of the exact ASP script, but you would have to declare the date() and then do a check against your database for the latest userID (can't figure out the sql syntax right now). Then, in your asp script you combine the date() and userID as, say, newID (or whatever) and then insert that into the database with the new record. That way, in whatever other app you use this data, you can have "newID" (which would appear as 05-01-19-001, ...002, etc) be your unique identifier (even though userID is the uniqueID as far as the database is concerned).