how to get the data week wise in sql server

Question

Post reply

how to get the data week wise in sql server

narendra.babu57

Old Hand

Points: 386
More actions
February 29, 2012 at 1:13 am

#256737

hi,
I have following tables like
1)Table : QMaster & datatype of date column is nvarchar
date cnt Empcode
2/2/2011 1 101
2/2/2011 1 102
2/4/2011 1 103
2/4/2011 1 104
2/6/2011 1 105
2/6/2011 1 106
2/8/2011 1 107
2/9/2011 1 108
2/10/2011 1 109
2/11/2011 1 110
2/11/2011 1 111
2/24/2011 1 112
2/27/2011 1 113
2/27/2011 1 114
3/01/2011 1 115
3/04/2011 1 116
3/09/2011 1 117
2)Table: QEmp
Empcode EmpName
101 a
102 b
103 c
104 d
105 e
106 f
107 g
108 h
109 i
110 j
111 k
112 l
113 m
114 n
115 0
116 p
117 q
MY output format is
Week count empcode empname
1-3 feb 1 101 a
1-3 feb 1 102 b
4-10 feb 1 103 c
4-10 feb 1 104 d
4-10 feb 1 105 e
4-10 feb 1 106 f
4-10 feb 1 107 g
4-10 feb 1 108 h
4-10 feb 1 109 i
11-17 feb 1 110 j
11-17 feb 1 111 k
18-24 feb 1 112 l
25-28 feb 1 113 m
25-28 feb 1 114 n
1-3 march 1 115 0
4-10 march 1 116 p
4-10 march 1 117 0

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

Do you have a calendar table in your database?

If not you have to calculate it yourself:

Getting the first day in a week with T-SQL

Getting the EmpName in the result can easily be achieved with a join.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

GuruGPrasad SSCommitted Points: 1768 More actions · Answer 2

create table QMaster (dates date,cnt int,Empcode int)

Insert into QMaster values('2/4/2011', 1 ,104),('2/4/2011', 1, 105),('2/4/2011', 1 ,104),('2/6/2011',1, 105),('2/6/2011' ,1, 106),

('2/8/2011', 1 ,107),('2/9/2011', 1, 108),('2/10/2011', 1, 109),('2/11/2011', 1, 110),('2/11/2011', 1, 111),('2/24/2011', 1 ,112),

('2/27/2011', 1 ,113),('2/27/2011', 1, 114),('3/01/2011', 1, 115),('3/04/2011', 1, 116),('3/09/2011', 1 ,117)

create table QEmp(Empcode int ,EmpName varchar)

Insert into QEmp values(101,'a'),(102,'b'),(103 ,'c'),(104,'d'),(105 ,'e'),(106,'f'),(107 ,'g'),(108 ,'h'),(109,'i'),(110 ,'j'),

(111,'k'),(112,'l'),(113,'m'),(114,'n'),(115,'0'),(116,'p'),(117,'q')

with test

as

(

select dates, CONVERT(varchar, DATEADD(ww, DATEDIFF(ww,0,dates), 0) ,106) + ' - ' + convert(varchar, DATEADD(ww, DATEDIFF(ww,0,dates), 7),106) ends,cnt,m.empcode,e.empname from qmaster M inner join qemp e on M.empcode=e.empcode

)

select * from test

Regards

Guru

Cadavre SSC-Forever Points: 41690 More actions · Answer 3

Sample data:-

SELECT date, cnt, Empcode

INTO QMaster

FROM (VALUES(N'2/2/2011', 1, 101),(N'2/2/2011', 1, 102),

(N'2/4/2011', 1, 103),(N'2/4/2011', 1, 104),(N'2/6/2011', 1, 105),

(N'2/6/2011', 1, 106),(N'2/8/2011', 1, 107),(N'2/9/2011', 1, 108),

(N'2/10/2011', 1, 109),(N'2/11/2011', 1, 110),(N'2/11/2011', 1, 111),

(N'2/24/2011', 1, 112),(N'2/27/2011', 1, 113),(N'2/27/2011', 1, 114),

(N'3/01/2011', 1, 115),(N'3/04/2011', 1, 116),(N'3/09/2011', 1, 117)) a(date, cnt, Empcode)

SELECT Empcode, EmpName

INTO QEmp

FROM (VALUES(101, 'a'),(102, 'b'),(103, 'c'),

(104, 'd'),(105, 'e'),(106, 'f'),(107, 'g'),

(108, 'h'),(109, 'i'),(110, 'j'),(111, 'k'),

(112, 'l'),(113, 'm'),(114, 'n'),(115, '0'),

(116, 'p'),(117, 'q')) a(Empcode, EmpName)

CLR (T-SQL)

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[weekNumber]') AND type IN (N'FN', N'IF', N'TF', N'FS', N'FT'))

DROP FUNCTION [dbo].[weekNumber];

GO

IF EXISTS (SELECT *FROM sys.assemblies asms WHERE asms.NAME = N'dateWeekValidation' AND is_user_defined = 1)

DROP ASSEMBLY [dateWeekValidation];

CREATE ASSEMBLY [dateWeekValidation] AUTHORIZATION [dbo]

FROM

0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103001B0B4E4F0000000000000000E00002210B0108000010000000060000000000001E2E0000002000000040000000004000002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000D02D00004B000000004000008803000000000000000000000000000000000000006000000C0000001C2D00001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000240E0000002000000010000000020000000000000000000000000000200000602E7273726300000088030000004000000004000000120000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001600000000000000000000000000004000004200000000000000000000000000000000002E00000000000048000000020005000C220000100B000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001B3003008D0000000100001100000F00280F00000A7201000070281000000A2C0E0F00280F00000A281100000A2C077E1200000A2B110F00280F00000A281300000A6F1400000A0A06281100000A0D092D1E000672BC020070281300000A281500000A2802000006281600000A0CDE2600DE150B00076F1700000A6F1800000A281600000A0CDE0E0072CE020070281600000A0C2B0000082A0000000110000000000100666700151700000113300400FB000000030000110012000F00281900000A0F00281A00000A17281B00000A00120017281C00000A1305120523000000000000F0BF281D00000A0B020C020D2B11120223000000000000F0BF281D00000A0C1202281E00000A1B2E090806281F00000A2B0116130611062DD52B11120323000000000000F03F281D00000A0D1203281E00000A1A2E090907282000000A2B0116130611062DD51B8D1400000113071107161202282100000A13081208281300000A282200000AA211071772DE020070A21107181203282100000A13081208281300000A282200000AA211071972E2020070A211071A0F0072E6020070282300000AA21107282400000A13042B0011042A1E02282500000A2A0042534A4201000100000000000C00000076322E302E35303732370000000005006C000000B4020000237E000020030000DC03000023537472696E677300000000FC060000F002000023555300EC090000100000002347554944000000FC0900001401000023426C6F620000000000000002000001471502000900000000FA2533001600000100000019000000020000000300000002000000250000000C00000003000000010000000300000000000A00010000000000060044003D000A006C005700060081003D000600BA00A8000600D100A8000600EE00A80006000D01A80006002601A80006003F01A80006005A01A80006007501A8000600AD018E010600C101A8000600ED01DA013B00010200000600300210020600500210020A0089026E020E00C702A8020600D5023D0006000503F002060026033D00060056033D00060091033D000600CC033D0000000000010000000000010001000100100021002100050001000100502000000000960076000A000100FC200000000096008A0011000200032200000000861892001700030000000100980000000100A300210092001B00290092001B00310092001B00390092001B00410092001B00490092001B00510092001B00590092001B00610092002000690092001B00710092002500810092002B0089009200170091009200170011009E0235009900CD023900A100DC023F00A100EA024400A90011034700A10036034C0019003F03520011004A035B00B9006003350009003603350019006C036A00190075036A00190092006E0019007F037500190089037B0019009B0381001900A90386001900B80386001900C4036A00C90036034C00190036038E00A100D20393000900920017002000730030002E002B00AB002E001300C3002E001B00C3002E002300C3002E000B00AB002E003300C9002E003B00C3002E004B00C3002E005B00E1002E006300EA002E006B00F30061004400990004800000010000005A114D5000000000000021000000020000000000000000000000010034000000000002000000000000000000000001004B000000000002000000000000000000000001003D000000000000000000003C4D6F64756C653E00646174655765656B56616C69646174696F6E2E646C6C00646174655765656B56616C69646174696F6E006D73636F726C69620053797374656D004F626A6563740053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C537472696E67007765656B4E756D626572004461746554696D65004765745765656B002E63746F720064617465537472696E6700646174650053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C747572654174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C6541747472696275746500417373656D626C7956657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E417474726962757465006765745F56616C75650053797374656D2E546578742E526567756C617245787072657373696F6E730052656765780049734D6174636800537472696E670049734E756C6C4F72456D70747900456D7074790053797374656D2E476C6F62616C697A6174696F6E0043756C74757265496E666F006765745F496E76617269616E7443756C747572650049466F726D617450726F766964657200546F537472696E670050617273654578616374006F705F496D706C6963697400457863657074696F6E006765745F4D657373616765006765745F59656172006765745F4D6F6E7468004164644D6F6E7468730041646444617973004461794F665765656B006765745F4461794F665765656B006F705F477265617465725468616E006F705F4C6573735468616E006765745F44617900496E74333200436F6E636174000000000082B928003F006E003A005E0028003F003D005C0064002900280028003F003C006D006F006E00740068003E0030003F005B0031002D0039005D007C0031005B003000310032005D00290028003F003C007300650070003E005B002F002E002D005D00290028003F003C006400610079003E003300310028003F00210028002E0030003F005B0032003400360039005D007C0031003100290029007C003300300028003F0021002E0030003F00320029007C003200390028003F0028002E0030003F003200290028003F003D002E007B0033002C0034007D00280031005B0036002D0039005D007C005B0032002D0039005D005C0064002900280030005B00340038005D007C005B0032003400360038005D005B003000340038005D007C005B00310033003500370039005D005B00320036005D0029007C002800310036007C005B0032003400360038005D005B003000340038005D007C005B0033003500370039005D005B00320036005D00290030003000290029007C0030003F005B0031002D0039005D007C0031005C0064007C0032005B0030002D0038005D0029005C00320028003F003C0079006500610072003E00280031005B0036002D0039005D007C005B0032002D0039005D005C00640029005C0064007B0032007D00290028003F003A0028003F003D005C007800320030005C00640029005C007800320030007C002400290029003F0028003F003C00740069006D0065003E002800280030003F005B0031002D0039005D007C0031005B003000310032005D00290028003A005B0030002D0035005D005C00640029007B0030002C0032007D0028003F0069003A005C0020005B00410050005D004D00290029007C0028005B00300031005D005C0064007C0032005B0030002D0033005D00290028003A005B0030002D0035005D005C00640029007B0031002C0032007D0029003F002400290001114D002F0064002F007900790079007900000F49006E00760061006C006900640000032D000103200000094D004D004D004D000023B509DAA56A5F4CBD0BFD89F839C68A0008B77A5C561934E089060001110911090500010E110D03200001042001010E042001010205200101113D042001010804010000000320000E050002020E0E040001020E02060E04000012550520010E1259080003110D0E0E125905000111090E0807040E125D1109020320000806200301080808052001110D08052001110D0D042000116107000202110D110D0420010E0E0500010E1D0E110709110D110D110D110D0E110D021D0E0817010012646174655765656B56616C69646174696F6E000005010000000017010012436F7079726967687420C2A920203230313200000801000701000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010000000000001B0B4E4F000000000200000095000000382D0000380F000052534453A8744455AF99574C92B074E1E5815FD411000000633A5C75736572735C6372616967775C646F63756D656E74735C76697375616C2073747564696F20323031305C50726F6A656374735C646174655765656B56616C69646174696F6E5C646174655765656B56616C69646174696F6E5C6F626A5C44656275675C646174655765656B56616C69646174696F6E2E70646200000000F82D000000000000000000000E2E0000002000000000000000000000000000000000000000000000002E00000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500204000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000300300000000000000000000300334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001004D505A11000001004D505A113F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00490020000010053007400720069006E006700460069006C00650049006E0066006F0000006C0200000100300030003000300030003400620030000000500013000100460069006C0065004400650073006300720069007000740069006F006E000000000064006100740065005700650065006B00560061006C00690064006100740069006F006E000000000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0034003400340032002E00320030003500350037000000000050001700010049006E007400650072006E0061006C004E0061006D006500000064006100740065005700650065006B00560061006C00690064006100740069006F006E002E0064006C006C00000000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003100320000005800170001004F0072006900670069006E0061006C00460069006C0065006E0061006D006500000064006100740065005700650065006B00560061006C00690064006100740069006F006E002E0064006C006C0000000000480013000100500072006F0064007500630074004E0061006D0065000000000064006100740065005700650065006B00560061006C00690064006100740069006F006E000000000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0034003400340032002E00320030003500350037000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0034003400340032002E003200300035003500370000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000203E00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

WITH PERMISSION_SET = SAFE;

GO

CREATE FUNCTION [dbo].[weekNumber] (@dateString [nvarchar](4000))

RETURNS [nvarchar] (4000)

WITH EXECUTE AS CALLER

AS

EXTERNAL NAME [dateWeekValidation].[dateWeekValidation.dateWeekValidation].[weekNumber]

GO

Query:

--Actual query

SELECT dbo.weekNumber(date), cnt, a.Empcode, b.EmpName

FROM QMaster a

INNER JOIN QEmp b ON a.Empcode = b.Empcode

Result:

cnt Empcode EmpName

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

1-3 February 1 101 a

1-3 February 1 102 b

4-10 February 1 103 c

4-10 February 1 104 d

4-10 February 1 105 e

4-10 February 1 106 f

4-10 February 1 107 g

4-10 February 1 108 h

4-10 February 1 109 i

11-17 February 1 110 j

11-17 February 1 111 k

18-24 February 1 112 l

25-28 February 1 113 m

25-28 February 1 114 n

1-3 March 1 115 0

4-10 March 1 116 p

4-10 March 1 117 q

CLR code (C#)

using System;

using System.Data.SqlTypes;

using System.Globalization;

using System.Text.RegularExpressions;

using Microsoft.SqlServer.Server;

namespace dateWeekValidation

{

public class dateWeekValidation

{

[SqlFunction]

public static SqlString weekNumber(

SqlString dateString)

{

try

{

const string regDate =

@"(?n:^(?=\d)((?<month>0?[1-9]|1[012])(?<sep>[/.-])(?<day>31(?!(.0?[2469]|11))|30(?!.0?2)|29(?(.0?2)(?=.{3,4}(1[6-9]|[2-9]\d)(0[48]|[2468][048]|[13579][26])|(16|[2468][048]|[3579][26])00))|0?[1-9]|1\d|2[0-8])\2(?<year>(1[6-9]|[2-9]\d)\d{2})(?:(?=\x20\d)\x20|$))?(?<time>((0?[1-9]|1[012])(:[0-5]\d){0,2}(?i:\ [AP]M))|([01]\d|2[0-3])(:[0-5]\d){1,2})?$)";

//Check if date is valid datetime

var validatedDate = Regex.IsMatch(dateString.Value, regDate) && !string.IsNullOrEmpty(dateString.Value)

? dateString.Value.ToString(CultureInfo.InvariantCulture)

: string.Empty;

if (!string.IsNullOrEmpty(validatedDate))

{

return GetWeek(DateTime.ParseExact(validatedDate, "M/d/yyyy", CultureInfo.InvariantCulture));

}

catch (Exception e)

{

// on any error, return NULL

return SqlString.Null;

}

// return NULL is date is not a valid datetime

return SqlString.Null;

}

public static string GetWeek(DateTime date)

{

var firstDayOfTheMonth = new DateTime(date.Year, date.Month, 1);

var endDayOfTheMonth = firstDayOfTheMonth.AddMonths(1).AddDays(-1);

var startWeekDay = date;

var endWeekDay = date;

while (startWeekDay.DayOfWeek != DayOfWeek.Friday && startWeekDay > firstDayOfTheMonth)

startWeekDay = startWeekDay.AddDays(-1);

while (endWeekDay.DayOfWeek != DayOfWeek.Thursday && endWeekDay < endDayOfTheMonth)

endWeekDay = endWeekDay.AddDays(+1);

return startWeekDay.Day.ToString(CultureInfo.InvariantCulture) + "-" + endWeekDay.Day.ToString(CultureInfo.InvariantCulture) + " " + date.ToString("MMMM");

}

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

narendra.babu57 Old Hand Points: 386 More actions · Answer 4

hi bellow format is mix with another month also but i need separate month wise

datesendscntempcodeempname

2011-02-0431 Jan 2011 - 07 Feb 20111104d

2011-02-0431 Jan 2011 - 07 Feb 20111105e

2011-02-0607 Feb 2011 - 14 Feb 20111105e

2011-02-0607 Feb 2011 - 14 Feb 20111106f

2011-02-0807 Feb 2011 - 14 Feb 20111107g

2011-02-0907 Feb 2011 - 14 Feb 20111108h

2011-02-1007 Feb 2011 - 14 Feb 20111109i

2011-02-1107 Feb 2011 - 14 Feb 20111110j

2011-02-1107 Feb 2011 - 14 Feb 20111111k

2011-02-2421 Feb 2011 - 28 Feb 20111112l

2011-02-2728 Feb 2011 - 07 Mar 20111113m

2011-02-2728 Feb 2011 - 07 Mar 20111114n

2011-03-0128 Feb 2011 - 07 Mar 201111150

2011-03-0428 Feb 2011 - 07 Mar 20111116p

2011-03-0907 Mar 2011 - 14 Mar 20111117q

Cadavre SSC-Forever Points: 41690 More actions · Answer 5

narendra.babu57 (2/29/2012)
hi bellow format is mix with another month also but i need separate month wise

Fairly certain that you won't have the same issue with my CLR.

~~As I see it, CLR is going to be the fastest route with a calendar table a close second.~~ --edit-- Actually, I haven't tested that last statement so I've deleted it. It's something to look at when I've more time available.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lowell SSC Guru Points: 323518 More actions · Answer 6

Cadavre (2/29/2012)
{snip}
IF EXISTS (SELECT *FROM sys.assemblies asms WHERE asms.NAME = N'dateWeekValidation' AND is_user_defined = 1)
DROP ASSEMBLY [dateWeekValidation];
CREATE ASSEMBLY [dateWeekValidation] AUTHORIZATION [dbo]
FROM
0x4D5A90000300000004000000FFFF0000B{snip}

Cadavre my friend how do you script your clr to be the binary string like that?

what's the trick?

i've only done it manually so far, nd it's a pain....load the dll as a varbinary into a table, then select it, then copy paste into a script.

please tell me there is an easier way.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Cadavre SSC-Forever Points: 41690 More actions · Answer 7

Lowell (2/29/2012)
Cadavre my friend how do you script your clr to be the binary string like that?
what's the trick?
i've only done it manually so far, nd it's a pain....load the dll as a varbinary into a table, then select it, then copy paste into a script.
please tell me there is an easier way.

I generally deploy directly to a test server with a test database, then script out the assembly.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lowell SSC Guru Points: 323518 More actions · Answer 8

Cadavre (2/29/2012)
Lowell (2/29/2012)
Cadavre my friend how do you script your clr to be the binary string like that?
what's the trick?
i've only done it manually so far, nd it's a pain....load the dll as a varbinary into a table, then select it, then copy paste into a script.
please tell me there is an easier way.
I generally deploy directly to a test server with a test database, then script out the assembly.

I am so stupid;

I had no idea I could go to Object Explorer>>Specific Database>> Programmability>>Assemblies and script them out.

I'd always take a model and copy/pasted all the pieces into place.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Cadavre SSC-Forever Points: 41690 More actions · Answer 9

Lowell (2/29/2012)
I am so stupid;
I had no idea I could go to Object Explorer>>Specific Database>> Programmability>>Assemblies and script them out.
I'd always take a model and copy/pasted all the pieces into place

Whoops 😛

I'm not convinced there isn't a better way, but I've learnt CLR in the same way that I learnt T-SQL - by doing! So without having had someone to instruct me or offer pointers on where I'm going wrong, there's a fair chance that I've missed a short-cut

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

narendra.babu57 Old Hand Points: 386 More actions · Answer 10

narendra.babu57

Old Hand

Points: 386

February 29, 2012 at 6:51 am

#1453135

Than q for all Now it is working fine............

narendra.babu57 Old Hand Points: 386 More actions · Answer 11

Hi,

Some dates it will coming invalid. Why it happen?

For example 29-10-2011,30-08-2011 dates... --it will come Invalid

Cadavre SSC-Forever Points: 41690 More actions · Answer 12

narendra.babu57 (3/1/2012)
Hi,
Some dates it will coming invalid. Why it happen?
For example 29-10-2011,30-08-2011 dates... --it will come Invalid

You said your dates were in this format -- 10/29/2011. So I coded the CLR to only accept dates in that format as valid. For an invalid date, the CLR returns NULL.

Are you using the CLR? Or something else? Provide sample data and DDL in the format shown in this link[/url].

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

narendra.babu57 Old Hand Points: 386 More actions · Answer 13

Hi,

Thanks for your reply, actually in my table date column data type is nvarchar and data is following

20110830

20110829

The output is coming following bellow format

NAME week date_done NO_OF_ERRS

Brinella DSouza 22-28 April 201104271

Amit Attri - GGN22-28 April201104271

Papu Kumar Invalid201104291

narendra.babu57 Old Hand Points: 386 More actions · Answer 14

The above code is not working for 29,30 th dates for all months