Archive for the ‘DateTime’ Category

SQL Get First And Last Day of a week-Month-Quarter-Year

In many reports we may need to get data periodically or within specified date interval so we need a method to calculate the end and start of months, weeks or days. In this post we will discuss some of this methods through the following examples. First: Get First day for any month . we can […]

More »

TSQL Number of Days In a Given Month

TSQL Number of Days In a Given Month. The Following query will get the number of days in a month for a given date this query will consider Leap years –Declare Variable to set datetime Declare @GivenDate DATETIME=getdate(); select CASE WHEN MONTH(@GivenDate) IN (1, 3, 5, 7, 8, 10, 12) THEN 31 WHEN MONTH(@GivenDate) IN […]

More »

Datepart Options And Abbreviations

The following table lists the datepart options and abbreviations recognized by SQL Server Compact 3.5. Datepart Abbreviations Year yy, yyyy quarter qq, q month mm, m dayofyear dy, y day dd, d week wk, ww weekday* dw hour hh minute mi, n second ss, s millisecond ms

More »

How to format datetime & date in Sql Server

Run the following queries to show various types of date time formats in SQL server Declare @d datetime select @d = getdate() select @d as OriginalDate, convert(varchar,@d,100) as ConvertedDate, 100 as FormatValue, ‘mon dd yyyy hh:miAM (or PM)’ as OutputFormat union all select @d,convert(varchar,@d,101),101,’mm/dd/yy’ union all select @d,convert(varchar,@d,102),102,’yy.mm.dd’ union all select @d,convert(varchar,@d,103),103,’dd/mm/yy’ union all select […]

More »

DateTime Operators

Using Operators with Date and Time Data Types The relational operators (=, ), comparison operators (=, =, , !) and logical operators and Boolean predicates (IS NULL, IS NOT NULL, IN, BETWEEN, EXISTS, NOT EXISTS, and LIKE) are supported for all the date and time data types. Possible Usage use [AdventureWorks] select * from Person.Contact […]

More »