How to split day from date

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

 Posted an hour ago
i have made sql query for pivot Attendance ,my output is 

5-1-2019	6-1-2019	7-1-2019	8-1-2019
P	A 	P	A
 

But I want this Output 

5	6	7	8
P	A 	P	A

What I have tried:

ALTER Procedure [dbo].[PIvot_Attendance]  
As  
Begin 

SELECT DISTINCT (date) as date INTO #Datessss
FROM EmployeeAtt
ORDER BY date


DECLARE @cols NVARCHAR(4000)
SELECT  @cols = COALESCE(@cols + ',[' + CONVERT(varchar, (date), 106)
				+ ']','[' + CONVERT(varchar, date, 106) + ']')
FROM    #Datessss
ORDER BY date




-- Building the query with dynamic dates
DECLARE @qry NVARCHAR(4000)
SET @qry =
'SELECT * FROM
(SELECT empcode, Day ,(date)
FROM EmployeeAtt)emp
PIVOT (MAX(Day) FOR date IN (' + @cols + ')) AS stat'


-- Executing the query
EXEC(@qry)



End

Posted 17-Jan-19 23:11pm

akhter86

Updated 18-Jan-19 2:01am

Add a Solution

4 solutions

Solution 2

Start by changing your database: don't store date or based items in text based fields, use DATE, DATETIME, or DATETIME2 instead.
The problems with text fields is that they can - and this will at some point - contain non-date information, or date info in a different format from that which you expect. And without the context provided by the user when it was input (which you don't have once it reaches the DB) you can't tell if 01/02/03 is the first of Feb 2003, the second of Jan 2003, or the third of Feb 2001.
When you add in that text based fields are sorted as text, not dates - so the comparison is entirely dependant on the first pair of different characters - it becomes both extremely clumsy and difficult to work with text based fields.
Using Date fields for date info means all that goes away, and you can use the SQL built in functions for data manipulation, such as DATEPART.

Seriously, change your DB now, before it starts to be a lot of work, or worse, bad "real world" data gets into your production DB.

Posted 17-Jan-19 23:34pm

OriginalGriff

Solution 3

What you seek is the DatePart function
DATEPART (Transact-SQL) - SQL Server | Microsoft Docs[^]

Posted 18-Jan-19 0:04am

MadMyche

Comments

Maciej Los 18-Jan-19 6:26am

5ed!

Solution 4

You may be interested in:
DAY (Transact-SQL) - SQL Server | Microsoft Docs[^]
MONTH (Transact-SQL) - SQL Server | Microsoft Docs[^]
YEAR (Transact-SQL) - SQL Server | Microsoft Docs[^]

More you'll find here: Date and Time Data Types and Functions (Transact-SQL) - SQL Server | Microsoft Docs[^]

Posted 18-Jan-19 2:01am

Maciej Los

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

#realJSOP · Accepted Answer · 2019-01-17T23:34:00

Solution 1

Try this:

SQL

DECLARE @qry NVARCHAR(4000) = 
N'SELECT * 
FROM (SElECT empcode, DATEPART(DAY, [date]) AS [DayOfMonth], [date]
      FROM EmployeeeAtt) AS emp
PIVOT (MAX(DATEPART(DAY, [date]) FOR [date] IN (' + cols + ')) AS stat'

Posted 17-Jan-19 23:34pm

#realJSOP

Comments

akhter86 18-Jan-19 6:16am

this error is coming
(6 row(s) affected)
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '('.

MadMyche 18-Jan-19 6:25am

Then you need to troubleshoot it.
I actually was working on the query you need but with the very poor column and alias names (Day, Date) I didn't bother finishing.

#realJSOP 18-Jan-19 6:32am

Be a programmer and fix it. I typed it off the top of my head without the benefit of ssms syntax checking.

Member 12314309 18-Jan-19 13:26pm

Thanks John Simmons ,i have fixed my issued

Maciej Los 18-Jan-19 6:26am

5ed!