Reformat YYYYMMDD String into MM/DD/YYYY

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Reformat YYYYMMDD String into MM/DD/YYYY

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-05-13 : 09:54:24

Chris writes "I am writing a query to do a count of occurences (how many times an employee number shows up) on two fields in a table. The first field is the employee number (stored as text), the second field is the date (stored as a long integer) and is formatted as YYYYMMDD. When I run my query, I would like the date in the output to be reformated in MM/DD/YYYY format. Sample output would be:

Count of Employee     Date

-----------------   --------

       5             03/01/2002

       6             03/02/2002

       12            03/03/2002

How do I reformat the date field so it comes out as MM/DD/YYYY?"

Nazim
A custom title

1408 Posts

Posted - 2002-05-13 : 09:56:15

Check for Convert function in BOL.

--------------------------------------------------------------

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-05-13 : 12:37:28

I think this may be what you are looking for.

Create table dates
(employee_number char(11),
date1 int)
GO

INSERT INTO dates VALUES ('444-44-4444', 20020510)
INSERT INTO dates VALUES ('444-44-4444', 20020511)
INSERT INTO dates VALUES ('444-44-4444', 20020512)
INSERT INTO dates VALUES ('555-55-5555', 20020510)
INSERT INTO dates VALUES ('555-55-5555', 20020511)
GO

SELECT Substring(cast(date1 as char(8)),5,2)+'/'+RIGHT(cast(date1 as char(8)),2)+'/'+LEFT(cast(date1 as char(8)),4) as Date1, Count(employee_number)
FROM Dates
GROUP BY Substring(cast(date1 as char(8)),5,2)+'/'+RIGHT(cast(date1 as char(8)),2)+'/'+LEFT(cast(date1 as char(8)),4)
GO

Jeremy

KHeon
Posting Yak Master

135 Posts

Posted - 2002-05-13 : 15:35:05

You could also try using DATEPART to build your date string.

Kyle Heon
PixelMEDIA, Inc.
Senior Application Programmer, MCP
kheon@pixelmedia.com

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-05-13 : 15:57:59

Kyle,

Will this work since his date column has an integer datatype? The only reason I used cast is because of the datatype. When I try using datepart in a query, I get the following error:

Arithmetic overflow error converting expression to data type datetime

Jeremy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources