SQL

SQL : Useful DateTime Queries

Posted on

—-Today

SELECT GETDATE() ‘Today’
—-Yesterday
SELECT DATEADD(d,-1,GETDATE()) ‘Yesterday’
—-First Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) ‘First Day of Current Week’
—-Last Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6) ‘Last Day of Current Week’
—-First Day of Last Week
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) ‘First Day of Last Week’
—-Last Day of Last Week
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6) ‘Last Day of Last Week’
—-First Day of Current Month
SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) ‘First Day of Current Month’
—-Last Day of Current Month
SELECT DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))) ‘Last Day of Current Month’
—-First Day of Last Month
SELECT DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) ‘First Day of Last Month’
—-Last Day of Last Month
SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))) ‘Last Day of Last Month’
—-First Day of Current Year
SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0) ‘First Day of Current Year’
—-Last Day of Current Year
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0))) ‘Last Day of Current Year’
—-First Day of Last Year
SELECT DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) ‘First Day of Last Year’
—-Last Day of Last Year
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))) ‘Last Day of Last Year’
—-First Day Of Current Quarter
SELECT Dateadd(qq, Datediff(qq,0,GetDate()), 0)’First Day Of Current Quarter’
—-Last Day Of Current Quarter
SELECT Dateadd(ms,-3,Dateadd(qq, Datediff(qq,0,GetDate())+1, 0)) ‘Last Day Of Current Quarter’
—-First Day Of Prior Quarter
SELECT Dateadd(qq, Datediff(qq,0,GetDate())-1, 0) ‘First Day Of Prior Quarter’
—-Last Day Of Prior Quarter
SELECT Dateadd(ms,-3,Dateadd(qq, Datediff(qq,0,GetDate()), 0)) ‘Last Day Of Prior Quarter’

This entry was posted in SQL, SQL Server.

Microsoft SQL Server Report Viewer Control Redistributable Packages and Sample Applications

Posted on


Microsoft Report Viewer control enables applications that run on the .NET Framework to display reports designed using Microsoft reporting technology.
The redistributable packages contain Windows Forms and ASP.NET Web server control versions of the Report Viewer.
You can find here download links of Report Viewer control redistributable packages for MS SQL Server 2005 and Microsoft SQL Server 2008 and sample applications using reportviewer control for Visual Studio 2005 and Visual Studio 2008.
Microsoft Report Viewer Redistributable 2008
File name : ReportViewer.exe
Version : 09.00.21022.08
Download Size : 2.8 MB
Microsoft Report Viewer Redistributable 2005
File name : ReportViewer.exe
Version : 2007
Download Size : 1.8 MB
Microsoft Report Viewer Redistributable 2005 SP1 (Upgrade)
File name : VS80-KB933137-X86.exe
Version : 1
Download Size : 1.7 MB
Microsoft Report Viewer Redistributable 2005 SP1 (Full Installation)
File name : ReportViewer.exe
Version : 1.0
Download Size : 1.8 MB
ReportViewer Samples for Microsoft Visual Studio 2008
File name : ReportViewerSamples2008.exe
Version : 1.0
Download Size : 172 KB
ReportViewer Samples for Visual Studio 2005
File name : ReportViewerSamples.exe
Version : 1.0
Download Size : 173 KB

This entry was posted in SQL.

Standard Numeric Format Strings in SQL

Posted on


Article Submitted by Madankumar Kasthuri
Standard numeric format strings are used to format common numeric types. A standard format string takes the form Axx where A is a single alphabetic character called the format specifier, and xx is an optional integer called the precision specifier. The format specifier must be one of the built-in format characters. The precision specifier ranges from 0 to 99 and controls the number of significant digits or zeros to the right of a decimal. The format string cannot contain white spaces.
If the format string does not contain one of the standard format specifiers, then a FormatException is thrown. For example, the format string “z” is interpreted as a standard numeric format string because it contains one alphabetic character, but the alphabetic character is not one of the standard numeric format specifiers so a FormatException is thrown. Any numeric format string that does not fit the definition of a standard numeric format string is interpreted as a custom numeric format string. The format string “c!” is interpreted as a custom format string because it contains two alphabetic characters, even though the character “c” is a standard numeric format specifier.
The following table describes the standard numeric format strings. Note that the result string produced by these format specifiers is influenced by the settings in the Regional Options control panel. Computers using different settings will generate different result strings.

Format specifier Name Description
C or c Currency The number is converted to a string that represents a currency amount. The conversion is controlled by the currency format information of the NumberFormatInfo object used to format the number. The precision specifier indicates the desired number of decimal places. If the precision specifier is omitted, the default currency precision given by the NumberFormatInfo is used.
D or d Decimal This format is supported for integral types only. The number is converted to a string of decimal digits (0-9), prefixed by a minus sign if the number is negative. The precision specifier indicates the minimum number of digits desired in the resulting string. If required, the number is padded with zeros to its left to produce the number of digits given by the precision specifier.
E or e Scientific (exponential) The number is converted to a string of the form “-d.ddd…E+ddd” or “-d.ddd…e+ddd”, where each ‘d’ indicates a digit (0-9). The string starts with a minus sign if the number is negative. One digit always precedes the decimal point. The precision specifier indicates the desired number of digits after the decimal point. If the precision specifier is omitted, a default of six digits after the decimal point is used. The case of the format specifier indicates whether to prefix the exponent with an ‘E’ or an ‘e’. The exponent always consists of a plus or minus sign and a minimum of three digits. The exponent is padded with zeros to meet this minimum, if required.
F or f Fixed-point The number is converted to a string of the form “-ddd.ddd…” where each ‘d’ indicates a digit (0-9). The string starts with a minus sign if the number is negative. The precision specifier indicates the desired number of decimal places. If the precision specifier is omitted, the default numeric precision given by the NumberFormatInfo is used.
G or g General The number is converted to the most compact of either fixed-point or scientific notation, depending on the type of the number and whether a precision specifier is present. If the precision specifier is omitted or zero, the type of the number determines the default precision, as indicated by the following list.

  • Byte or SByte: 3
  • Int16 or UInt16: 5
  • Int32 or UInt32: 10
  • Int64 or UInt64: 19
  • Single: 7
  • Double: 15
  • Decimal: 29

Fixed-point notation is used if the exponent that would result from expressing the number in scientific notation is greater than -5 and less than the precision specifier; otherwise, scientific notation is used. The result contains a decimal point if required and trailing zeroes are omitted. If the precision specifier is present and the number of significant digits in the result exceeds the specified precision, then the excess trailing digits are removed by rounding. If scientific notation is used, the exponent in the result is prefixed with ‘E’ if the format specifier is ‘G’, or ‘e’ if the format specifier is ‘g’.
The exception to the preceding rule is if the number is a Decimal and the precision specifier is omitted. In that case, fixed-point notation is always used and trailing zeroes are preserved.

N or n Number The number is converted to a string of the form “-d,ddd,ddd.ddd…”, where each ‘d’ indicates a digit (0-9). The string starts with a minus sign if the number is negative. Thousand separators are inserted between each group of three digits to the left of the decimal point. The precision specifier indicates the desired number of decimal places. If the precision specifier is omitted, the default numeric precision given by the NumberFormatInfo is used.
P or p Percent The number is converted to a string that represents a percent as defined by the NumberFormatInfo.PercentNegativePattern property or the NumberFormatInfo.PercentPositivePattern property. If the number is negative, the string produced is defined by the PercentNegativePattern and starts with a minus sign. The converted number is multiplied by 100 in order to be presented as a percentage. The precision specifier indicates the desired number of decimal places. If the precision specifier is omitted, the default numeric precision given by NumberFormatInfo is used.
R or r Round-trip The round-trip specifier guarantees that a numeric value converted to a string will be parsed back into the same numeric value. When a numeric value is formatted using this specifier, it is first tested using the general format, with 15 spaces of precision for a Double and 7 spaces of precision for a Single. If the value is successfully parsed back to the same numeric value, it is formatted using the general format specifier. However, if the value is not successfully parsed back to the same numeric value, then the value is formatted using 17 digits of precision for a Double and 9 digits of precision for a Single. Although a precision specifier can be appended to the round-trip format specifier, it is ignored. Round trips are given precedence over precision when using this specifier. This format is supported by floating-point types only.
X or x Hexadecimal The number is converted to a string of hexadecimal digits. The case of the format specifier indicates whether to use uppercase or lowercase characters for the hexadecimal digits greater than 9. For example, use ‘X’ to produce “ABCDEF”, and ‘x’ to produce “abcdef”. The precision specifier indicates the minimum number of digits desired in the resulting string. If required, the number is padded with zeros to its left to produce the number of digits given by the precision specifier. This format is supported for integral types only.

This entry was posted in SQL.

SQL Query to get Field names as Records

Posted on


    At times you may need to have the SQL table column names in runtime for your various reasons, below is the simple query to get the columns as records for any given table. Copy the below code and execute in your SQL Query window.
select column_name,* from information_schema.columns  
where table_name = ‘Your Table Name’
order by ordinal_position

For More tips on SQL query… keep comeback to this blog

This entry was posted in SQL.