-Maltrak-: 2011

miércoles, 21 de diciembre de 2011

Convertir campo de texto en un picklist

Esta función esta pensada en que puedas guardar los valores en un campo de texto, a partir de un picklist llenado dinamicamente con un webservice.

Lo que se hace es convertir el campo de texto en un picklist, luego lo llenas con el webservice y cuando se guarde el formulario se salvaran los datos en el campo de texto.

function ConvertTextToPickList( controlId )

{

var textControl = document.getElementById( controlId );
var picklistControl = document.createElement( "SELECT" );
picklistControl.id = textControl.id;
picklistControl.req = textControl.req;
picklistControl.className = "ms-crm-selectBox ";
picklistControl.value = textControl.DataValue;
textControl.parentElement.appendChild( picklistControl );
textControl.parentElement.removeChild( textControl );
return picklistControl ;

}

jueves, 1 de diciembre de 2011

xmlhttp request CRM, for a webservice Method.

How to load a .js File to Dynamics CRM

function load_script (url)
{
var x = new ActiveXObject("Msxml2.XMLHTTP");
x.open('GET', url, false); x.send('');
eval(x.responseText);
var s = x.responseText.split(/\n/);
var r = /^function\s*([a-z_]+)/i;
for (var i = 0; i < s.length; i++)
{
var m = r.exec(s[i]);
if (m != null)
window[m[1]] = eval(m[1]);
}
}

load_script("/_customscript/jquery-1.2.6.min.js");
load_script("/_customscript/jqModal.js");
load_script("/_customscript/jqDnR.js");
load_script("/_customscript/customscript.js");

lunes, 31 de octubre de 2011

# Could not load file or assembly 'Microsoft.ReportViewer.ProcessingObjectModel, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified

You can try copying the ReportViewer assemblies manually. Normally if the ReportViewer.exe successfully completes the setup process, the following files should be placed within the following folder :

C:\Program Files\Microsoft Visual Studio 8\ReportViewer or
C:\Program Files\Microsoft Visual Studio 9.0\ReportViewer

Microsoft.ReportViewer.Common.dll

Microsoft.ReportViewer.WebForms.dll

Microsoft.ReportViewer.WebForms.xml

Microsoft.ReportViewer.WinForms.dll

Microsoft.ReportViewer.WinForms.xml

You can manually copy the following dll files into the application folder /bin directory. After copying the files into the application bin folder the web application will start responding successfully. And ASP.NET ReportViewer control will work on your web application.

Bytes!! :)

miércoles, 12 de octubre de 2011

Solomon Crystal reports printer error 513: Printer undefined

The 513 error message usually occurs when Dynamics SL cannot find the printer.

If the printer appears to be fine, then try logging out of Dynamics  log back into Dynamics SL
and test printing.

You might want to check your Windows Default printer to make certain that there are no issues. You might also delete and recreate your printer in case the printer corrupted.

Or if yu're using Citrix check if the name of printer is too long and try to put a short name, and it work's.

bytes,

domingo, 9 de octubre de 2011

Troubleshooting: Common failure reasons while deploying applications to the BlackBerry PlayBook simulator

As described in the BlackBerry Tablet OS Application development guides (Getting Started – Adobe AIR; Getting Started - BlackBerry WebWorks), deploying an *.bar BlackBerry® PlayBook™ Tablet application to the BlackBerry® Tablet Simulator requires the following 2 steps:

Enable development mode on the target Simulator
Use the blackberry-deploy service, found within the BlackBerry® Tablet OS SDK, to deploy your *.bar application to the simulator:

or If You're trying with Flash Builder, and get an error like this:

actual_id::MjA5OGRhZTZkMDY2MmUxICAgICA
actual_version::1.0.0.0
result::failure
Error: Connection to 192.168.198.137 refused.

Cannot connect:Connection to https://192.168.181.129 refused. Please check Ip address settings for the target. You May have to reboot the target.

Go to the command promt and type ping to your playbook simulator

Enable your VMware NetWork Adapter.

Go again to the command promt and type ping to your playbook simulator

That's all .

bytes. !!

martes, 20 de septiembre de 2011

Como hacer un casco de DaftPunk

http://burnmexico.tv/2011/09/19/cascos-diy-de-daft-punk/

miércoles, 14 de septiembre de 2011

How to get GPS data from Playbook and save in a File and read it.

import flash.sensors.Geolocation;

import flashx.textLayout.factory.StringTextLineFactory;

import mx.events.FlexEvent;

import qnx.media.QNXStageWebView;

private var geoLocation:Geolocation;

private var ObjfiletoSave:File;

private var Objstream:FileStream = new FileStream();

private var Objstream2:FileStream = new FileStream();

private var Count:Number =0;

private var Coord: String="";

protected function application1_applicationCompleteHandler(event:FlexEvent):void {

lectura.text="file applicationdirectory: "+File.applicationDirectory.nativePath +"\n"

+"file applicationStorageDirectory: "+File.applicationStorageDirectory.nativePath +"\n"

+"file desktopDirectory: "+File.desktopDirectory.nativePath +"\n"

+"file documentsDirectory: "+File.documentsDirectory.nativePath +"\n"

+"file userDirectory: "+File.userDirectory.nativePath +"\n"

// TODO Auto-generated method stub

if(Geolocation.isSupported==true){

geoLocation = new Geolocation();

geoLocation.setRequestedUpdateInterval(1000);

geoLocation.addEventListener(GeolocationEvent.UPDATE,handleLocateRequest);

}else{

status.text="No soporta Geolocalización!"

}

private function handleLocateRequest(event:GeolocationEvent):void{

var mph:Number = event.speed * 2.23693629;

var kph:Number = event.speed * 3.6;

info.text="Actualización: " + new Date().toTimeString() + "\n\n"

+ "latitud: " + event.latitude.toString() + "\n"

+ "longitud: " + event.longitude.toString() + "\n"

+ "altitud: " + event.altitude.toString() + "\n"

+ "speed: " + event.speed.toString() + "\n"

+ "speed: " + mph.toString() + "\n"

+ "speed: " + kph.toString() + "\n"

+ "cabecera: " + event.heading.toString()+ "\n"

+ "horizontal accuracy: " + event.horizontalAccuracy.toString() + "\n"

+ "vertical accuracy: " + event.verticalAccuracy.toString() ;

SaveinTextFile(event.latitude.toString(),event.longitude.toString());

}

private function SaveinTextFile(Latitud:String,Longitud:String):void{

var Fecha:String = new Date().toTimeString();

ObjfiletoSave=File.documentsDirectory.resolvePath("samples/GPS.txt");

status.text="Salva Lectura de " + File.documentsDirectory.nativePath + "/samples/GPS.txt";

Objstream.open(ObjfiletoSave, FileMode.APPEND);

Objstream.writeUTFBytes("var Count"+ Count.toString() +"=new GLatLng("+ Latitud + "," + Longitud + "); \n" )

Coord= Coord + "Count" + Count.toString()+",";

Count ++;

}

private function ReadTextFile():void{

ObjfiletoSave=File.documentsDirectory.resolvePath("samples/GPS.txt");

status.text="Lectura de " + File.documentsDirectory.name + "/samples/GPS.txt";

var Objstream2:FileStream = new FileStream();

Objstream.open(ObjfiletoSave, FileMode.READ);

lectura.text = Objstream.readUTFBytes(Objstream.bytesAvailable);

Objstream.close();

//status.text="Lectura..." }

protected function detener_clickHandler(event:MouseEvent):void

{

// TODO Auto-generated method stub

geoLocation.removeEventListener(GeolocationEvent.UPDATE,handleLocateRequest);

status.text="Lectura detenida";

Objstream.writeUTFBytes(Coord );

Objstream.close();

}

protected function mostrar_clickHandler(event:MouseEvent):void {

// TODO Auto-generated method stub

ReadTextFile();

}

protected function continua_clickHandler(event:MouseEvent):void {

// TODO Auto-generated method stub

geoLocation = new Geolocation();

geoLocation.setRequestedUpdateInterval(1000);

geoLocation.addEventListener(GeolocationEvent.UPDATE,handleLocateRequest);

}

martes, 30 de agosto de 2011

A generic error occurred in GDI+.

If you have a troubble just like this,

[ExternalException (0x80004005): A generic error occurred in GDI+.] System.Drawing.Image.Save(String filename, ImageCodecInfo encoder, EncoderParameters encoderParams) System.Drawing.Image.Save(String filename, ImageFormat format) Infragistics.WebUI.UltraWebChart.UltraChart.Render(HtmlTextWriter output) [ImageStoreException: Unable to write chart image files at given file path. Please check and correct UltraChart's DeploymentScenario property. Or please provide write permissions to directory path where chart must save images. Please refer Deployment Scenario section in help files.

Follow the next steps.

In Windows Explorer (shortcut: Windows Key-E), browse to the web application folder or to the virtual directory that contains content (for example, D:\MyWeb\MyApplication\ChartImages).
Right-click the folder, and then click Properties.
On the Security tab, click Add. If you are running Windows XP and do not see a Security tab, you may have simple sharing enabled; you must de-activate it to assign specific security privileges, see "How to disable simplified sharing in Windows XP" for more information.
For Windows 2003 Server, type NETWORK SERVICE (for example, on a computer that is named 'Webdev', type Webdev\NETWORK SERVICE), and then click OK. For most other versions of Windows, type LocalMachineName\ASPNET (for example, on a computer that is named 'Webdev', type Webdev\ASPNET), and then click OK.
Allow the following permissions for the ASP.NET worker process account to this folder, files and all subfolders:

Full Control

Click OK to close the Properties dialog box and to save the changes.

viernes, 26 de agosto de 2011

SQL injection samples.

This is an example when a SQL injection occurs.

I pick this article off from

http://blogs.msdn.com/b/raulga/archive/2007/01/04/dynamic-sql-sql-injection.aspx

it´s not mine but is very interesting.

-- An innocent looking SP

CREATE PROC [sp_demo_injection01]( @name sysname )

-- ...with an obvious SQL injection-vulnerable sample

EXEC( 'SELECT * FROM sys.database_principals WHERE name = ''' + @name +'''' )

-- This is how it was intended to be used

declare @var sysname

SET @var = 'Some Name'

EXEC [sp_demo_injection01] @var

-- As you can see, I can easily abuse this module in the following manner

declare @var sysname

SET @var = 'Some Name''; GRANT CONTROL TO [Malicious User]; PRINT ''Game over! This system is no longer yours!''-- Malicious User now can control the database!!!'

EXEC [sp_demo_injection01] @var

When the attacker runs this query the system will concatenate the input to the command we defined in the SP:

EXEC ( 'SELECT * FROM sys.database_principals WHERE name = ''' + 'Some Name''; GRANT CONTROL TO [Malicious User]; PRINT ''Game over! This system is no longer yours!''-- Malicious User now can control the database!!!' +'''' )

The attacker is able to close the quote in the user name (notice the trailing quote in Some Name’) and converted the rest of what should have been a user name into a different SQL statement, causing the following command to be executed:

SELECT * FROM sys.database_principals WHERE name = 'Some Name'; GRANTCONTROL TO [Malicious User]; PRINT 'Game over! This system is no longer yours!'-- Malicious User now can control the database!!!'

As you can see the attacker was able to add extra SQL statements that were not intended by the author of the stored procedure, in this case granting CONTROL on the database to herself and printing a note.

Parameterization

In most of these scenarios there is an alternative to the example used above using parameterization. Using parameterization gives you the advantage that you can clearly specify the data type and avoid pitfalls as well as the final T-SQL statement generated will reference the parameters as variables and not directly use the user defined input to generate the statement.

If you are using T-SQL directly to generate dynamic SQL, you can take advantage of sp_ExecuteSql to execute parameterized queries, for example:

-- An improved version of [sp_demo_injection01]

CREATE PROC [sp_demo_injection02]( @name sysname )

declare @cmd nvarchar(max)

declare @parameters nvarchar(max)

set @cmd = N'SELECT * FROM sys.database_principals WHERE name = @name'

set @parameters = '@name sysname'

EXEC sp_executesql @cmd, @parameters, @name = @name

-- This is how it was intended to be used

declare @var sysname

SET @var = 'Some Name'

EXEC [sp_demo_injection02] @var

-- The previous attack no longer has any effect!

declare @var sysname

SET @var = 'Some Name''; GRANT CONTROL TO [Malicious User]; PRINT ''Game over! This system is no longer yours!''-- Malicious User now can control the database!!!'

EXEC [sp_demo_injection02] @var

CREATE PROC [sp_demo_injection03]( @Value nvarchar(100) )

declare @cmd nvarchar(max)

declare @parameters nvarchar(max)

set @cmd = N'SELECT * FROM sys.database_principals WHERE principal_id = @Value'

set @parameters = '@Value int'

EXEC sp_executesql @cmd, @parameters, @value = @value

-- Should work

declare @var sysname

SET @var = '1'

EXEC [sp_demo_injection03] @var

-- Expect error 8114

-- Error converting data type nvarchar to int.

declare @var sysname

SET @var = '1; select * from sys.objects'

EXEC [sp_demo_injection03] @var

But be careful, using sp_executesql is not a guarantee that the SQL statement to be executed is not susceptible to SQL injection; the parameters should be used properly in order to really take advantage of this feature. The following example is a demonstration of a common mistake I have seen a few times: constructing the @cmd parameter using user-defined data instead of using it as a parameter.

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

-- Incorrect usage of sp_executeSql

CREATE PROC [sp_demo_injection04]( @name sysname )

declare @cmd nvarchar(max)

declare @parameters nvarchar(max)

-- Looks famliar? yep, same injection as [sp_demo_injection01]

set @cmd = N'SELECT * FROM sys.database_principals WHERE name = ''' +@name + N''''

-- No parameters!!! This is typically a sign of misusing sp_execsql.

set @parameters = null

EXEC sp_executesql @cmd, @parameters

-- and now run the same attack we tried before...

declare @var sysname

SET @var = 'Some Name''; GRANT CONTROL TO [Malicious User]; PRINT ''Game over! This system is no longer yours!''-- Malicious User now can control the database!!!'

EXEC [sp_demo_injection04] @var

-- ... and it is game over!

Be aware that sp_ExecuteSql doesn’t automatically protect against every SQL injection. It helps you to create the parameterized query, but it has to be used properly in order to work. I have seen a common misuse of this stored procedure: using the user-defined input (untrusted data) to generate the @statement parameter.

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

-- Incorrect usage of sp_executeSql

CREATE PROC [sp_demo_injection04]( @name sysname )

declare @cmd nvarchar(max)

declare @parameters nvarchar(max)

-- Looks famliar? yep, same injection as [sp_demo_injection01]

set @cmd = N'SELECT * FROM sys.database_principals WHERE name = ''' +@name + N''''

-- No parameters!!! This is typically a sign of misusing sp_execsql.

set @parameters = null

EXEC sp_executesql @cmd, @parameters

-- and now run the same attack we tried before...

declare @var sysname

SET @var = 'Some Name''; GRANT CONTROL TO [Malicious User]; PRINT ''Game over! This system is no longer yours!''-- Malicious User now can control the database!!!'

EXEC [sp_demo_injection04] @var

-- ... and it is game over!

If you are using the .Net framework, you can use the SqlParameter class to create parameterized queries in a similar way, and the same warning still applies: Do not use user-defined input directly when constructing the parameterized statement. For further reference on this class, please refer tohttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqlparameterclasstopic.asp

Convert strings to date time.

Read original font click here. I took this article from sqlusa.

Execute the following T-SQL scripts in Microsoft SQL Server Manangement Studio Query Editor to demonstrate T-SQL convert and cast functions in transforming string date, string time & string datetime data to datetime data type. T-SQL date / datetime functions

-- SQL Server string to date / datetime conversion - datetime string format sql server

-- MSSQL string to datetime conversion - convert char to date - convert varchar to date

-- Subtract 100 from style number (format) for yy instead yyyy (or ccyy with century)

SELECT convert(datetime, 'Oct 23 2012 11:01AM', 100) -- mon dd yyyy hh:mmAM (or PM)

SELECT convert(datetime, 'Oct 23 2012 11:01AM') -- 2012-10-23 11:01:00.000

-- Without century (yy) string date conversion - convert string to datetime function

SELECT convert(datetime, 'Oct 23 12 11:01AM', 0) -- mon dd yy hh:mmAM (or PM)

SELECT convert(datetime, 'Oct 23 12 11:01AM') -- 2012-10-23 11:01:00.000

-- Convert string to datetime sql - convert string to date sql - sql dates format

-- T-SQL convert string to datetime - SQL Server convert string to date

SELECT convert(datetime, '10/23/2016', 101) -- mm/dd/yyyy

SELECT convert(datetime, '2016.10.23', 102) -- yyyy.mm.dd

SELECT convert(datetime, '23/10/2016', 103) -- dd/mm/yyyy

SELECT convert(datetime, '23.10.2016', 104) -- dd.mm.yyyy

SELECT convert(datetime, '23-10-2016', 105) -- dd-mm-yyyy

-- mon types are nondeterministic conversions, dependent on language setting

SELECT convert(datetime, '23 OCT 2016', 106) -- dd mon yyyy

SELECT convert(datetime, 'Oct 23, 2016', 107) -- mon dd, yyyy

-- 2016-10-23 00:00:00.000

SELECT convert(datetime, '20:10:44', 108) -- hh:mm:ss

-- 1900-01-01 20:10:44.000

-- mon dd yyyy hh:mm:ss:mmmAM (or PM) - sql time format - SQL Server datetime format

SELECT convert(datetime, 'Oct 23 2016 11:02:44:013AM', 109)

-- 2016-10-23 11:02:44.013

SELECT convert(datetime, '10-23-2016', 110) -- mm-dd-yyyy

SELECT convert(datetime, '2016/10/23', 111) -- yyyy/mm/dd

-- YYYYMMDD ISO date format works at any language setting - international standard

SELECT convert(datetime, '20161023')

SELECT convert(datetime, '20161023', 112) -- yyyymmdd

-- 2016-10-23 00:00:00.000

SELECT convert(datetime, '23 Oct 2016 11:02:07:577', 113) -- dd mon yyyy hh:mm:ss:mmm

-- 2016-10-23 11:02:07.577

SELECT convert(datetime, '20:10:25:300', 114) -- hh:mm:ss:mmm(24h)

-- 1900-01-01 20:10:25.300

SELECT convert(datetime, '2016-10-23 20:44:11', 120) -- yyyy-mm-dd hh:mm:ss(24h)

-- 2016-10-23 20:44:11.000

SELECT convert(datetime, '2016-10-23 20:44:11.500', 121) -- yyyy-mm-dd hh:mm:ss.mmm

-- 2016-10-23 20:44:11.500

-- Style 126 is ISO 8601 format: international standard - works with any language setting

SELECT convert(datetime, '2008-10-23T18:52:47.513', 126) -- yyyy-mm-ddThh:mm:ss(.mmm)

-- 2008-10-23 18:52:47.513

-- Convert DDMMYYYY format to datetime - sql server to date / datetime

SELECT convert(datetime, STUFF(STUFF('31012016',3,0,'-'),6,0,'-'), 105)

-- 2016-01-31 00:00:00.000

-- SQL Server T-SQL string to datetime conversion without century - some exceptions

-- nondeterministic means language setting dependent such as Mar/Mär/mars/márc

SELECT convert(datetime, 'Oct 23 16 11:02:44AM') -- Default

SELECT convert(datetime, '10/23/16', 1) -- mm/dd/yy U.S.

SELECT convert(datetime, '16.10.23', 2) -- yy.mm.dd ANSI

SELECT convert(datetime, '23/10/16', 3) -- dd/mm/yy UK/FR

SELECT convert(datetime, '23.10.16', 4) -- dd.mm.yy German

SELECT convert(datetime, '23-10-16', 5) -- dd-mm-yy Italian

SELECT convert(datetime, '23 OCT 16', 6) -- dd mon yy non-det.

SELECT convert(datetime, 'Oct 23, 16', 7) -- mon dd, yy non-det.

SELECT convert(datetime, '20:10:44', 8) -- hh:mm:ss

SELECT convert(datetime, 'Oct 23 16 11:02:44:013AM', 9) -- Default with msec

SELECT convert(datetime, '10-23-16', 10) -- mm-dd-yy U.S.

SELECT convert(datetime, '16/10/23', 11) -- yy/mm/dd Japan

SELECT convert(datetime, '161023', 12) -- yymmdd ISO

SELECT convert(datetime, '23 Oct 16 11:02:07:577', 13) -- dd mon yy hh:mm:ss:mmm EU dflt

SELECT convert(datetime, '20:10:25:300', 14) -- hh:mm:ss:mmm(24h)

SELECT convert(datetime, '2016-10-23 20:44:11',20) -- yyyy-mm-dd hh:mm:ss(24h) ODBC can.

SELECT convert(datetime, '2016-10-23 20:44:11.500', 21)-- yyyy-mm-dd hh:mm:ss.mmm ODBC

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

-- SQL Datetime Data Type: Combine date & time string into datetime - sql hh mm ss

-- String to datetime - mssql datetime - sql convert date - sql concatenate string

DECLARE @DateTimeValue varchar(32), @DateValue char(8), @TimeValue char(6)

SELECT @DateValue = '20120718',

@TimeValue = '211920'

SELECT @DateTimeValue =

convert(varchar, convert(datetime, @DateValue), 111)

+ ' ' + substring(@TimeValue, 1, 2)

+ ':' + substring(@TimeValue, 3, 2)

+ ':' + substring(@TimeValue, 5, 2)

SELECT

DateInput = @DateValue, TimeInput = @TimeValue,

DateTimeOutput = @DateTimeValue;

DateInput TimeInput DateTimeOutput

20120718 211920 2012/07/18 21:19:20 */

/* Datetime 8 bytes internal storage structure
o 1st 4 bytes: number of days after the base date 1900-01-01

o 2nd 4 bytes: number of clock-ticks (3.33 milliseconds) since midnight

Smalldatetime 4 bytes internal storage structure
o 1st 2 bytes: number of days after the base date 1900-01-01

o 2nd 2 bytes: number of minutes since midnight

SELECT CONVERT(binary(8), getdate()) -- 0x00009E4D 00C01272

SELECT CONVERT(binary(4), convert(smalldatetime,getdate())) -- 0x9E4D 02BC
*/

-- SQL convert seconds to HH:MM:SS - sql times format - sql hh mm

DECLARE @Seconds INT

SET @Seconds = 20000

SELECT HH = @Seconds / 3600, MM = (@Seconds%3600) / 60, SS = (@Seconds%60)

/* HH MM SS

5 33 20 */

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

-- SQL Server Date Only from DATETIME column - get date only

-- T-SQL just date - truncate time from datetime - remove time part

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

DECLARE @Now datetime = CURRENT_TIMESTAMP -- getdate()

SELECT DateAndTime = @Now -- Date portion and Time portion

,DateString = REPLACE(LEFT(CONVERT (varchar, @Now, 112),10),' ','-')

,[Date] = CONVERT(DATE, @Now) -- SQL Server 2008 and on - date part

,Midnight1 = dateadd(day, datediff(day,0, @Now), 0)

,Midnight2 = CONVERT(DATETIME,CONVERT(int, @Now))

,Midnight3 = CONVERT(DATETIME,CONVERT(BIGINT,@Now) & (POWER(Convert(bigint,2),32)-1))

/* DateAndTime DateString Date Midnight1 Midnight2 Midnight3

2010-11-02 08:00:33.657 20101102 2010-11-02 2010-11-02 00:00:00.000 2010-11-02 00:00:00.000 2010-11-02 00:00:00.000 */

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

-- SQL Server 2008 convert datetime to date - sql yyyy mm dd

SELECT TOP (3) OrderDate = CONVERT(date, OrderDate),

Today = CONVERT(date, getdate())

FROM AdventureWorks2008.Sales.SalesOrderHeader

ORDER BY newid();

/* OrderDate Today

2004-02-15 2012-06-18 .....*/

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

-- SQL date yyyy mm dd - sqlserver yyyy mm dd - date format yyyymmdd

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

/* YYYY/MM/DD

2015/07/11 */

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

/* YYYYMMDD

20150711 */

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

/* YYYY MM DD

2015 07 11 */
-- Converting to special (non-standard) date fomats: DD-MMM-YY
SELECT UPPER(REPLACE(CONVERT(VARCHAR,GETDATE(),6),' ','-'))
-- 07-MAR-14
------------

-- SQL convert date string to datetime - time set to 00:00:00.000 or 12:00AM

PRINT CONVERT(datetime,'07-10-2012',110) -- Jul 10 2012 12:00AM

PRINT CONVERT(datetime,'2012/07/10',111) -- Jul 10 2012 12:00AM

PRINT CONVERT(datetime,'20120710', 112) -- Jul 10 2012 12:00AM

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

-- String to date conversion - sql date yyyy mm dd - sql date formatting

-- SQL Server cast string to date - sql convert date to datetime

SELECT [Date] = CAST (@DateValue AS datetime)

-- 2012-07-18 00:00:00.000

-- SQL convert string date to different style - sql date string formatting

SELECT CONVERT(varchar, CONVERT(datetime, '20140508'), 100)

-- May 8 2014 12:00AM

-- SQL Server convert date to integer

DECLARE @Date datetime; SET @Date = getdate();

SELECT DateAsInteger = CAST (CONVERT(varchar,@Date,112) as INT);

-- Result: 20161225

-- SQL Server convert integer to datetime

DECLARE @iDate int

SET @iDate = 20151225

SELECT IntegerToDatetime = CAST(convert(varchar,@iDate) as datetime)

-- 2015-12-25 00:00:00.000

-- Alternates: date-only datetime values

-- SQL Server floor date - sql convert datetime

SELECT [DATE-ONLY]=CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE())))

SELECT [DATE-ONLY]=CONVERT(DATETIME, FLOOR(CONVERT(MONEY, GETDATE())))

-- SQL Server cast string to datetime

-- SQL Server datetime to string convert

SELECT [DATE-ONLY]=CAST(CONVERT(varchar, GETDATE(), 101) AS DATETIME)

-- SQL Server dateadd function - T-SQL datediff function

-- SQL strip time from date - MSSQL strip time from datetime

SELECT getdate() ,dateadd(dd, datediff(dd, 0, getdate()), 0)

-- Results: 2016-01-23 05:35:52.793 2016-01-23 00:00:00.000

-- String date - 10 bytes of storage

SELECT [STRING DATE]=CONVERT(varchar, GETDATE(), 110)

SELECT [STRING DATE]=CONVERT(varchar, CURRENT_TIMESTAMP, 110)

-- Same results: 01-02-2012

-- SQL Server cast datetime as string - sql datetime formatting

SELECT stringDateTime=CAST (getdate() as varchar) -- Dec 29 2012 3:47AM

----------

-- SQL date range BETWEEN operator

----------

-- SQL date range select - date range search - T-SQL date range query

-- Count Sales Orders for 2003 OCT-NOV

DECLARE @StartDate DATETIME, @EndDate DATETIME

SET @StartDate = convert(DATETIME,'10/01/2003',101)

SET @EndDate = convert(DATETIME,'11/30/2003',101)

SELECT @StartDate, @EndDate

-- 2003-10-01 00:00:00.000 2003-11-30 00:00:00.000

SELECT dateadd(DAY,1,@EndDate),

dateadd(ms,-3,dateadd(DAY,1,@EndDate))

-- 2003-12-01 00:00:00.000 2003-11-30 23:59:59.997

-- MSSQL date range select using >= and <

SELECT [Sales Orders for 2003 OCT-NOV] = COUNT(* )

FROM Sales.SalesOrderHeader

WHERE OrderDate >= @StartDate AND OrderDate < dateadd(DAY,1,@EndDate)

/* Sales Orders for 2003 OCT-NOV

3668 */

-- Equivalent date range query using BETWEEN comparison

-- It requires a bit of trick programming

SELECT [Sales Orders for 2003 OCT-NOV] = COUNT(* )

FROM Sales.SalesOrderHeader

WHERE OrderDate BETWEEN @StartDate AND dateadd(ms,-3,dateadd(DAY,1,@EndDate))

-- 3668

USE AdventureWorks;

-- SQL between string dates

SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader

WHERE OrderDate BETWEEN '20040201' AND '20040210' -- Result: 108

-- SQL BETWEEN dates without time - time stripped - time removed - date part only

SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader

WHERE datediff(dd,0,OrderDate)

BETWEEN datediff(dd,0,'20040201 12:11:39') AND datediff(dd,0,'20040210 14:33:19')

-- 108

-- BETWEEN is equivalent to >=...AND....<=

SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader

WHERE OrderDate

BETWEEN '2004-02-01 00:00:00.000' AND '2004-02-10 00:00:00.000'

/* Orders with OrderDates

'2004-02-10 00:00:01.000' - 1 second after midnight (12:00AM)

'2004-02-10 00:01:00.000' - 1 minute after midnight

'2004-02-10 01:00:00.000' - 1 hour after midnight

are not included in the two queries above. */

-- To include the entire day of 2004-02-10 use:

SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader

WHERE OrderDate >= '20040201' AND OrderDate < '20040211'

----------

-- Calculate week ranges in a year

----------

DECLARE @Year INT = '2016';

WITH cteDays AS (SELECT DayOfYear=Dateadd(dd, number,

CONVERT(DATE, CONVERT(char(4),@Year)+'0101'))

FROM master.dbo.spt_values WHERE type='P'),

CTE AS (SELECT DayOfYear, WeekOfYear=DATEPART(week,DayOfYear)

FROM cteDays WHERE YEAR(DayOfYear)= @YEAR)

SELECT WeekOfYear, StartOfWeek=MIN(DayOfYear), EndOfWeek=MAX(DayOfYear)

FROM CTE GROUP BY WeekOfYear ORDER BY WeekOfYear

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

-- Date validation function ISDATE - returns 1 or 0 - SQL datetime functions

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

DECLARE @StringDate varchar(32)

SET @StringDate = '2011-03-15 18:50'

IF EXISTS( SELECT * WHERE ISDATE(@StringDate) = 1)

PRINT 'VALID DATE: ' + @StringDate

ELSE

PRINT 'INVALID DATE: ' + @StringDate

-- Result: VALID DATE: 2011-03-15 18:50

DECLARE @StringDate varchar(32)

SET @StringDate = '20112-03-15 18:50'

IF EXISTS( SELECT * WHERE ISDATE(@StringDate) = 1)

PRINT 'VALID DATE: ' + @StringDate

ELSE PRINT 'INVALID DATE: ' + @StringDate

-- Result: INVALID DATE: 20112-03-15 18:50

-- First and last day of date periods - SQL Server 2008 and on code

DECLARE @Date DATE = '20161023'

SELECT ReferenceDate = @Date

SELECT FirstDayOfYear = CONVERT(DATE, dateadd(yy, datediff(yy,0, @Date),0))

SELECT LastDayOfYear = CONVERT(DATE, dateadd(yy, datediff(yy,0, @Date)+1,-1))

SELECT FDofSemester = CONVERT(DATE, dateadd(qq,((datediff(qq,0,@Date)/2)*2),0))

SELECT LastDayOfSemester

= CONVERT(DATE, dateadd(qq,((datediff(qq,0,@Date)/2)*2)+2,-1))

SELECT FirstDayOfQuarter = CONVERT(DATE, dateadd(qq, datediff(qq,0, @Date),0))

-- 2016-10-01

SELECT LastDayOfQuarter = CONVERT(DATE, dateadd(qq, datediff(qq,0,@Date)+1,-1))

-- 2016-12-31

SELECT FirstDayOfMonth = CONVERT(DATE, dateadd(mm, datediff(mm,0, @Date),0))

SELECT LastDayOfMonth = CONVERT(DATE, dateadd(mm, datediff(mm,0, @Date)+1,-1))

SELECT FirstDayOfWeek = CONVERT(DATE, dateadd(wk, datediff(wk,0, @Date),0))

SELECT LastDayOfWeek = CONVERT(DATE, dateadd(wk, datediff(wk,0, @Date)+1,-1))

-- 2016-10-30

-- Month sequence generator - sequential numbers / dates

DECLARE @Date date = '2000-01-01'

SELECT MonthStart=dateadd(MM, number, @Date)

FROM master.dbo.spt_values

WHERE type='P' AND dateadd(MM, number, @Date) <= CURRENT_TIMESTAMP

ORDER BY MonthStart

/* MonthStart

2000-01-01

2000-02-01

2000-03-01 ....*/

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

-- Selected named date styles
------------

DECLARE @DateTimeValue varchar(32)

-- US-Style

SELECT @DateTimeValue = '10/23/2016'

SELECT StringDate=@DateTimeValue,

[US-Style] = CONVERT(datetime, @DatetimeValue)

SELECT @DateTimeValue = '10/23/2016 23:01:05'

SELECT StringDate = @DateTimeValue,

[US-Style] = CONVERT(datetime, @DatetimeValue)

-- UK-Style, British/French - convert string to datetime sql

-- sql convert string to datetime

SELECT @DateTimeValue = '23/10/16 23:01:05'

SELECT StringDate = @DateTimeValue,

[UK-Style] = CONVERT(datetime, @DatetimeValue, 3)

SELECT @DateTimeValue = '23/10/2016 04:01 PM'

SELECT StringDate = @DateTimeValue,

[UK-Style] = CONVERT(datetime, @DatetimeValue, 103)

-- German-Style

SELECT @DateTimeValue = '23.10.16 23:01:05'

SELECT StringDate = @DateTimeValue,

[German-Style] = CONVERT(datetime, @DatetimeValue, 4)

SELECT @DateTimeValue = '23.10.2016 04:01 PM'

SELECT StringDate = @DateTimeValue,

[German-Style] = CONVERT(datetime, @DatetimeValue, 104)

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

-- Double conversion to US-Style 107 with century: Oct 23, 2016

SET @DateTimeValue='10/23/16'

SELECT StringDate=@DateTimeValue,

[US-Style] = CONVERT(varchar, CONVERT(datetime, @DateTimeValue),107)

-- Using DATEFORMAT - UK-Style - SQL dateformat

SET @DateTimeValue='23/10/16'

SET DATEFORMAT dmy

SELECT StringDate=@DateTimeValue,

[Date Time] = CONVERT(datetime, @DatetimeValue)

-- Using DATEFORMAT - US-Style

SET DATEFORMAT mdy

-- Convert date string from DD/MM/YYYY UK format to MM/DD/YYYY US format
DECLARE @UKdate char(10) = '15/03/2016'
SELECT CONVERT(CHAR(10), CONVERT(datetime, @UKdate,103),101)

-- 03/15/2016

-- DATEPART datetime function example - SQL Server datetime functions

SELECT * FROM Northwind.dbo.Orders

WHERE DATEPART(YEAR, OrderDate) = '1996' AND

DATEPART(MONTH,OrderDate) = '07' AND

DATEPART(DAY, OrderDate) = '10'

-- Alternate syntax for DATEPART example

SELECT * FROM Northwind.dbo.Orders

WHERE YEAR(OrderDate) = '1996' AND

MONTH(OrderDate) = '07' AND

DAY(OrderDate) = '10'

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

-- T-SQL calculate the number of business days function / UDF - exclude SAT & SUN

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

CREATE FUNCTION fnBusinessDays (@StartDate DATETIME, @EndDate DATETIME)

RETURNS INT AS

BEGIN

IF (@StartDate IS NULL OR @EndDate IS NULL) RETURN (0)

DECLARE @i INT = 0;

WHILE (@StartDate <= @EndDate)

BEGIN

SET @i = @i + CASE

WHEN datepart(dw,@StartDate) BETWEEN 2 AND 6 THEN 1

ELSE 0

END

SET @StartDate = @StartDate + 1

END -- while

RETURN (@i)

END -- function

SELECT dbo.fnBusinessDays('2016-01-01','2016-12-31')

-- 261

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

-- T-SQL DATENAME function usage for weekdays

SELECT DayName=DATENAME(weekday, OrderDate), SalesPerWeekDay = COUNT(*)

FROM AdventureWorks2008.Sales.SalesOrderHeader

GROUP BY DATENAME(weekday, OrderDate), DATEPART(weekday,OrderDate)

ORDER BY DATEPART(weekday,OrderDate)

/* DayName SalesPerWeekDay

Sunday 4482

Monday 4591

Tuesday 4346.... */

-- DATENAME application for months

SELECT MonthName=DATENAME(month, OrderDate), SalesPerMonth = COUNT(*)

FROM AdventureWorks2008.Sales.SalesOrderHeader

GROUP BY DATENAME(month, OrderDate), MONTH(OrderDate) ORDER BY MONTH(OrderDate)

/* MonthName SalesPerMonth

January 2483

February 2686

March 2750

April 2740.... */

-- Getting month name from month number

SELECT DATENAME(MM,dateadd(MM,7,-1)) -- July

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

-- Extract string date from text with PATINDEX pattern matching

-- Apply sql server string to date conversion

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

USE tempdb;

CREATE TABLE InsiderTransaction (

InsiderTransactionID int identity primary key,

TradeDate datetime,

TradeMsg varchar(256),

ModifiedDate datetime default (getdate()))

-- Populate table with dummy data