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

Crystal Rouse
Crystal Rouse used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Sebastian StrittmatterSoftware Development

Commented:
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/
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
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.
Crystal RouseTask Lead

Author

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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
>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.
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
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

Crystal RouseTask Lead

Author

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
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
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

Crystal RouseTask Lead

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial