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

In SQL, I have a Date field called Origination Date that is a datetime datatype.
I am writing a View where I need to create a Request Number that is a combination of the Date field and the letters AB.

The end results needs to read:  AB-20181002-# if the date was 10-02-2018.  The end # is a counter for how many requests have been made for that day.

So I have a select query that selects the rows I need, how do I create this combination column?
Crystal RouseTask LeadAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Sebastian StrittmatterSoftware DevelopmentCommented:
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/
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Start with this..
SELECT 'AB-' + CONVERT(VARCHAR, GETDATE(), 112) + '-#'

Open in new window

We'll need to see a sample data set to handle the 'the end # is a counter...' part.
0
Crystal RouseTask LeadAuthor Commented:
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
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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

Open in new window

Once you get the above correct then you can throw that into a subquery and pull off the expression you want.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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

Open in new window

0
Crystal RouseTask LeadAuthor Commented:
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
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Give this a whirl..

SELECT 'AB-' + CONVERT(VARCHAR, cr.OriginationDate, 112) + '-' + CAST(a.the_counter as varchar(10)) as makeupanamehere
FROM (
   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) a

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Crystal RouseTask LeadAuthor Commented:
Thanks everyone!  I got it working and now know more about SQL subqueries!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.