Avatar of Crystal Rouse
Crystal Rouse
Flag for United States of America asked on

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?
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
Crystal Rouse

8/22/2022 - Mon
Sebastian Strittmatter

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 Horn

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 Rouse

ASKER
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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Jim Horn

>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 Horn

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 Rouse

ASKER
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Jim Horn

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Crystal Rouse

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