Date parts in SQL

Datepart (part,date), 2. Dateadd (datepart,date,number) 3. Datediff (datepart,start date,End date)
SELECT GETDATE() 'Today' --0->1-1-1900

SELECT DATEADD(d,-1,GETDATE()) 'Yesterday'

SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) 'First Day of Current Week'

SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6) 'Last Day of Current Week'

SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) 'First Day of Last Week'

SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6) 'Last Day of Last Week'

SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) 'First Day of Current Month'

SELECT DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))) 'Last Day of Current Month'

SELECT DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) 'First Day of Last Month'

SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))) 'Last Day of Last Month'

SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0) 'First Day of Current Year'

SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0))) 'Last Day of Current Year'

SELECT DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) 'First Day of Last Year'

SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))) 'Last Day of Last Year'

Comments

Post a Comment

Popular posts from this blog

Who Deleted and Drop the record in table

CREATE DATABASE ReadingDBLog; GO -- Create tables. USE ReadingDBLog; GO CREATE TABLE [Location] (     [Sr.No] INT IDENTITY,     [Date] DATETIME DEFAULT GETDATE (),     [City] CHAR (25) DEFAULT 'Bangalore');           select * from [Location]       INSERT INTO Location DEFAULT VALUES ; GO 100 DELETE Location WHERE [Sr.No]=10 GO SELECT * FROM Location WHERE [Sr.No]=10 GO SELECT     [Transaction ID],     Operation,     Context,     AllocUnitName   FROM     fn_dblog(NULL, NULL) WHERE     Operation = 'LOP_DELETE_ROWS'       SELECT     Operation,     [Transaction ID],     [Begin Time],     [Transaction Name],     [Transaction SID] FROM     fn_dblog(NULL, NULL) WHERE     [Transaction ID] = '0000:000002ef' AND     [Operat...
Read more

Generate Password

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[SP_GeneratePassword](@Length int, @PasswordTemp varchar(100) output) AS BEGIN DECLARE @RandomID varchar(32) DECLARE @counter smallint DECLARE @RandomNumber float DECLARE @RandomNumberInt tinyint DECLARE @CurrentCharacter varchar(1) DECLARE @ValidCharacters varchar(255) SET @ValidCharacters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789' DECLARE @ValidCharactersLength int SET @ValidCharactersLength = len(@ValidCharacters) SET @CurrentCharacter = '' SET @RandomNumber = 0 SET @RandomNumberInt = 0 SET @RandomID = '' SET NOCOUNT ON SET @counter = 1 WHILE @counter < (@Length + 1) BEGIN         SET @RandomNumber = Rand()         SET @RandomNumberInt = Convert(tinyint, ((@ValidCharactersLength - 1) * @RandomNumber + 1))         SELECT @CurrentCharacter = SUBSTRING(@ValidCharacters, @RandomNumberInt, 1) ...
Read more

Key Board Shortcuts

Image
100 KEYBOARD SHORTCUTS CTRL + C (Copy) CTRL + X (Cut) CTRL + V (Paste) CTRL + Z (Undo) DELETE (Delete) SHIFT + DELETE (Delete the selected item permanently without placing the item in the Recycle Bin) CTRL while dragging an item (Copy the selected item) CTRL + SHIFT while dragging an item (Create a shortcut to the selected item) F2 KEY (Rename the selected item) CTRL + RIGHT ARROW (Move the insertion point to the beginning of the next word) CTRL + LEFT ARROW (Move the insertion point to the beginning of the previous word) CTRL + DOWN ARROW (Move the insertion point to the beginning of the next paragraph) CTRL + UP ARROW (Move the insertion point to the beginning of the previous paragraph) CTRL + SHIFT with any of the arrow keys (Highlight a block of text) SHIFT with any of the arrow keys (Select more than one item in a window or on the desktop, or select text in a document) CTRL + A (Select all) F3 KEY (Search for a file or a folder) ALT...
Read more