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
————
Wednesday 13 June 2012
Remove duplicate rows from a DataTable
--------- Demo Removing Duplicate rows in a DataTable ----------------
using System;
using System.Data;
using System.Collections;
namespace Demo_DataTableRemoveDupRows
{
class Class1
{
[STAThread]
static void Main(string[] args)
{
// create an example datatable with duplicate rows
DataTable tbl = new DataTable();
tbl.Columns.Add("ColumnA");
tbl.Columns.Add("ColumnB");
tbl.Columns.Add("ColumnC");
for(int i = 0; i<10; i++)
{
DataRow nr = tbl.NewRow();
nr["ColumnA"] = "A" + i.ToString();
nr["ColumnB"] = "B" + i.ToString();
nr["ColumnC"] = "C" + i.ToString();
tbl.Rows.Add(nr);
// duplicate
nr = tbl.NewRow();
nr["ColumnA"] = "A" + i.ToString();
nr["ColumnB"] = "B" + i.ToString();
nr["ColumnC"] = "C" + i.ToString();
tbl.Rows.Add(nr);
}
PrintRows(tbl); // show table with duplicates
//Create an array of DataColumns to compare
//If these columns all match we consider the
//rows duplicate.
DataColumn[] keyColumns =
new DataColumn[]{tbl.Columns["ColumnA"],
tbl.Columns["ColumnA"]};
//remove the duplicates
RemoveDuplicates(tbl, keyColumns);
PrintRows(tbl); // show table again
Console.ReadLine(); // pause to view output
}
/// <summary>
/// Removes duplicate rows from given DataTable
/// </summary>
/// <param name="tbl">Table to scan for duplicate rows</param>
/// <param name="KeyColumns">An array of DataColumns
/// containing the columns to match for duplicates</param>
private static void RemoveDuplicates(DataTable tbl,
DataColumn[] keyColumns)
{
int rowNdx = 0;
while(rowNdx < tbl.Rows.Count-1)
{
DataRow[] dups = FindDups(tbl, rowNdx, keyColumns);
if(dups.Length>0)
{
foreach(DataRow dup in dups)
{
tbl.Rows.Remove(dup);
}
}
else
{
rowNdx++;
}
}
}
private static DataRow[] FindDups(DataTable tbl,
int sourceNdx,
DataColumn[] keyColumns)
{
ArrayList retVal = new ArrayList();
DataRow sourceRow = tbl.Rows[sourceNdx];
for(int i=sourceNdx + 1; i<tbl.Rows.Count; i++)
{
DataRow targetRow = tbl.Rows[i];
if(IsDup(sourceRow, targetRow, keyColumns))
{
retVal.Add(targetRow);
}
}
return (DataRow[]) retVal.ToArray(typeof(DataRow));
}
private static bool IsDup(DataRow sourceRow,
DataRow targetRow,
DataColumn[] keyColumns)
{
bool retVal = true;
foreach(DataColumn column in keyColumns)
{
retVal = retVal && sourceRow[column].Equals(targetRow[column]);
if(!retVal) break;
}
return retVal;
}
private static void PrintRows(DataTable tbl)
{
for(int i=0; i<tbl.Rows.Count; i++)
{
Console.WriteLine("row: {0}, ColumnA: {1}, ColumnB: {2}", i, tbl.Rows[i]["ColumnA"], tbl.Rows[i]["ColumnB"]);
}
}
}
}
--------- End Demo Code ----------------
using System;
using System.Data;
using System.Collections;
namespace Demo_DataTableRemoveDupRows
{
class Class1
{
[STAThread]
static void Main(string[] args)
{
// create an example datatable with duplicate rows
DataTable tbl = new DataTable();
tbl.Columns.Add("ColumnA");
tbl.Columns.Add("ColumnB");
tbl.Columns.Add("ColumnC");
for(int i = 0; i<10; i++)
{
DataRow nr = tbl.NewRow();
nr["ColumnA"] = "A" + i.ToString();
nr["ColumnB"] = "B" + i.ToString();
nr["ColumnC"] = "C" + i.ToString();
tbl.Rows.Add(nr);
// duplicate
nr = tbl.NewRow();
nr["ColumnA"] = "A" + i.ToString();
nr["ColumnB"] = "B" + i.ToString();
nr["ColumnC"] = "C" + i.ToString();
tbl.Rows.Add(nr);
}
PrintRows(tbl); // show table with duplicates
//Create an array of DataColumns to compare
//If these columns all match we consider the
//rows duplicate.
DataColumn[] keyColumns =
new DataColumn[]{tbl.Columns["ColumnA"],
tbl.Columns["ColumnA"]};
//remove the duplicates
RemoveDuplicates(tbl, keyColumns);
PrintRows(tbl); // show table again
Console.ReadLine(); // pause to view output
}
/// <summary>
/// Removes duplicate rows from given DataTable
/// </summary>
/// <param name="tbl">Table to scan for duplicate rows</param>
/// <param name="KeyColumns">An array of DataColumns
/// containing the columns to match for duplicates</param>
private static void RemoveDuplicates(DataTable tbl,
DataColumn[] keyColumns)
{
int rowNdx = 0;
while(rowNdx < tbl.Rows.Count-1)
{
DataRow[] dups = FindDups(tbl, rowNdx, keyColumns);
if(dups.Length>0)
{
foreach(DataRow dup in dups)
{
tbl.Rows.Remove(dup);
}
}
else
{
rowNdx++;
}
}
}
private static DataRow[] FindDups(DataTable tbl,
int sourceNdx,
DataColumn[] keyColumns)
{
ArrayList retVal = new ArrayList();
DataRow sourceRow = tbl.Rows[sourceNdx];
for(int i=sourceNdx + 1; i<tbl.Rows.Count; i++)
{
DataRow targetRow = tbl.Rows[i];
if(IsDup(sourceRow, targetRow, keyColumns))
{
retVal.Add(targetRow);
}
}
return (DataRow[]) retVal.ToArray(typeof(DataRow));
}
private static bool IsDup(DataRow sourceRow,
DataRow targetRow,
DataColumn[] keyColumns)
{
bool retVal = true;
foreach(DataColumn column in keyColumns)
{
retVal = retVal && sourceRow[column].Equals(targetRow[column]);
if(!retVal) break;
}
return retVal;
}
private static void PrintRows(DataTable tbl)
{
for(int i=0; i<tbl.Rows.Count; i++)
{
Console.WriteLine("row: {0}, ColumnA: {1}, ColumnB: {2}", i, tbl.Rows[i]["ColumnA"], tbl.Rows[i]["ColumnB"]);
}
}
}
}
--------- End Demo Code ----------------
copy Outlook Calendar items from one folder to another
For Outlook 2010
Clearing the filter from an existing view
To clear the filter from an existing tabular view, follow these steps:
- Click to select the Outlook Calendar folder.
- On the View tab, click Change view, and then click to select one of the tabular views such as By List or ByCategory.
- On the View menu, click Reset View.
- Click Yes to reset the view to the original settings.
Defining a new tabular view
To define a new permanent tabular view, follow these steps:
- Click to select the Calendar folder.
- On the View tab, click Change view, click Manage Views, and then click New.
- Type a name for the new view, click to select Table, and then click OK.
- In Advanced View Settings, click Sort.
- In the Sort items by list, click to select Start and Ascending.
- In the Then By list, click to select (none), and then click OK.
- Click OK, and then click Apply View.
Either of the previous procedures will display a list of all Calendar items.
After all items are displayed, follow these steps:
- To select all items, use the keyboard shortcut, CONTROL+A.
- To copy the items, use the keyboard shortcut, CONTROL+C.
- Click to select the destination folder.
Note To paste the items correctly, the destination folder must be a calendar folder. - To paste the items, use the keyboard shortcut, CONTROL+V.
This process inserts a copy of all the Calendar items into the destination folder.
Additional option for Outlook 2010
- On the Folder tab, click Copy Calendar.
- Select the folder location where you want the calendar to be saved, and then click OK.
NoteTo copy the items correctly, the destination folder must be a calendar folder.
Back to the topFor Outlook 2007, 2003, 2002, 2000
Clearing the filter from an existing view
To clear the filter from an existing tabular view, follow these steps:
- Click to select the Outlook Calendar folder.
- On the View menu, point to Current View, and then click to select one of the tabular views such as Events or By Category.
- On the View menu, point to Current View, and then click Customize Current View.
- In the View Summary dialog box, click Filter, click Clear All, and then click OK twice.
Defining a new tabular view
To define a new permanent, tabular view, follow these steps:
- Click to select the Calendar folder.
- On the View menu, point to Current View, click Define Views, and then click New.
- Type a name for the new view, click to select Table, and then click OK.
- In View Summary, click Sort.
- In the Sort items by list, click to select Start and Ascending.
- In the Then By list, click to select (none), and then click OK.
- Click OK, and then click Apply View.
Either one of the previous procedures will display a list of all Calendar items.
After all items are displayed, follow these steps:
- On the Edit menu, click Select All.
- On the Edit menu, click Copy.
- Click to select the destination folder.
- On the Edit menu, click Paste.
This process inserts a copy of all the Calendar items into the destination folder.
check if node exists
using System; using System.IO; using System.Xml; public class Sample { public static void Main() { XmlDocument doc = new XmlDocument(); doc.Load("booksort.xml"); //Select the book node with the matching attribute value. XmlNode nodeToFind; XmlElement root = doc.DocumentElement; // Selects all the title elements that have an attribute named lang nodeToFind = root.SelectSingleNode("//title[@lang]"); if( nodeToFind != null ) { // It was found, manipulate it. } else { // It was not found. } } } |
Monday 11 June 2012
Import Excel Sheet to SharePoint List
In this article, we will learn how to export the excel data to the SharePoint list.
Steps:
1. Click on Site Actions -> More Options
2. Now click on the Lists -> Select Import Spread Sheet from the type -> then Click Create
3. Give the Name and Description of the List to be created and then browse the Excel Sheet to be uploaded
4. Clicking on Import option, you will find see a popup window opens. Select Range of Cells in the Range Type
5. This option allows you to select the table columns and rows in excel as shown below. After selecting, you will see the selected field’s column and row data being populated in the Select Range field in the popup window
6. Now click on Import in the popup window. Your SharePoint list will be created from the Excel Sheet and the values are also populated as shown below:
You may face some issues while importing the Excel Sheet to SharePoint and the solution is follows:
Error: “Method ‘Post’ of object ‘IOWSPostData’ failed” in SharePoint
Solution:
Ø Open the Excel Sheet Add-In EXPTOOWS.XLA which will is available at the following location C:\Program Files\Microsoft Office\Office12\1033 by default.
Ø Press Alt+F11 to display the Visual Basic code editor. Locate the form named "PublishForm" under the folder "Forms" and open the code view of this form.
Ø Search (Ctrl+F) for the line "lVer = Application.SharePointVersion(URL)" and place a new line "lVer=2" after that line.
Ø Now try to import the Excel Sheet to the SharePoint, you will not get any errors.
Error: "The specified file is not a valid spreadsheet or contains no data to import"
Solution:
This error occurs when the site you are opening do not have permission to file system. To give permission follow the below steps:
In Internet Explorer click Tools --> Internet Options -> Click Security tab --> Select Trusted Sites --> In the Sites add your URL here.
Now you will be able to import the file properly.
Hope this post helps you! Please free to comment and share this post.
Friday 8 June 2012
How to Find the Duplicate Values in ArrayList
contacts.Sort();
for (int i=1; i <= contacts.Count-1; i++)
{
Console.WriteLine(contacts[ i ]);
Console.WriteLine(contacts[ i-1] );
if(contacts[ i ].ToString() == contacts[ i-1 ].ToString())
{
Console.WriteLine("Duplicate: "+contacts[ i ]);
}
}
for (int i=1; i <= contacts.Count-1; i++)
{
Console.WriteLine(contacts[ i ]);
Console.WriteLine(contacts[ i-1] );
if(contacts[ i ].ToString() == contacts[ i-1 ].ToString())
{
Console.WriteLine("Duplicate: "+contacts[ i ]);
}
}
Finding Duplicates with SQL
Here's a handy query for finding duplicates in a table. Suppose you want to find all email addresses in a table that exist more than once:
You could also use this technique to find rows that occur exactly once:
SELECT email,
COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )
You could also use this technique to find rows that occur exactly once:
SELECT email
FROM users
GROUP BY email
HAVING ( COUNT(email) = 1 )
The difference between Public, Private, Protected, and Nothing?
Access modifiers
publicThe type or member can be accessed by any other code in the same assembly or another assembly that references it.
privateThe type or member can only be accessed by code in the same class or struct.
protectedThe type or member can only be accessed by code in the same class or struct, or in a derived class.
internalThe type or member can be accessed by any code in the same assembly, but not from another assembly.
protected internalThe type or member can be accessed by any code in the same assembly, or by any derived class in another assembly.
Static
The static modifier on a class means that the class cannot be instantiated, and that all of its members are static. A static member has one version regardless of how many instances of its enclosing type are created.
A static class is basically the same as a non-static class, but there is one difference: a static class cannot be instantiated. In other words, you cannot use the new keyword to create a variable of the class type. Because there is no instance variable, you access the members of a static class by using the class name itself.
Static classes are often used as services, you can use them like so:
MyStaticClass.ServiceMethod(...);
Subscribe to:
Posts (Atom)