How to format datetime & date in Sql Server 2005

Execute the following Microsoft SQL Server T-SQL datetime and date formatting scripts in Management Studio Query Editor to demonstrate the multitude of temporal data formats available in SQL Server.

First we start with the conversion options available for sql datetime formats with century (YYYY or CCYY format). Subtracting 100 from the Style (format) number will transform dates without century (YY). For example Style 103 is with century, Style 3 is without century. The default Style values – Style 0 or 100, 9 or 109, 13 or 113, 20 or 120, and 21 or 121 – always return the century (yyyy) format.

 

– Microsoft SQL Server T-SQL date and datetime formats

– Date time formats – mssql datetime

– MSSQL getdate returns current system date and time in standard internal format

SELECT convert(varchar, getdate(), 100) – mon dd yyyy hh:mmAM (or PM)

– Oct  2 2008 11:01AM

SELECT convert(varchar, getdate(), 101) – mm/dd/yyyy - 10/02/2008

SELECT convert(varchar, getdate(), 102) – yyyy.mm.dd – 2008.10.02

SELECT convert(varchar, getdate(), 103) – dd/mm/yyyy

SELECT convert(varchar, getdate(), 104) – dd.mm.yyyy

SELECT convert(varchar, getdate(), 105) – dd-mm-yyyy

SELECT convert(varchar, getdate(), 106) – dd mon yyyy

SELECT convert(varchar, getdate(), 107) – mon dd, yyyy

SELECT convert(varchar, getdate(), 108) – hh:mm:ss

SELECT convert(varchar, getdate(), 109) – mon dd yyyy hh:mm:ss:mmmAM (or PM)

– Oct  2 2008 11:02:44:013AM

SELECT convert(varchar, getdate(), 110) – mm-dd-yyyy

SELECT convert(varchar, getdate(), 111) – yyyy/mm/dd

SELECT convert(varchar, getdate(), 112) – yyyymmdd

SELECT convert(varchar, getdate(), 113) – dd mon yyyy hh:mm:ss:mmm

– 02 Oct 2008 11:02:07:577

SELECT convert(varchar, getdate(), 114) – hh:mm:ss:mmm(24h)

SELECT convert(varchar, getdate(), 120) – yyyy-mm-dd hh:mm:ss(24h)

SELECT convert(varchar, getdate(), 121) – yyyy-mm-dd hh:mm:ss.mmm

SELECT convert(varchar, getdate(), 126) – yyyy-mm-ddThh:mm:ss.mmm

– 2008-10-02T10:52:47.513

– SQL create different date styles with t-sql string functions

SELECT replace(convert(varchar, getdate(), 111), ‘/’, ‘ ‘) – yyyy mm dd

SELECT convert(varchar(7), getdate(), 126)                 – yyyy-mm

SELECT right(convert(varchar, getdate(), 106), 8)          – mon yyyy

————

– SQL Server date formatting function – convert datetime to string

————

– SQL datetime functions

– SQL Server date formats

– T-SQL convert dates

– Formatting dates sql server

CREATE FUNCTION dbo.fnFormatDate (@Datetime DATETIME, @FormatMask VARCHAR(32))

RETURNS VARCHAR(32)

AS

BEGIN

DECLARE @StringDate VARCHAR(32)

SET @StringDate = @FormatMask

IF (CHARINDEX (‘YYYY’,@StringDate) > 0)

SET @StringDate = REPLACE(@StringDate, ‘YYYY’,

DATENAME(YY, @Datetime))

IF (CHARINDEX (‘YY’,@StringDate) > 0)

SET @StringDate = REPLACE(@StringDate, ‘YY’,

RIGHT(DATENAME(YY, @Datetime),2))

IF (CHARINDEX (‘Month’,@StringDate) > 0)

SET @StringDate = REPLACE(@StringDate, ‘Month’,

DATENAME(MM, @Datetime))

IF (CHARINDEX (‘MON’,@StringDate COLLATE SQL_Latin1_General_CP1_CS_AS)>0)

SET @StringDate = REPLACE(@StringDate, ‘MON’,

LEFT(UPPER(DATENAME(MM, @Datetime)),3))

IF (CHARINDEX (‘Mon’,@StringDate) > 0)

SET @StringDate = REPLACE(@StringDate, ‘Mon’,

LEFT(DATENAME(MM, @Datetime),3))

IF (CHARINDEX (‘MM’,@StringDate) > 0)

SET @StringDate = REPLACE(@StringDate, ‘MM’,

RIGHT(’0′+CONVERT(VARCHAR,DATEPART(MM, @Datetime)),2))

IF (CHARINDEX (‘M’,@StringDate) > 0)

SET @StringDate = REPLACE(@StringDate, ‘M’,

CONVERT(VARCHAR,DATEPART(MM, @Datetime)))

IF (CHARINDEX (‘DD’,@StringDate) > 0)

SET @StringDate = REPLACE(@StringDate, ‘DD’,

RIGHT(’0′+DATENAME(DD, @Datetime),2))

IF (CHARINDEX (‘D’,@StringDate) > 0)

SET @StringDate = REPLACE(@StringDate, ‘D’,

DATENAME(DD, @Datetime))

RETURN @StringDate

END

GO

 

– Microsoft SQL Server date format function test

– MSSQL formatting dates

SELECT dbo.fnFormatDate (getdate(), ‘MM/DD/YYYY’)           – 01/03/2012

SELECT dbo.fnFormatDate (getdate(), ‘DD/MM/YYYY’)           – 03/01/2012

SELECT dbo.fnFormatDate (getdate(), ‘M/DD/YYYY’)            – 1/03/2012

SELECT dbo.fnFormatDate (getdate(), ‘M/D/YYYY’)             – 1/3/2012

SELECT dbo.fnFormatDate (getdate(), ‘M/D/YY’)               – 1/3/12

SELECT dbo.fnFormatDate (getdate(), ‘MM/DD/YY’)             – 01/03/12

SELECT dbo.fnFormatDate (getdate(), ‘MON DD, YYYY’)         – JAN 03, 2012

SELECT dbo.fnFormatDate (getdate(), ‘Mon DD, YYYY’)         – Jan 03, 2012

SELECT dbo.fnFormatDate (getdate(), ‘Month DD, YYYY’)       – January 03, 2012

SELECT dbo.fnFormatDate (getdate(), ‘YYYY/MM/DD’)           – 2012/01/03

SELECT dbo.fnFormatDate (getdate(), ‘YYYYMMDD’)             – 20120103

SELECT dbo.fnFormatDate (getdate(), ‘YYYY-MM-DD’)           – 2012-01-03

– CURRENT_TIMESTAMP returns current system date and time in standard internal format

SELECT dbo.fnFormatDate (CURRENT_TIMESTAMP,‘YY.MM.DD’)      – 12.01.03

GO

————

 

/***** SELECTED SQL DATE/DATETIME FORMATS WITH NAMES *****/

 

– SQL format datetime

– Default format: Oct 23 2006 10:40AM

SELECT [Default]=CONVERT(varchar,GETDATE(),100)

 

– US-Style format: 10/23/2006

SELECT [US-Style]=CONVERT(char,GETDATE(),101)

 

– ANSI format: 2006.10.23

SELECT [ANSI]=CONVERT(char,CURRENT_TIMESTAMP,102)

 

– UK-Style format: 23/10/2006

SELECT [UK-Style]=CONVERT(char,GETDATE(),103)

 

– German format: 23.10.2006

SELECT [German]=CONVERT(varchar,GETDATE(),104)

 

– ISO format: 20061023

SELECT ISO=CONVERT(varchar,GETDATE(),112)

 

– ISO8601 format: 2008-10-23T19:20:16.003

SELECT [ISO8601]=CONVERT(varchar,GETDATE(),126)

————

 

– SQL Server datetime formats

– Century date format MM/DD/YYYY usage in a query

– Format dates SQL Server 2005

SELECT TOP (1)

SalesOrderID,

OrderDate = CONVERT(char(10), OrderDate, 101),

OrderDateTime = OrderDate

FROM AdventureWorks.Sales.SalesOrderHeader

/* Result

 

SalesOrderID      OrderDate               OrderDateTime

43697             07/01/2001          2001-07-01 00:00:00.000

*/

 

– SQL update datetime column

– SQL datetime DATEADD

UPDATE Production.Product

SET ModifiedDate=DATEADD(dd,1, ModifiedDate)

WHERE ProductID = 1001

 

– MM/DD/YY date format

– Datetime format sql

SELECT TOP (1)

SalesOrderID,

OrderDate = CONVERT(varchar(8), OrderDate, 1),

OrderDateTime = OrderDate

FROM AdventureWorks.Sales.SalesOrderHeader

ORDER BY SalesOrderID desc

/* Result

 

SalesOrderID      OrderDate         OrderDateTime

75123             07/31/04          2004-07-31 00:00:00.000

*/

 

– Combining different style formats for date & time

– Datetime formats

– Datetime formats sql

DECLARE @Date DATETIME

SET @Date = ’2015-12-22 03:51 PM’

SELECT CONVERT(CHAR(10),@Date,110) + SUBSTRING(CONVERT(varchar,@Date,0),12,8)

– Result: 12-22-2015  3:51PM

 

– Microsoft SQL Server cast datetime to string

SELECT stringDateTime=CAST (getdate() as varchar)

– Result: Dec 29 2012  3:47AM

————

– SQL Server date and time functions overview

————

– SQL Server CURRENT_TIMESTAMP function

– SQL Server datetime functions

– local NYC – EST – Eastern Standard Time zone

– SQL DATEADD function – SQL DATEDIFF function

SELECT CURRENT_TIMESTAMP                        – 2012-01-05 07:02:10.577

– SQL Server DATEADD function

SELECT DATEADD(month,2,’2012-12-09′)            – 2013-02-09 00:00:00.000

– SQL Server DATEDIFF function

SELECT DATEDIFF(day,’2012-12-09′,’2013-02-09′)  – 62

– SQL Server DATENAME function

SELECT DATENAME(month,   ’2012-12-09′)          – December

SELECT DATENAME(weekday, ’2012-12-09′)          – Sunday

– SQL Server DATEPART function

SELECT DATEPART(month, ’2012-12-09′)            – 12

– SQL Server DAY function

SELECT DAY(’2012-12-09′)                        – 9

– SQL Server GETDATE function

– local NYC – EST – Eastern Standard Time zone

SELECT GETDATE()                                – 2012-01-05 07:02:10.577

– SQL Server GETUTCDATE function

– London – Greenwich Mean Time

SELECT GETUTCDATE()                             – 2012-01-05 12:02:10.577

– SQL Server MONTH function

SELECT MONTH(’2012-12-09′)                      – 12

– SQL Server YEAR function

SELECT YEAR(’2012-12-09′)                       – 2012

 

 

————

– T-SQL Date and time function application

– CURRENT_TIMESTAMP and getdate() are the same in T-SQL

————

– SQL first day of the month

– SQL first date of the month

– SQL first day of current month – 2012-01-01 00:00:00.000

SELECT DATEADD(dd,0,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP),0))

– SQL last day of the month

– SQL last date of the month

– SQL last day of current month – 2012-01-31 00:00:00.000

SELECT DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP)+1,0))

– SQL first day of last month

– SQL first day of previous month – 2011-12-01 00:00:00.000

SELECT DATEADD(mm,-1,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP),0))

– SQL last day of last month

– SQL last day of previous month – 2011-12-31 00:00:00.000

SELECT DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,DATEADD(MM,-1,GETDATE()))+1,0))

– SQL first day of next month – 2012-02-01 00:00:00.000

SELECT DATEADD(mm,1,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP),0))

– SQL last day of next month – 2012-02-28 00:00:00.000

SELECT DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,DATEADD(MM,1,GETDATE()))+1,0))

GO

– SQL first day of a month – 2012-10-01 00:00:00.000

DECLARE @Date datetime; SET @Date = ’2012-10-23′

SELECT DATEADD(dd,0,DATEADD(mm, DATEDIFF(mm,0,@Date),0))

GO

– SQL last day of a month – 2012-03-31 00:00:00.000

DECLARE @Date datetime; SET @Date = ’2012-03-15′

SELECT DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,@Date)+1,0))

GO

– SQL first day of year

– SQL first day of the year  -  2012-01-01 00:00:00.000

SELECT DATEADD(yy, DATEDIFF(yy,0,CURRENT_TIMESTAMP), 0)

– SQL last day of year

– SQL last day of the year   – 2012-12-31 00:00:00.000

SELECT DATEADD(yy,1, DATEADD(dd, -1, DATEADD(yy,

DATEDIFF(yy,0,CURRENT_TIMESTAMP), 0)))

– SQL last day of last year

– SQL last day of previous year   – 2011-12-31 00:00:00.000

SELECT DATEADD(dd,-1,DATEADD(yy,DATEDIFF(yy,0,CURRENT_TIMESTAMP), 0))

GO

– SQL calculate age in years, months, days

– SQL table-valued function

– SQL user-defined function – UDF

– SQL Server age calculation – date difference

– Format dates SQL Server 2008

USE AdventureWorks2008;

GO

CREATE FUNCTION fnAge  (@BirthDate DATETIME)

RETURNS @Age TABLE(Years  INT,

Months INT,

Days   INT)

AS

BEGIN

DECLARE  @EndDate     DATETIME, @Anniversary DATETIME

SET @EndDate = Getdate()

SET @Anniversary = Dateadd(yy,Datediff(yy,@BirthDate,@EndDate),@BirthDate)

INSERT @Age

SELECT Datediff(yy,@BirthDate,@EndDate) - (CASE

WHEN @Anniversary > @EndDate THEN 1

ELSE 0

END), 0, 0

UPDATE @Age     SET    Months = Month(@EndDate - @Anniversary) - 1

UPDATE @Age     SET    Days = Day(@EndDate - @Anniversary) - 1

RETURN

END

GO

 

– Test table-valued UDF

SELECT * FROM   fnAge(’1956-10-23′)

SELECT * FROM   dbo.fnAge(’1956-10-23′)

/* Results

Years       Months      Days

52          4           1

*/

 

———-

– SQL date range between

———-

– SQL between dates

USE AdventureWorks;

– SQL between

SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader

WHERE OrderDate BETWEEN ’20040301′ AND ’20040315′

– Result: 108

 

– BETWEEN operator is equivalent to >=…AND….<=

SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader

WHERE OrderDate

BETWEEN ’2004-03-01 00:00:00.000′ AND ’2004-03-15  00:00:00.000′

/*

Orders with OrderDates

’2004-03-15  00:00:01.000′  – 1 second after midnight (12:00AM)

’2004-03-15  00:01:00.000′  – 1 minute after midnight

’2004-03-15  01:00:00.000′  – 1 hour after midnight

 

are not included in the two queries above.

*/

– To include the entire day of 2004-03-15 use the following two solutions

SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader

WHERE OrderDate >= ’20040301′ AND OrderDate < ’20040316′

 

– SQL between with DATE type (SQL Server 2008)

SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader

WHERE CONVERT(DATE, OrderDate) BETWEEN ’20040301′ AND ’20040315′

———-

– Non-standard format conversion: 2011 December 14

– SQL datetime to string

SELECT [YYYY Month DD] =

CAST(YEAR(GETDATE()) AS VARCHAR(4))+ ‘ ‘+

DATENAME(MM, GETDATE()) + ‘ ‘ +

CAST(DAY(GETDATE()) AS VARCHAR(2))

 

– Converting datetime to YYYYMMDDHHMMSS format: 20121214172638

SELECT replace(convert(varchar, getdate(),111),‘/’,”) +

replace(convert(varchar, getdate(),108),‘:’,”)

 

– Datetime custom format conversion to YYYY_MM_DD

select CurrentDate=rtrim(year(getdate())) + ‘_’ +

right(’0′ + rtrim(month(getdate())),2) + ‘_’ +

right(’0′ + rtrim(day(getdate())),2)

 

– Converting seconds to HH:MM:SS format

declare @Seconds int

set @Seconds = 10000

select TimeSpan=right(’0′ +rtrim(@Seconds / 3600),2) + ‘:’ +

right(’0′ + rtrim((@Seconds % 3600) / 60),2) + ‘:’ +

right(’0′ + rtrim(@Seconds % 60),2)

– Result: 02:46:40

 

– Test result

select 2*3600 + 46*60 + 40

– Result: 10000

– Set the time portion of a datetime value to 00:00:00.000

– SQL strip time from date

– SQL strip time from datetime

SELECT CURRENT_TIMESTAMP ,DATEADD(dd, DATEDIFF(dd, 0, CURRENT_TIMESTAMP), 0)

– Results: 2014-01-23 05:35:52.793 2014-01-23 00:00:00.000

/*******

 

VALID DATE RANGES FOR DATE/DATETIME DATA TYPES

 

SMALLDATETIME date range:

January 1, 1900 through June 6, 2079

 

DATETIME date range:

January 1, 1753 through December 31, 9999

 

DATETIME2 date range (SQL Server 2008):

January 1,1 AD through December 31, 9999 AD

 

DATE date range (SQL Server 2008):

January 1, 1 AD through December 31, 9999 AD

 

*******/

– Selecting with CONVERT into different styles

– Note: Only Japan & ISO styles can be used in ORDER BY

SELECT TOP(1)

Italy  = CONVERT(varchar, OrderDate, 105)

, USA    = CONVERT(varchar, OrderDate, 110)

, Japan  = CONVERT(varchar, OrderDate, 111)

, ISO    = CONVERT(varchar, OrderDate, 112)

FROM AdventureWorks.Purchasing.PurchaseOrderHeader

ORDER BY PurchaseOrderID DESC

/* Results

Italy       USA         Japan       ISO

25-07-2004  07-25-2004  2004/07/25  20040725

*/

– SQL Server convert date to integer

DECLARE @Datetime datetime

SET @Datetime = ’2012-10-23 10:21:05.345′

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

– Result: 20121023

 

– SQL Server convert integer to datetime

DECLARE @intDate int

SET @intDate = 20120315

SELECT IntegerToDatetime = CAST(CAST(@intDate as varchar) as datetime)

– Result: 2012-03-15 00:00:00.000

————

– SQL Server CONVERT script applying table INSERT/UPDATE

————

– SQL Server convert date

– Datetime column is converted into date only string column

USE tempdb;

GO

CREATE TABLE sqlConvertDateTime   (

DatetimeCol datetime,

DateCol char(8));

INSERT sqlConvertDateTime (DatetimeCol) SELECT GETDATE()

 

UPDATE sqlConvertDateTime

SET DateCol = CONVERT(char(10), DatetimeCol, 112)

SELECT * FROM sqlConvertDateTime

 

– SQL Server convert datetime

– The string date column is converted into datetime column

UPDATE sqlConvertDateTime

SET DatetimeCol = CONVERT(Datetime, DateCol, 112)

SELECT * FROM sqlConvertDateTime

 

– Adding a day to the converted datetime column with DATEADD

UPDATE sqlConvertDateTime

SET DatetimeCol = DATEADD(day, 1, CONVERT(Datetime, DateCol, 112))

SELECT * FROM sqlConvertDateTime

 

– Equivalent formulation

– SQL Server cast datetime

UPDATE sqlConvertDateTime

SET DatetimeCol = DATEADD(dd, 1, CAST(DateCol AS datetime))

SELECT * FROM sqlConvertDateTime

GO

DROP TABLE sqlConvertDateTime

GO

/* First results

DatetimeCol                   DateCol

2014-12-25 16:04:15.373       20141225 */

 

/* Second results:

DatetimeCol                   DateCol

2014-12-25 00:00:00.000       20141225  */

 

/* Third results:

DatetimeCol                   DateCol

2014-12-26 00:00:00.000       20141225  */

————

– SQL month sequence – SQL date sequence generation with table variable

– SQL Server cast string to datetime – SQL Server cast datetime to string

– SQL Server insert default values method

DECLARE @Sequence table (Sequence int identity(1,1))

DECLARE @i int; SET @i = 0

DECLARE @StartDate datetime;

SET @StartDate = CAST(CONVERT(varchar, year(getdate()))+

RIGHT(’0′+convert(varchar,month(getdate())),2) + ’01′ AS DATETIME)

WHILE ( @i < 120)

BEGIN

INSERT @Sequence DEFAULT VALUES

SET @i = @i + 1

END

SELECT MonthSequence = CAST(DATEADD(month, Sequence,@StartDate) AS varchar)

FROM @Sequence

GO

/* Partial results:

MonthSequence

Jan  1 2012 12:00AM

Feb  1 2012 12:00AM

Mar  1 2012 12:00AM

Apr  1 2012 12:00AM

*/

————

 

————

– SQL Server Server datetime internal storage

– SQL Server datetime formats

————

– SQL Server datetime to hex

SELECT Now=CURRENT_TIMESTAMP, HexNow=CAST(CURRENT_TIMESTAMP AS BINARY(8))

/* Results

 

Now                     HexNow

2009-01-02 17:35:59.297 0x00009B850122092D

*/

– SQL Server date part – left 4 bytes – Days since 1900-01-01

SELECT Now=DATEADD(DAY, CONVERT(INT, 0x00009B85), ’19000101′)

GO

– Result: 2009-01-02 00:00:00.000

 

– SQL time part – right 4 bytes – milliseconds since midnight

– 1000/300 is an adjustment factor

– SQL dateadd to Midnight

SELECT Now=DATEADD(MS, (1000.0/300)* CONVERT(BIGINT, 0x0122092D), ’2009-01-02′)

GO

– Result: 2009-01-02 17:35:59.290

————

————

– String date and datetime date&time columns usage

– SQL Server datetime formats in tables

————

USE tempdb;

SET NOCOUNT ON;

– SQL Server select into table create

SELECT TOP (5)

FullName=convert(nvarchar(50),FirstName+‘ ‘+LastName),

BirthDate = CONVERT(char(8), BirthDate,112),

ModifiedDate = getdate()

INTO Employee

FROM AdventureWorks.HumanResources.Employee e

INNER JOIN AdventureWorks.Person.Contact c

ON c.ContactID = e.ContactID

ORDER BY EmployeeID

GO

– SQL Server alter table

ALTER TABLE Employee ALTER COLUMN FullName nvarchar(50) NOT NULL

GO

ALTER TABLE Employee

ADD CONSTRAINT [PK_Employee] PRIMARY KEY (FullName )

GO

/* Results

 

Table definition for the Employee table

Note: BirthDate is string date (only)

 

CREATE TABLE dbo.Employee(

FullName nvarchar(50) NOT NULL PRIMARY KEY,

BirthDate char(8) NULL,

ModifiedDate datetime NOT NULL

)

*/

SELECT * FROM Employee ORDER BY FullName

GO

/* Results

FullName                BirthDate   ModifiedDate

Guy Gilbert             19720515    2009-01-03 10:10:19.217

Kevin Brown             19770603    2009-01-03 10:10:19.217

Rob Walters             19650123    2009-01-03 10:10:19.217

Roberto Tamburello      19641213    2009-01-03 10:10:19.217

Thierry D’Hers          19490829    2009-01-03 10:10:19.217

*/

 

– SQL Server age

SELECT FullName, Age = DATEDIFF(YEAR, BirthDate, GETDATE()),

RowMaintenanceDate = CAST (ModifiedDate AS varchar)

FROM Employee ORDER BY FullName

GO

/* Results

FullName                Age   RowMaintenanceDate

Guy Gilbert             37    Jan  3 2009 10:10AM

Kevin Brown             32    Jan  3 2009 10:10AM

Rob Walters             44    Jan  3 2009 10:10AM

Roberto Tamburello      45    Jan  3 2009 10:10AM

Thierry D’Hers          60    Jan  3 2009 10:10AM

*/

 

– SQL Server age of Rob Walters on specific dates

– SQL Server string to datetime implicit conversion with DATEADD

SELECT AGE50DATE = DATEADD(YY, 50, ’19650123′)

GO

– Result: 2015-01-23 00:00:00.000

 

– SQL Server datetime to string, Italian format for ModifiedDate

– SQL Server string to datetime implicit conversion with DATEDIFF

SELECT FullName,

AgeDEC31 = DATEDIFF(YEAR, BirthDate, ’20141231′),

AgeJAN01 = DATEDIFF(YEAR, BirthDate, ’20150101′),

AgeJAN23 = DATEDIFF(YEAR, BirthDate, ’20150123′),

AgeJAN24 = DATEDIFF(YEAR, BirthDate, ’20150124′),

ModDate = CONVERT(varchar, ModifiedDate, 105)

FROM Employee

WHERE FullName = ‘Rob Walters’

ORDER BY FullName

GO

/* Results

Important Note: age increments on Jan 1 (not as commonly calculated)

 

FullName    AgeDEC31    AgeJAN01    AgeJAN23    AgeJAN24    ModDate

Rob Walters 49          50          50          50          03-01-2009

*/

 

————

– SQL combine integer date & time into datetime

————

– Datetime format sql

– SQL stuff

DECLARE @DateTimeAsINT TABLE ( ID int identity(1,1) primary key,

DateAsINT int,

TimeAsINT int

)

– NOTE: leading zeroes in time is for readability only!

INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 235959)

INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 010204)

INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 002350)

INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 000244)

INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 000050)

INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 000006)

 

SELECT DateAsINT, TimeAsINT,

CONVERT(datetime, CONVERT(varchar(8), DateAsINT) + ‘ ‘+

STUFF(STUFF ( RIGHT(REPLICATE(’0′, 6) + CONVERT(varchar(6), TimeAsINT), 6),

3, 0, ‘:’), 6, 0, ‘:’))  AS DateTimeValue

FROM   @DateTimeAsINT

ORDER BY ID

GO

/* Results

DateAsINT   TimeAsINT   DateTimeValue

20121023    235959      2012-10-23 23:59:59.000

20121023    10204       2012-10-23 01:02:04.000

20121023    2350        2012-10-23 00:23:50.000

20121023    244         2012-10-23 00:02:44.000

20121023    50          2012-10-23 00:00:50.000

20121023    6           2012-10-23 00:00:06.000

*/

————

 

– SQL Server string to datetime, implicit conversion with assignment

UPDATE Employee SET ModifiedDate = ’20150123′

WHERE FullName = ‘Rob Walters’

GO

SELECT ModifiedDate FROM Employee WHERE FullName = ‘Rob Walters’

GO

– Result: 2015-01-23 00:00:00.000

 

/* SQL string date, assemble string date from datetime parts  */

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

– SQL Server number to varchar conversion

– SQL Server leading zeroes for month and day

– SQL Server right string function

UPDATE Employee SET BirthDate =

CONVERT(char(4),YEAR(CAST(’1965-01-23′ as DATETIME)))+

RIGHT(’0′+CONVERT(varchar,MONTH(CAST(’1965-01-23′ as DATETIME))),2)+

RIGHT(’0′+CONVERT(varchar,DAY(CAST(’1965-01-23′ as DATETIME))),2)

WHERE FullName = ‘Rob Walters’

GO

SELECT BirthDate FROM Employee WHERE FullName = ‘Rob Walters’

GO

– Result: 19650123

 

– Perform cleanup action

DROP TABLE Employee

– SQL nocount

SET NOCOUNT OFF;

GO

————

————

– sql isdate function

————

USE tempdb;

– sql newid – random sort

SELECT top(3) SalesOrderID,

stringOrderDate = CAST (OrderDate AS varchar)

INTO DateValidation

FROM AdventureWorks.Sales.SalesOrderHeader

ORDER BY NEWID()

GO

SELECT * FROM DateValidation

/* Results

SalesOrderID      stringOrderDate

56720             Oct 26 2003 12:00AM

73737             Jun 25 2004 12:00AM

70573             May 14 2004 12:00AM

*/

– SQL update with top

UPDATE TOP(1) DateValidation

SET stringOrderDate = ‘Apb 29 2004 12:00AM’

GO

– SQL string to datetime fails without validation

SELECT SalesOrderID, OrderDate = CAST (stringOrderDate as datetime)

FROM DateValidation

GO

/* Msg 242, Level 16, State 3, Line 1

The conversion of a varchar data type to a datetime data type resulted in an

out-of-range value.

*/

– sql isdate – filter for valid dates

SELECT SalesOrderID, OrderDate = CAST (stringOrderDate as datetime)

FROM DateValidation

WHERE ISDATE(stringOrderDate) = 1

GO

/* Results

SalesOrderID      OrderDate

73737             2004-06-25 00:00:00.000

70573             2004-05-14 00:00:00.000

*/

– SQL drop table

DROP TABLE DateValidation

Go

 

————

– SELECT between two specified dates – assumption TIME part is 00:00:00.000

————

– SQL datetime between

– SQL select between two dates

SELECT EmployeeID, RateChangeDate

FROM AdventureWorks.HumanResources.EmployeePayHistory

WHERE RateChangeDate >= ’1997-11-01′ AND

RateChangeDate < DATEADD(dd,1,’1998-01-05′)

GO

/* Results

EmployeeID  RateChangeDate

3           1997-12-12 00:00:00.000

4           1998-01-05 00:00:00.000

*/

 

/* Equivalent to

 

– SQL datetime range

SELECT EmployeeID, RateChangeDate

FROM AdventureWorks.HumanResources.EmployeePayHistory

WHERE RateChangeDate >= ’1997-11-01 00:00:00′ AND

RateChangeDate <  ’1998-01-06 00:00:00′

GO

*/

————

– SQL datetime language setting

– SQL Nondeterministic function usage – result varies with language settings

SET LANGUAGE  ‘us_english’;  –– Jan 12 2015 12:00AM

SELECT US = convert(VARCHAR,convert(DATETIME,’01/12/2015′));

SET LANGUAGE  ‘British’;     –– Dec  1 2015 12:00AM

SELECT UK = convert(VARCHAR,convert(DATETIME,’01/12/2015′));

SET LANGUAGE  ‘German’;      –– Dez  1 2015 12:00AM

SET LANGUAGE  ‘Deutsch’;     –– Dez  1 2015 12:00AM

SELECT Germany = convert(VARCHAR,convert(DATETIME,’01/12/2015′));

SET LANGUAGE  ‘French’;      –– déc  1 2015 12:00AM

SELECT France = convert(VARCHAR,convert(DATETIME,’01/12/2015′));

SET LANGUAGE  ‘Spanish’;     –– Dic  1 2015 12:00AM

SELECT Spain = convert(VARCHAR,convert(DATETIME,’01/12/2015′));

SET LANGUAGE  ‘Hungarian’;   –– jan 12 2015 12:00AM

SELECT Hungary = convert(VARCHAR,convert(DATETIME,’01/12/2015′));

SET LANGUAGE  ‘us_english’;

GO

————

————

– Function for Monday dates calculation

————

USE AdventureWorks2008;

GO

– SQL user-defined function

– SQL scalar function – UDF

CREATE FUNCTION fnMondayDate

(@Year          INT,

@Month         INT,

@MondayOrdinal INT)

RETURNS DATETIME

AS

BEGIN

DECLARE  @FirstDayOfMonth CHAR(10),

@SeedDate        CHAR(10)

SET @FirstDayOfMonth = convert(VARCHAR,@Year) + ‘-’ + convert(VARCHAR,@Month) + ‘-01′

SET @SeedDate = ’1900-01-01′

RETURN DATEADD(DD,DATEDIFF(DD,@SeedDate,DATEADD(DD,(@MondayOrdinal * 7) - 1,

@FirstDayOfMonth)) / 7 * 7,  @SeedDate)

END

GO

 

– Test Datetime UDF

– Third Monday in Feb, 2015

SELECT dbo.fnMondayDate(2016,2,3)

– 2015-02-16 00:00:00.000

 

– First Monday of current month

SELECT dbo.fnMondayDate(Year(getdate()),Month(getdate()),1)

– 2009-02-02 00:00:00.000

————

Delete the background image on YouTube

-log into youtube
-go to your channel
-click 'edit channel'
-click the blue link that says 'channel Design' on the left hand side
-go down to 'background image'
-delete the link from the box
-click 'update channel'

now the background image will no longer display on your youtube channel

 

 

other answer

*Go to "Account" then "Channel Design".

*Under channel design go to "Advanced Design Customization" then click "pick" next to "Background Color". Pick the color black at the very bottom left of the color palate.

*Then under "Repeat Background Image", click "no".

*Save this picture under "My Pictures" on your computer. http://upload.wikimedia.org/wikipedia/commons/thumb/1/1a/Color_icon_black.svg/300px-Color_icon_black.svg.png
*Next to "Background Image" on your "youtube account" page, click "Browse". Save that picture of the image to use as your background.

*Afterwards, you should be able to pick any color off of the "Background Color" option.

I hope this helps...it is kinda confusing, I know, but if you have any questions just let me know. :)

I figured it out on my own.

or

1 -- click on "ACCOUNT▼", ignoring the blue drop-down menu.
(you'll get a new page with black titles and many blue subtitles)

2 -- look on the left for the 3rd black title called "MY CHANNEL"
(you'll see 5 different blue subtitles including "Channel Design"

3 -- click on "CHANNEL DESIGN"
(you'll get a new page with nine colour combinations at the top)

4 -- scroll half-way down and look for "BACKGROUND IMAGE"
(it's the 2nd thin rectangle, with the "Browse" box just beside it)

5 -- simply DELETE THE LINK
(the rectangle should be blank)

6 -- scroll to the top of the page and click "UPDATE CHANNEL"
(it's on the grey stripe just above the nine colour combinations)

Here is the URL address of the YouTube help reference which
most closely answers your question, followed by its direct link:

www . google . com / support / youtube / bin / answer . py ? hl = en & answer = 71515
http://www.google.com/support/youtube/bi…

Change of Apache port in xamp http.config file

Change apache port in xamp http.config file
C://xamp/apache/config/--> change localhost 80 to 8080

Sample example of error message infomation for site

Sorry, an error has occurred...

Unfortunately an error has occurred during the processing of your page request. Please be assured we log and review all errors, even if you do not report this error we will endeavor to correct it.

Google:
500. That’s an error.

The server encountered an error and could not complete your request.

If the problem persists, please report your problem and mention this error message and the query that caused it. That’s all we know.

How to Put 6 Game CDs Into One DVD With UltraISO

Step1: Backup your game CDs

1*) Start UltraISO, press F5, the 'Make CD image' dialog will appear
2) Select 'CloneCD(CCD/IMG/SUB) format
3) Click 'Make'
*: For copy-protected game CDs, UltraISO may work very slowly.
CloneCD is recommanded to backup this kind of CDs.

Step2: Create a DVD ISO

1) Add all files just created to 'Image' area, including .CCD, .IMG and .SUB files.
2) Save the image as an ISO file

Step3: Burn to DVD disc

1) Firstly, please make sure you are using lastest version (7.2.1.819 and above)
2) Use 'Options'->'Configuration' from main menu, a dialog will apear
3) Click 'Detect' button, UltraISO will ask you to confirm using Easy CD/DVD Creator,
WinOnCD or Easy Media Creator as default burning software, answer 'Yes'
4) Click 'OK' to close this dialog
5) Put a blank DVD-R disc to your recorder, and press F7 to burn

Step4: Play the game

1) Install a CD emulation software such as SoftDisc
2) Mount the CCD file to virtual drive (up to 4 Simultaneously)
3) You now can play your game on DVD

How to take Screenshots of Movies (DVD and AVI)

How to take Screenshots of Movies (DVD and AVI)

Guide For AVI's (XviD/DivX) -

1. Get MPC here! and the XviD codec here! if you don't already have it   [Install the XviD codec]

2. Start MPC and open the file [XviD/DivX]

3. Browse to the 'file' and click on 'open'

4. Stop the video. Click on 'file' again and then click on 'save thumbnails'

5. Change the () 'rows' to 6 & (2) 'columns' to 1, also change the (3) 'image width' to the same width as your video file.
My video file is 640 (width) x 480 (height) so I input 640 as in the image below Then browse to the destination where you would like to save the snap and click on 'save' for DVD's is below or click here!

6. Results (click on image for an enlarged view)

Guide For DVD's - Get VLC Player here! and install it

Matrix Output in C

user will just enter number of rows and cols and your program will generate following result.....
for eg.
for n=3 your output should be

4 9 2
3 5 7
8 1 6

each number is used only once....addition of numbers in rows,cols and on diagonals are same. So, now try this, first of all just for the odd values of n...then think about the even values of n.....first of all is there any pattern ??? then and then you can write logic for this.... Lets see how much time it will take????

void main()
{
int a=3;
int b;
b=(++a)+(++a)+(++a);
a=3;
printf("%d",(++a)+(++a)+(++a));
}

How To use Pirated Softwares in 4 Simple Steps

Usage 4 any softwares :

1 . Patch :

Package comes as trail software + patch.exe

a. First u have to install the software & have to close all instances of it.
b. Copy the patch.exe to the installed directory ex : C:ProgramFilesWinRAR & double click it

2.Crack. :

Package comes as trail software + application.exe + .dll files

a. First u have to install the software & have to close all instances of it.
b.GO to the installed directory & replace the original .exe with the given application.exe file & follow the instructions given in readme.txt for dll files

3.Keygen:

Package comes as trail software + Keygen.exe

a )install the software & minimize it for a while

b) Input the details like name, mail in keygen.exe . u will get keys copy them & paste in the registration field of the software.

4.Retail

Package comes as single setupfile

this is the least bothered case which comes with pre manipulated registration details . just install it

NB:

1. B4 installing any pirated s/w u have to read the instructions given as readme.txt or how_to.txt.

2. wenever there are .nfo , .biz , .diz , .txt files are given 4 cracks u shud read them by opening in notepads

How to create a folder named "con"

1. Creating a folder with 'con' as folder name is not possible in Windows. This is because 'con' is a reserved word since the DOS OS was created. The word 'con' stands for console. But there is a way to create a folder named 'con' at any location. Thats true! Run the following command in command prompt :

Example - Code:

md.C:con

This will create a folder with 'con' as its name. But before creating the folder please make a note, nor use it to save any data, Its just a useless folder...

2. I tried experimenting with that command, and found out its actual syntax which you can use to create the con folder at any location on the windows based computer. The syntax is :

Example - Code:


md.path-to-create-con-folder

Example - Code:

md.C:SpearMan

3. How to delete a folder named "con"

I found a way to delete the con folder. Use the same command to delete, just replace 'md' command with 'rmdir'
Example - Code:

rmdir.C:con

How To Lock a Folder Without Using any Software Modified one

How To Lock a Folder Without Using any Software And changing attributes!

I had modified the script by making some changes Check it out

Simple Copy-Paste-Rename-Store-and LOCK. can work anywhere on any computer.

NO Software Required. Simple Lock your Folder, with these Few Steps. Ive tried it, and works great. it works on Windows Xp, SP2 and vista
IM not sure, if it works on other OS as well. but its harmless, so try it out.

Steps:

1- make a new folder ( name it as you like )

2- inside this folder make a ( TXT ) file & copy inside it this (the entire thing)

Quote:
@ECHO OFF
cls
color 0b
title Folder Private
if EXIST "Control Panel.{21EC2020-3AEA-1069-A2DD-08002B30309D}" goto UNLOCK
if NOT EXIST Private goto MDLOCKER
:CONFIRM
echo Are you sure you want to lock the folder(Y/N)
set/p "cho=>"
if %cho%==Y goto LOCK
if %cho%==y goto LOCK
if %cho%==n goto END
if %cho%==N goto END
echo Invalid choice.
goto CONFIRM
:LOCK
ren Private "Control Panel.{21EC2020-3AEA-1069-A2DD-08002B30309D}"
attrib +h +s "Control Panel.{21EC2020-3AEA-1069-A2DD-08002B30309D}"
echo Folder locked
pause
goto End
:UNLOCK
echo Enter password to unlock folder
set/p "pass=>"
if NOT %pass%== passwordhere goto FAIL
attrib -h -s "Control Panel.{21EC2020-3AEA-1069-A2DD-08002B30309D}"
ren "Control Panel.{21EC2020-3AEA-1069-A2DD-08002B30309D}" Private
echo Folder Unlocked successfully
pause
goto End
:FAIL
echo Invalid password
pause
goto end
:MDLOCKER
md Private
echo Private created successfully
goto End
:End

3- After u copy the Commanding go to line 23 (or try using shortcut- CTRL+F and type password to locate the line) u will find this word : password here (Change it with any password u like.) is :

eg: if NOT %pass%== narnia1234 goto FAIL
//so ur password here becomes narnia1234 .//

4- After that go to ‘save as’ & name this file as "locker.bat "

5- Now back to the folder & u will find a ( LOCKER ) commanding.
(locker.exe)

6- Double Click on it & u will find a new folder (Private )

7- Ok ,, now copy what u want in this "private Folder" & after that come out of the folder, and Double click on locker again. It will open and ask if you want to lock your folder? Y/N ?

8- Type Y. your private folder will dissapear.

9- If you want to UNLOCK your folder, go to (locker) & type your pass and you will see your private folder.

Add color to your folders

In google search with the phrase "iColorFolder", download .exe from the very first link and install it on ur system.

now right click on ur folder which u want to see in color, there is one option called color from this u will see the desired color.

Turn Your Computer Off In Less Than 3 Seconds

Turn Your Computer Off In Less Than 3 Seconds

---

1. First open up task manager by pressing "Ctrl + ALt + Delete"
2. Go to "Shutdown"
3. Click on "Turn Off" while holding "Ctrl"

once you get a hang of it, it will only take few seconds to turn your computer off without harming it.

Photoshop shortcut keys

Photoshop Useful Shortcut Key Chart for Windows -

One of Photoshop's key features is that almost everything in it can be controlled using keyboard shortcuts. This allows the user to stay in a creative thought pattern and quickly change tools in monotonous work that requires it.

F1 - Toggles Adobe Online Help
F5 - Toggles Brush style palette
F6 - Toggles Colour, Swatches, Styles palette
F7 - Toggles Layers, Channels, Paths palette
F8 - Toggles Navigator, Info palette
F9 - Toggles Actions, History, Presets palette
Tab (Key) - Toggles all the palettes on screen
Shift + Tab (Key) - Toggles palettes on screen, excluding the Toolbar.

Main Toolbar
Key	Action	Action	Key
	Marquee Tool	Move Tool
	Lasso Tool	Magic Wand Tool
	Airbrush Tool	Paintbrush Tool
	Rubber Stamp Tool	History Brush Tool
	Eraser Tool	Pencil Tool
	Blur Tool	Dodge Tool
	Pen Tool	Type Tool
	Measure Tool	Linear Gradient Tool
	Paint Bucket Tool	Eyedropper Tool
	Hand Tool	Zoom Tool
	Default foreground and background colors.
	Switches between foreground and background colors.
	Switches between standard mode or quick mask mode.
	Switches between screen modes.
TAB	Hides/Unhides open tools.

ile Menu
Key	Action
CTRL + N	ew
CTRL + O	pen
CTRL + ALT + O	Opn As
CTRL + W	lose
CTRL + S	ave
CTRL + SHIFT + S	Save
CTRL + ALT + S	Save a Cop
CTRL + SHIFT + P	Pae Setup
CTRL + P	rint
CTRL + K	Preerences > eneral

dit Menu
Key	Action
CTRL + Z	Undo
CTRL + X	Cu
CTRL + C	opy
CTRL + SHIFT + C	Copy erged
CTRL + V	aste
CTRL + SHIFT + V	Paste nto
CTRL + T	ree Transform
CTRL + SHIFT + T	Trnsform > gain

mage Menu
Key	Action
CTRL + L	djust > evels
CTRL + SHIFT + L	djust > uto Levels
CTRL + ALT + SHIFT + L	djust > Ato Contrast
CTRL + M	djust > Cures
CTRL + B	djust > Color alance
CTRL + U	djust > ue/Saturation
CTRL + SHIFT + U	djust > esaturate
CTRL + I	djust > nvert
CTRL + ALT + X	Extract

ayer Menu
Key	Action
CTRL + SHIFT + N	ew > ayer
CTRL + J	ew > Layer Via opy
CTRL + SHIFT + J	ew > Layer Via Cu
CTRL + G	roup with Previous
CTRL + SHIFT + G	ngroup
CTRL + SHIFT + ]	rrange > Bring to ront
CTRL + ]	rrange > Bring Forard
CTRL + [	rrange > Send Bacward
CTRL + SHIFT + [	rrange > Send to ack
CTRL + E	rge Down
CTRL + SHIFT + E	Merge isible

elect Menu
Key	Action
CTRL + A	ll
CTRL + D	eselect
CTRL + SHIFT + D	select
CTRL + SHIFT + I	nverse
CTRL + ALT + D	eather

Filer Menu
Key	Action
CTRL + F	Last ilter
CTRL + SHIFT + F	Fa

iew Menu
Key	Action
CTRL + Y	Prview > MYK
CTRL + SHIFT + Y	Gamut arning
CTRL + +	Zoom
CTRL + -	Zoom ut
CTRL + 0	it on Screen
CTRL + SHIFT + 0	ctual Pixels
CTRL + H	Hide Eges
CTRL + SHIFT + H	Hide Pat
CTRL + R	Show ulers
CTRL + ;	Hide Gides
CTRL + SHIFT + ;	Snap o Guides
CTRL + ALT + ;	Loc Guides
CTRL + "	Show rid
CTRL + SHIFT + "	ap To Grid

elp Menu
Key	Action
F1	ontents

Other Shortcuts
Key	Action
Page Up/Down	Move view up/down 1 screen.
SHIFT + Page Up/Down	Nudge view up/down.
CTRL + Page Up/Down	Move view left/right 1 screen.
CTRL + SHIFT + Page Up/Down	Nudge screen left/right.
CTRL + SHIFT + Z	Previous History entry.
CTRL + ALT + Z	Next History entry.
SHIFT + -	Previous blending mode.
SHIFT + +	Next blending mode.

Ctrl Short Cuts -

Ctrl + N - New Document Dialogue Box
Ctrl + M - Curves Dialogue Box
Ctrl + A - Selects all in the currently foreground document or currently selected layer
Ctrl + D - Deselects all in the currently foreground document or currently selected layer
Ctrl + J - Automatically creates a duplicate of the currently selected layer
Ctrl + K - Preferences Dialogue Box
Ctrl + L - Levels Dialogue Box
Ctrl + F4 - Closes current document
Ctrl + ' (Single Quote Key) - Toggles Grid Lines
Ctrl + Q - Quits Photoshop altogether
Ctrl + R - Toggles Rulers
Ctrl + U - Hue/Saturation Dialogue Box
Ctrl + O - Opens New File
Ctrl + P - Print Dialogue Box
Ctrl + Z - Undo last Action
Ctrl + Tab - Toggle between open documents
Ctrl + Shift + C - Copy Merged
Ctrl + C - Copy
Ctrl + H - Toggle Extras
Ctrl + ; - Toggle Guides
Ctrl + Shift + ; - Toggle Snap
Ctrl + X - Cut
Ctrl + Alt + Shift + X - Pattern Maker
Ctrl + V - Paste
Ctrl + Shift + V - Paste into selection
Ctrl + Alt + Shift + V - Paste Outside
Ctrl + T - Transform Tool
Ctrl + Shift + T - Repeats the last performed Transform
Shift + F5 / Shift + Backspace (Key) - Fill Layer Dialogue BoxCombination Number 9 Shortcuts - Ctrl + Shift / Alt -

Ctrl + Shift + O - Photoshop's File Browser
Ctrl + Shift + P - Page Setup Dialogue Box
Ctrl + Shift + S - Save As Dialogue Box
Ctrl + Shift + K - Color Setting Preferences Box
Ctrl + Shift + F - Fade Dialogue Box
Ctrl + Shift + X - Liquify Filter Tool
Ctrl + Shift + N - Create New Layer Preferences Box
Ctrl + Shift + M - Launches ImageReady
Ctrl + Shift + E - Merges all layers into a single layer
Ctrl + Alt + Z - Step Backward
Ctrl + Shift + - (Minus Sign Key) - Zoom Out
Ctrl + Shift + + (Plus Sign Key) - Zoom In
Ctrl + Shift + Alt + N - Creates a new empty layer
Ctrl + Shift + Alt + S - Save For The Web Dialogue
Ctrl + Alt (in most Dialogue Boxes) - Changes the 'Cancel' command to 'Reset'
Ctrl + Alt (in the 'Save For Web Dialogue') - Changes the 'Cancel' command to 'Reset' & the 'Done' command to 'Remember'
Ctrl + Alt + ~(Tild Symbol) - Selects the brightest area of the currently selected layer
Ctrl + Shift + I - Inverts a selection
Ctrl + Alt + X - Extract
Shift + -/+ signs(on a layer) - Toggles the different layer modes
Shift + Ctrl + Z - Step Forward

File Menu Shortcuts:

Ctrl+N: New Document
Ctrl+O: Open Document
Shift+Ctrl+O: Browse
Alt+Ctrl+O:Open As
Ctrl+W: Close
Ctrl+Shift+W: Close All
Ctrl+S: Save
Ctrl+Shift+S: Save As
Ctrl+Alt+S: Save a Copy
Ctrl+Alt+Shift+ S: Save for Web
Ctrl+Shift+P: Page Setup
Ctrl+Shift+M: Jump to Image Ready
Ctrl+Q: ExitViewing Shortcuts:

Ctrl+Y: Proof Colors
Ctrl++: Zoom In
Ctrl+-: Zoom Out
Ctrl+Alt++: Zoom In & Resize Window
Ctrl+Alt+-: Zoom Out & Resize Window
Ctrl+Alt+0: Actual Pixels
Ctrl+Shift+H: Show/Hide Target Path
Ctrl+R: Show/Hide Rulers
Ctrl+Shift+; : On/Off Snap
Ctrl+H: Show/Hide Extras
Ctrl+Alt+;: Lock Guides
Ctrl+;: Show Guides
Ctrl+': Show GridTools Shortcuts:

A: Path Component Selection Tool
B: Paintbrush Tool
C: Crop Tool
D: Changes Default Colour Palettes To Black Foreground, White Background
E: Eraser Tool
F: Cycle Screen Modes
G: Gradient Tool
H: Hand Tool
I: Eyedropper Tool
J: Airbrush Tool
K: Slice Tool
L: Lasso Tool
M: Marquee Tool
N: Notes
O: Dodge/Burn/Sponge Tool
P: Pen Tool
Q: Quick Mask
R: Blur/Sharpen/ Smudge Tool
S: Clone Stamp
T: Type Tool
U: Shape Tool
V: Move Tool
W: Magic Wand
X: Swap Colours On Colour Pallete
Y: History Brush
Z: Zoom ToolLayer Shortcuts:

Ctrl+Shift+N: New Layer
Ctrl+J: Layer via Copy
Ctrl+Shift+J: Layer via Cut
Ctrl+G: Group with Previous
Ctrl+Shift+] : Bring to Front
Ctrl+]: Bring Forward
Ctrl+[: Send Backward
Ctrl+Shift+[ : Send Back
Ctrl+E: Merge Layers
Ctrl+Shift+E: Merge VisibleImage Manipulation Shortcuts:

Ctrl+L: Adjust Levels
Ctrl+Shift+L: Adjust Auto Levels
Ctrl+Alt+Shift+ L: Adjust Auto Contrast
Ctrl+M: Adjust Curves
Ctrl+B: Adjust Color Balance
Ctrl+U: Adjust Hue/Saturation
Ctrl+Shift+U: Desaturate
Ctrl+I: Invert
Ctrl+Alt+X: ExtractFilters Shortcuts:

Ctrl+F: Last Filter
Ctrl+Shift+F: Fade
Ctrl+Alt+X: Extract
Ctrl+Shift+X: Liquify
Ctrl+Shift+Alt+ X: Pattern MakeSelection Shortcuts:

Ctrl+A: Select All
Ctrl+D: Deselect
Ctrl+Shift+D: Reselect
Ctrl+Shift+I: Inverse
Ctrl+Alt+D: FeatherRandom Shortcuts:

Alt+Backspace: Fill with Forground Color
Shift+Backspace: Fill with Background Color
Alt+]: Ascend through Layers
Alt+[: Descend through Layers
Shift+Alt+]: Select Top Layer
Shift+Alt+[: Select Bottom Layer
Tab: Show/Hide All Palettes

A Beginners Guide to Photoshop

A Beginners Guide to Photoshop

This is a pretty awesome tutorial on everything you need to know about Photoshop. From the basics to the explination of almost everything in Photoshop.

The Workspace



Tools Palette:
Self explanatory - the palette that contains the tools that can be used to directly edit the image.
Tool Options:
A selection of options that relate to the currently selected tool and alter its effects.
Drop down menus:
Standard drop down menus as seen in most Windows and Macintosh applications. Offers access to most of Photoshop's features. Commands for the drop down menus in these tutorials are written in bold.
Info Palette:
Shows information about the current image, or shows a thumbnail via which you can navigate the image. Information provided includes crop/selection area, file size, image dimensions, etc.
Colours/Styles Palette:
Allows you to select a colour via RGB sliders or preset web-safe colours ('Swatches'). Also lets you apply layer styles - preset combinations of layer styles (effects) that can achieve effective results if you're in a hurry. Use the expansion arrow (just undereath the close button) to load more styles that are included with Photoshop.
History Palette:
Gives a list of actions that can be undone - lists around one hundred. You can save snapshots to return to at key points in your image creation, or return to the saved file/a recent step. Also contains actions and tool presets for the currently selected tool.
Layers Palette:
The layers palette has many features, too many to list here. They are described later in the tutorial.

Layers



Palette Objects

Layer Opacity:
The opacity of the current layer, 0-100%.
Blending Mode:
The manner in which this layer interacts with layers below it. See below.
Active/Linked Layers:
A small paint brush icon appears in this space to indicate the active layer, and chain icons signify other layers that are linked with the active layer.
Layer visibility:
An eye in this area signifies that the layer is visible, and an empty box means it is hidden from view and exempt from formatting.
Layers:
Two example layers showing an example background layer and new (transparent) layer (Layer 1).
New Fill/Adjustment Layer:
Creates a layer that can add a gradient to or adjust the hue, etc. of the layer below.
New Layer:
Creates a new layer [ctrl/cmd + shift + N].
Delete Layer:
Deletes the currently selected layer.
New Layer Set:
Creates a folder for layers to be put into for easy organisation of layers.
New Layer Mask:
Creates a sub-layer with which you can use all normal tools. Adding black to a layer mask, for example, means that that part of the layer is invisible.
Layer Effects (Styles):
Applies various effects to the current layer - can also be reached via Layer || Layer Style.

Blending Modes



Normal/Dissolve:
All layers appear as normal - dissolve doesn't blend the colours that are layered, but instead applies the colour to pixels at random in accordance.
Darken > Linear Burn:
Subtly different methods of darkening the layers beneath the selected layer.
Lighten > Linear Dodge:
Subtly different methods of lightening the layers beneath the selected layer.
Overlay:
Crudely put, reduces the opacity of the layer.
Soft Light > Pin Light:
Methods of lightening layers below the current one at varying strengths.
Difference/Exclusion:
Quite complicated - this is as it appears in the PS help files: "Looks at the color information in each channel and subtracts either the blend color from the base color or the base color from the blend color, depending on which has the greater brightness value. Blending with white inverts the base color values; blending with black produces no change." Exclusion is pretty much the same but at lower contrast.
Hue > Luminosity:
Effects the respective value of the layer below.

Tools



Marquee Tool
The marquee tool selects areas within a layer. It is capable of selecting an elliptical, square, single column, and single rows.
Move Tool
The move tool can move around all objects within a layer. To move entire image, Flatten the layers by selecting Layer > Flatten Image.
Lasso Tool
The lasso tool can select areas within in a layer that can’t be reached with the marquee tool.
Magic Wand
The magic wand is an automatic selection tool. It selects everything in the layer.
Cropping tool
This tool changes the size of the image. To use, Select the area you want to crop and then press enter.
Slice Tool
Makes Guidelines.
Heal Brush Tool and Patch Tool
The patch tool is another form of the heal brush. It is in the options for the heal brush. The Heal Brush deletes the high contrast of a picture and is used when retouching a photo. To use, hold down alt while clicking on the source (an area that looks like what you want the damaged part to look like) and drag the mouse around the damaged picture to repair it.
The patch tool fixes damaged parts of a picture by blending the damaged part with a better one. Just use the patch tool to select part of the image, then drag the selection to another part of the image. This will combine the selections.
Pencil tool and brush tool
Draws or paints a line. Same as the pencil or brush tool in paint. Change the color of the paint brush by clicking on the color picker .
Clone Stamp Tool and Pattern Stamp tool
The clone stamp tool duplicates part of the image. Hold down alt while clicking to choose the part of the image which you want to duplicate. Drag the mouse over the damaged area.
The pattern stamp tool can create and save a pattern you want to use over and over again. To make a pattern, use the marquee tool or the lasso tool to select part of the image. Go to Filter > Pattern Maker and click generate. Click on the floppy disk underneath the Tile History called Save Preset Pattern. This will save the pattern. Do not push OK unless you want to fill the entire image with that pattern. Press cancel and select your pattern from Pattern on the top center of Photoshop. Drag the mouse over the area with the pattern stamp tool to edit the picture.
History Brush Tool and Art History Brush
The history Brush tool can go back and undo certain changes in a picture. To delete everything you did since the opening of the picture simply drag the history brush over the area to be repaired. The history brush works best when transforming snap shots. Using the snap shot will only change certain parts of the picture and can go back in editing to change major errors. To make a Snap Shot, save the picture then click on the small camera on the bottom of the history window. The snap shot will be saved once it is named. If you click on a previous snap shot, you can edit a previous version of your picture. Click on the last snap shot then use the history brush tool to undo the editing to make the unedited section look like the previous snap shot.
The Art history Brush tool can artistically undo an image and warp the original picture. Click on the top snap shot and chose what kind of brush to use on the top of the window under Style. Click on the area you want to edit with the art history brush.
Eraser tool
This can erase part of the photo in a certain layer. To erase everything in a certain area to make it white, flatten the image or go through every layer to delete that part.
Paint Bucket
Makes an area one color. To edit all layers at one time, click on All layers at the top of the window.
The Blur tool, Sharpen Tool, and Smudge Tool
To use, drag over part of photo you want to edit. The hardest part of this tool is selecting the right strength, brush size and mode. This tool is capable of blurring part of the image, undoing the blur with the sharpen tool, and slightly liquefying with the smudge tool.
The Dodge tool, Burn tool, and Sponge tool
To use any of these tools, just drag it over the image. The dodge tool can lighten an image, the burn tool darkens it, and the sponge tool soaks color out of the image.
Text tool
Puts text in a picture. Click on the picture with the type tool and select a box the size of the area you want to add text. Type in the box then adjust the size of the text box.
Pen Tool
The pen tool can make lines and be used with shape tools to create different shapes. To create lines, use the pen tool to create anchors (the little boxes on a line) and change the shape of the line by moving around the anchors.
Custom Shape Tools
Creates shapes in the image. The custom shapes tools can create all shapes in the shape section located at the top center of the Photoshop window.
Annotation tool
This can create notes and sound effects in an image. The only difference between the note tool and the text tool is that the note comes up in a little white box and when the note is too long for the given space, there will be a scroll bar. This is usually used in PDF formats and Acrobat Documents.
The audio Annotation tool can add audio notation to the picture. To use, click on the audio notation tool and press start. Record your voice with the microphone then press stop. You can import the audio by File > Import > Annotations. Make sure the file you import is a PDF file or a FDF file.
Eyedropper tool
Samples a color from the picture, color swatches, or the color picker. To use, click on the color on the image you want to take and right click.
Hand Tool
Moves around image within an object. Is used with the zoom tool when you want to adjust the section of picture you want to look at.
Zoom tool
Zooms in on part of the picture for closer editing.