We help IT Professionals succeed at work.

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

98 Views
Last Modified: 2018-10-05
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

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
CERTIFIED EXPERT
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
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
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
CERTIFIED EXPERT
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
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Crystal RouseTask Lead

Author

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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions