DCSIMG
המידע שקיבלת במסגרת בלוג זה הינו מידע כללי בלבד ואין לראות ו\או להסתמך על מידע כאמור כייעוץ ו\או תחליף לייעוץ מכל סוג שהוא ו\או להסתמך עליו לעניין כלשהוא. May 2009 - Posts - Business Intelligence,Technology, Thoughts, Thinking
Sign in | Join | Help

May 2009 - Posts

SQL Server 2008 Developer Training Kit

Published at May 25 2009, 08:18 PM by Ronen Chenn
 

Overview and Benefits
The training kit offers the following benefits:

  • Learn how to build web applications that exploit the unique features and capabilities of SQL Server 2008.
  • Provides a comprehensive set of presentations, demos and hands-on labs
  • Designed for web developers who are already familiar with SQL Server application development.
  • Easy to download and install and works on free editions of SQL Server 2008 and Visual Studio 2008.

Intended Audience
The training kit is designed for the following technical roles:
  • Web developers who build applications for the Microsoft platform.
  • Microsoft evangelists, technical specialists and consultants.

Contents
The training kit includes the following content:
  • Presentations (6)
    • Filestream
    • Spatial
    • T-SQL
    • Date and Time Types
    • SQLCLR
    • Reporting Services
  • Demos (12)
    • AdventureWorks Racing All-Up SQL Server 2008 Demo
    • SQL Server 2008 All-Up Spatial Demo
    • Spatial Types Demo
    • Intro to Filestream Demo
    • SQL CLR Nullable Types Demo
    • Programming with Filestream Demo
    • Reporting Services Web Application Integration Demo
    • Date and Time Support in SQL Server 2008 Demo
    • T-SQL Table-Valued Parameters Demo
    • T-SQL Row Constructors Demo
    • T-SQL Grouping Sets Demo
    • T-SQL Merge Demo
  • Hands-on Labs (3)
    • Using Spatial Data in TSQL
    • Using Spatial Data in Managed Code
    • Using SQL CLR in SQL Server 2008
  • Download Site

Israeli BI User Group Meeting #4

Published at May 21 2009, 07:01 PM by Ronen Chenn

 

שלום לכולם,

במפגש הקרוב שמתוכנן ל27.5 יום רביעי מתוכננים הנושאים הבאים:

ניתן להירשם כאן!

מה במפגש?

17:30 - 17:45 Gathering

17:45 - 18:45 Itay Braun- SSIS Best practices, Tips & Tricks                                                 

18:45 - 19:15 Q&A

19:15 - 19:30 Break

19:30 - 20:30 Yossi Hanin -Scorecards and KPI's what are they really?

20:30- 21:00 Q&A

כולם מוזמנים!

 

תגים:, ,

ProClarity Analytics Server 6.3 Service Pack 2 (2217)

Published at May 18 2009, 11:49 AM by Ronen Chenn

Microsoft ProClarity Analytics Server 6.3 Service Pack 2 is available. The build of this service pack is 6.3.2217. This service pack includes all fixes that were released for the release version of ProClarity Analytics Server 6.3 (build 6.3.129.200).

Issues that are fixed by this service pack
  • When you set the Callouts property of a chart to Value, and then you specify a number value for the Decimal Places property of the chart axis, the actual number of decimal places in the callouts value is the number of the Decimal Places property plus 1.
  • When you slice a background hierarchy, you receive the following error message:

    Query Failed! (800F000A)

  • When you use ProClarity Web Standard to view a chart that was created in ProClarity Desktop Professional and published to ProClarity Analytics Server, the scale of the axis switches to use very large percentage numbers. This problem occurs if the following conditions are true:
    • The chart contains a measure whose value is NULL.
    • The NULL measure is on an axis that uses the Auto Scaling feature.
  • Consider the following scenario. You create a calculated member by specifying the format string as percentage in ProClarity Desktop Professional. You create a chart by putting the calculated member on an axis. Additionally, you put another set that contains formatted values on the opposite axis. Then you publish the book that contains this chart to ProClarity Analytics Server. When you open the chart in ProClarity Web Standard, the axis for the calculated member and the callouts for the calculated member are displayed in the decimal formatting instead of in the percentage formatting.
  • After you install ProClarity Analytics Server on Windows Server 2008, ProClarity Analytics Server cannot access the Applicationhost.config file. The ReadMe file for this cumulative update contains instructions that describe how to configure ProClarity Analytics Server to run on Windows Server 2008.

DownloadDownload the PAS63Hotfix2217.exe package now. (http://www.microsoft.com/downloads/details.aspx?FamilyID=d819745e-987c-468f-bf9c-feebf4a81ec1)

Ronen

תגים:,

Convert DateTime Format

Published at May 17 2009, 03:08 PM by Ronen Chenn

 

Hi ,

Recently I have encountered the need for extended date format such as quarter.

So I found many tips over the web and concentrated them into to one post.

For example 'MM/YYYY', and select Quarter from Getdate()...

Hope this help!

Ronen

 

Date Format

Standard

SQL Statement

Sample Output

Mon DD YYYY 1
HH:MIAM (or PM)

Default

SELECT CONVERT(VARCHAR(20), GETDATE(), 100)

Jan 1 2005 1:29PM 1

MM/DD/YY

USA

SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY]

11/23/98

MM/DD/YYYY

USA

SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]

11/23/1998

YY.MM.DD

ANSI

SELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD]

72.01.01

YYYY.MM.DD

ANSI

SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD]

1972.01.01

DD/MM/YY

British/French

SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY]

19/02/72

DD/MM/YYYY

British/French

SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY]

19/02/1972

DD.MM.YY

German

SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY]

25.12.05

DD.MM.YYYY

German

SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY]

25.12.2005

DD-MM-YY

Italian

SELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS [DD-MM-YY]

24-01-98

DD-MM-YYYY

Italian

SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY]

24-01-1998

DD Mon YY 1

-

SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY]

04 Jul 06 1

DD Mon YYYY 1

-

SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY]

04 Jul 2006 1

Mon DD, YY 1

-

SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY]

Jan 24, 98 1

Mon DD, YYYY 1

-

SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY]

Jan 24, 1998 1

HH:MM:SS

-

SELECT CONVERT(VARCHAR(8), GETDATE(), 108)

03:24:53

Mon DD YYYY HH:MI:SS:MMMAM (or PM) 1

Default +
milliseconds

SELECT CONVERT(VARCHAR(26), GETDATE(), 109)

Apr 28 2006 12:32:29:253PM 1

MM-DD-YY

USA

SELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY]

01-01-06

MM-DD-YYYY

USA

SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY]

01-01-2006

YY/MM/DD

-

SELECT CONVERT(VARCHAR(8), GETDATE(), 11) AS [YY/MM/DD]

98/11/23

YYYY/MM/DD

-

SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD]

1998/11/23

YYMMDD

ISO

SELECT CONVERT(VARCHAR(6), GETDATE(), 12) AS [YYMMDD]

980124

YYYYMMDD

ISO

SELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD]

19980124

DD Mon YYYY HH:MM:SS:MMM(24h) 1

Europe default + milliseconds

SELECT CONVERT(VARCHAR(24), GETDATE(), 113)

28 Apr 2006 00:34:55:190 1

HH:MI:SS:MMM(24H)

-

SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS:MMM(24H)]

11:34:23:013

YYYY-MM-DD HH:MI:SS(24h)

ODBC Canonical

SELECT CONVERT(VARCHAR(19), GETDATE(), 120)

1972-01-01 13:42:24

YYYY-MM-DD HH:MI:SS.MMM(24h)

ODBC Canonical
(with milliseconds)

SELECT CONVERT(VARCHAR(23), GETDATE(), 121)

1972-02-19 06:35:24.489

YYYY-MM-DDTHH:MM:SS:MMM

ISO8601

SELECT CONVERT(VARCHAR(23), GETDATE(), 126)

1998-11-23T11:25:43:250

DD Mon YYYY HH:MI:SS:MMMAM 1

Kuwaiti

SELECT CONVERT(VARCHAR(26), GETDATE(), 130)

28 Apr 2006 12:39:32:429AM 1

DD/MM/YYYY HH:MI:SS:MMMAM

Kuwaiti

SELECT CONVERT(VARCHAR(25), GETDATE(), 131)

28/04/2006 12:39:32:429AM

 

Extended Date Formats

Date Format

SQL Statement

Sample Output

YY-MM-DD

SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 8) AS [YY-MM-DD]

SELECT REPLACE(CONVERT(VARCHAR(8), GETDATE(), 11), '/', '-') AS [YY-MM-DD]

99-01-24

YYYY-MM-DD

SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD]

SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), '/', '-') AS [YYYY-MM-DD]

1999-01-24

MM/YY

SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 3), 5) AS [MM/YY]
SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 3), 4, 5) AS [MM/YY]

08/99

MM/YYYY

SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 103), 7) AS [MM/YYYY]

12/2005

YY/MM

SELECT CONVERT(VARCHAR(5), GETDATE(), 11) AS [YY/MM]

99/08

YYYY/MM

SELECT CONVERT(VARCHAR(7), GETDATE(), 111) AS [YYYY/MM]

2005/12

Month DD, YYYY 1

SELECT DATENAME(MM, GETDATE()) + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) AS [Month DD, YYYY]

July 04, 2006 1

Mon YYYY 1

SELECT SUBSTRING(CONVERT(VARCHAR(11), GETDATE(), 113), 4, 8) AS [Mon YYYY]

Apr 2006 1

Month YYYY 1

SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [Month YYYY]

February 2006 1

DD Month 1

SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) AS [DD Month]

11 September 1

Month DD 1

SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(DAY(GETDATE()) AS VARCHAR(2)) AS [Month DD]

September 11 1

DD Month YY 1

SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR(4)), 2) AS [DD Month YY]

19 February 72 1

DD Month YYYY 1

SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [DD Month YYYY]

11 September 2002 1

MM-YY

SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 5), 5) AS [MM-YY]
SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 5), 4, 5) AS [MM-YY]

12/92

MM-YYYY

SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 105), 7) AS [MM-YYYY]

05-2006

YY-MM

SELECT RIGHT(CONVERT(VARCHAR(7), GETDATE(), 120), 5) AS [YY-MM]
SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 5) AS [YY-MM]

92/12

YYYY-MM

SELECT CONVERT(VARCHAR(7), GETDATE(), 120) AS [YYYY-MM]

2006-05

MMDDYY

SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), '/', '') AS [MMDDYY]

122506

MMDDYYYY

SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') AS [MMDDYYYY]

12252006

DDMMYY

SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 3), '/', '') AS [DDMMYY]

240702

DDMMYYYY

SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), '/', '') AS [DDMMYYYY]

24072002

Mon-YY 1

SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE(), 6), 6), ' ', '-') AS [Mon-YY]

Sep-02 1

Mon-YYYY 1

SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8), ' ', '-') AS [Mon-YYYY]

Sep-2002 1

DD-Mon-YY 1

SELECT REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ' ', '-') AS [DD-Mon-YY]

25-Dec-05 1

DD-Mon-YYYY 1

SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ' ', '-') AS [DD-Mon-YYYY]

25-Dec-2005 1

1 To make the month name in upper case, simply use the UPPER string function.

תגים:, ,