Cast or Convert a Date field to text and add characters to it.

Use CONVERT  for example

SELECT ('AB-' + CONVERT(varchar, myDate, 112) + '-#') AS myNumberInView

The format codes listed here https://www.mssqltips.com/sqlservertip/1145/date-and-time-conversions-using-sql-server/

Start with this..

SELECT 'AB-' + CONVERT(VARCHAR, GETDATE(), 112) + '-#'

Select allOpen in new window

We'll need to see a sample data set to handle the 'the end # is a counter...' part.

Thanks!  I figured out the formatting part as your examples above.  What I'm having a hard time is figuring out how to Group the Data so that I can count the records by Day?  I have other records that I need in the View that make the rows unique.

CREATE VIEW [vw_export]
AS
SELECT        TOP (100) PERCENT table1.Title, table1.FirstName, table1.LastName, CONVERT(varchar(10),
                         table1.OriginationDate, 1) AS OriginationDate, table1.Priority, table1.Area, table1.Module, table1.Platform,
                         table1.Component, table1.VersionFound, ISNULL(table1.tbl_ChangeRequest.EstimatedHours, 0) AS EstimatedHours,
                         table1.Source, table1.RequirementNumber, table1.Description,
                         table1.Status, 'AB-' + CONVERT(VARCHAR(10), table1.OriginationDate, 112) AS ABNumber
FROM                     table1
                         
GROUP BY             table1.Title, table1.FirstName, table1.LastName, CONVERT(varchar(10), table1.tbl_ChangeRequest.OriginationDate, 1),
                  table1.Priority, table1.Area, table1.Module, table1.Platform, table1.Component, table1.VersionFound,
                        ISNULL(table1.EstimatedHours, 0), table1.Source, table1.RequirementNumber,
                        table1.Description, table1.Status, 'AB-' + CONVERT(VARCHAR(10), table1.OriginationDate, 112)
                  ORDER BY OriginationDate

GO

>What I'm having a hard time is figuring out how to Group the Data so that I can count the records by Day?
You'll probably want to do a subquery where you convert datetime to date, which makes life easy in the main query.  Something like..

SELECT t1.ID, t1g.dt, RANK() OVER (PARTITION BY t1g.dt ORDER BY whatever) as the_counter
FROM Table1 t1g
   JOIN (SELECT ID, CAST(OriginationDate) as dt FROM Table1) t1g ON t1.ID = t1g.ID
ORDER BY t1g.dt, the_counter

Select allOpen in new window

Once you get the above correct then you can throw that into a subquery and pull off the expression you want.

Slight correction to the above..

SELECT t1g.dt, RANK() OVER (PARTITION BY t1g.dt ORDER BY whatever) as the_counter
FROM Table1 t1g
   JOIN (SELECT ID, CAST(OriginationDate) as dt FROM Table1) t1g ON t1.ID = t1g.ID
GROUP BY t1g.dt
ORDER BY t1g.dt, the_counter

Select allOpen in new window

I got it to work!  How would I add this as a sub-query?

SELECT cr.OriginationDate, ROW_NUMBER() OVER (PARTITION BY cr1.Origination_Date ORDER BY Origination_Date) as the_counter
FROM mytable.tbl_Request cr
   JOIN (SELECT ID, CAST(OriginationDate as date) as Origination_Date FROM mytable.tbl_Request) cr1 ON cr.ID = cr1.ID
ORDER BY cr.OriginationDate, the_counter

Thanks everyone!  I got it working and now know more about SQL subqueries!