sql datetime()

SQL DateTime



1. SELECT YEAR('2007-04-30T01:01:01.1234567-07:00')

2. select YEAR( Y.DateAdded) as Year
from  ( select DateAdded from vw_Product) as Y

3.
select 'Jan' as Month, 1 as ID union
select 'Feb' as Month, 2 as ID union
select 'Mar' as Month, 3 as ID union
select 'Apr' as Month, 4 as ID union
select 'May' as Month, 5 as ID union
select 'Jun' as Month, 6 as ID union
select 'Jul' as Month, 7 as ID union
select 'Aug' as Month, 8 as ID union
select 'Sep' as Month, 9 as ID union
select 'Oct' as Month, 10 as ID union
select 'Nov' as Month, 11 as ID union
select 'Dec' as Month, 12 as ID order by ID
  end

3. select * from LabDoc

where Addedby='Sourov' and DateAdded='2012-10-20 10:24:22.930'

4. select * from LabDoc where

CONVERT(Varchar(11), DateAdded, 100) =

CONVERT(Varchar(11), CAST('2012-10-20' as Date), 100)

5. select * from Employee_Att 
where EmployeeID='LPL00062' and 
CONVERT(Varchar(7), ATTDate, 126 ) = 
CONVERT(Varchar(7), CAST('2012-11-20' as Date), 126) 
and AttStatus='A'



6. select * from Employee_Att 
where EmployeeID='LPL00062' 
and CONVERT(Varchar(7), ATTDate, 126 ) = 
CONVERT(Varchar(7), CAST('2012-11-21 08:54:25.000' as Date), 126) 
and AttStatus='A'

7. Update LabDoc 
SET MPersonName='Sourov',
MPersonDesignation = 'Sr. Software Engineer'
where 
Convert(Varchar(11), DateAdded, 100) = 
Convert(Varchar(11), Cast('2012-12-4' as Date), 100)

8.-- By Year and Month

select * from Employee_Att where EmployeeID='LPL00046' and 
convert(varchar(7), ATTDate, 126) = 
convert(varchar(7), CAST('2012-12-12' as Date), 126)

9. -- By Date Between

Select DoctorCode from LabDoc where Addedby='***' and  DateAdded between CONVERT(varchar(11), CAST('2013-5-1' as Date), 100) and 
CONVERT(varchar(11), CAST('2013-5-28' as Date), 100)
------------------------------------------------------------------------------------------------

-- SQL datetime formats with century (YYYY or CCYY format)- sql time format 
SELECT convert(varchar, getdate(), 100) -- mon dd yyyy hh:mmAM (or PM) 

-- Oct 2 2010 11:01AM 
SELECT convert(varchar, getdate(), 101) -- mm/dd/yyyy - 10/02/2010 
SELECT convert(varchar, getdate(), 102) -- yyyy.mm.dd - 2010.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 2010 11:02:44:013AM 
SELECT convert(varchar, getdate(), 110) -- mm-dd-yyyy 
SELECT convert(varchar, getdate(), 111) -- yyyy/mm/dd 
-- yyyymmdd - ISO date format - international standard - works with any language setting 
SELECT convert(varchar, getdate(), 112) -- yyyymmdd 
SELECT convert(varchar, getdate(), 113) -- dd mon yyyy hh:mm:ss:mmm 

-- 02 Oct 2010 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 

-- 2010-10-02T10:52:47.513
-- Without century (YY) date / datetime conversion - there are exceptions!
SELECT convert(varchar, getdate(), 0) -- mon dd yyyy hh:mmAM (or PM) 
SELECT convert(varchar, getdate(), 1) -- mm/dd/yy 
SELECT convert(varchar, getdate(), 2) -- yy.mm.dd 
SELECT convert(varchar, getdate(), 3) -- dd/mm/yy
SELECT convert(varchar, getdate(), 4) -- dd.mm.yy
SELECT convert(varchar, getdate(), 5) -- dd-mm-yy 
SELECT convert(varchar, getdate(), 6) -- dd mon yy 
SELECT convert(varchar, getdate(), 7) -- mon dd, yy 
SELECT convert(varchar, getdate(), 8) -- hh:mm:ss 
SELECT convert(varchar, getdate(), 9) -- mon dd yyyy hh:mm:ss:mmmAM (or PM) 
SELECT convert(varchar, getdate(), 10) -- mm-dd-yy 
SELECT convert(varchar, getdate(), 11) -- yy/mm/dd 
SELECT convert(varchar, getdate(), 12) -- yymmdd 
SELECT convert(varchar, getdate(), 13) -- dd mon yyyy hh:mm:ss:mmm 
SELECT convert(varchar, getdate(), 14) -- hh:mm:ss:mmm(24h) 
SELECT convert(varchar, getdate(), 20) -- yyyy-mm-dd hh:mm:ss(24h) 
SELECT convert(varchar, getdate(), 21) -- yyyy-mm-dd hh:mm:ss.mmm 
SELECT convert(varchar, getdate(), 22) -- mm/dd/yy hh:mm:ss AM (or PM)
SELECT convert(varchar, getdate(), 23) -- yyyy-mm-dd
SELECT convert(varchar, getdate(), 24) -- hh:mm:ss 
SELECT convert(varchar, getdate(), 25) -- yyyy-mm-dd hh:mm:ss.mmm 

-- 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
SELECT substring(convert(varchar, getdate(), 120),6, 11) -- mm-dd hh:mm


-- SQL Server 2000/2005
SELECT
CONVERT(VARCHAR(8),GETDATE(),108) AS HourMinuteSecond,
CONVERT(VARCHAR(10),GETDATE(),101) AS DateOnly;
GO

-- SQL Server 2008 Onwards
SELECT CONVERT(TIME,GETDATE()) AS HourMinuteSeconds;
SELECT CONVERT(DATE,GETDATE()) AS DateOnly;

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

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 @d,convert(varchar,@d,104),104,'dd.mm.yy'
union all
select @d,convert(varchar,@d,105),105,'dd-mm-yy'
union all
select @d,convert(varchar,@d,106),106,'dd mon yy'
union all
select @d,convert(varchar,@d,107),107,'Mon dd, yy'
union all
select @d,convert(varchar,@d,108),108,'hh:mm:ss'
union all
select @d,convert(varchar,@d,109),109,'mon dd yyyy hh:mi:ss:mmmAM (or PM)'
union all
select @d,convert(varchar,@d,110),110,'mm-dd-yy'
union all
select @d,convert(varchar,@d,111),111,'yy/mm/dd'
union all
select @d,convert(varchar,@d,112),112,'yymmdd'
union all
select @d,convert(varchar,@d,113),113,'dd mon yyyy hh:mm:ss:mmm(24h)'
union all
select @d,convert(varchar,@d,114),114,'hh:mi:ss:mmm(24h)'
union all
select @d,convert(varchar,@d,120),120,'yyyy-mm-dd hh:mi:ss(24h)'
union all
select @d,convert(varchar,@d,121),121,'yyyy-mm-dd hh:mi:ss.mmm(24h)'
union all
select @d,convert(varchar,@d,126),126,'yyyy-mm-dd Thh:mm:ss:mmm(no spaces)'
-------------------------
SQL SERVER –
Few Useful DateTime Functions to Find Specific Dates

----Today
SELECT GETDATE() 'Today'
----Yesterday
SELECT DATEADD(d,-1,GETDATE()) 'Yesterday'
----First Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)'First Day of Current Week'
----Last Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6) 'Last Day of Current Week'
----First Day of Last Week
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) 'First Day of Last Week'
----Last Day of Last Week
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6) 'Last Day of Last Week'
----First Day of Current Month
SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)'First Day of Current Month'
----Last Day of Current Month
SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)))'Last Day of Current Month'
----First Day of Last Month
SELECT DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))'First Day of Last Month'
----Last Day of Last Month
SELECTDATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)))'Last Day of Last Month'
----First Day of Current Year
SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)'First Day of Current Year'
----Last Day of Current Year
SELECTDATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)))'Last Day of Current Year'
----First Day of Last Year
SELECT DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))'First Day of Last Year'
----Last Day of Last Year
SELECTDATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)))'Last Day of Last Year'

sp

USE [DM]
GO
/****** Object:  StoredProcedure [dbo].[spSalesTarget]    Script Date: 10/17/2012 09:41:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spSalesTarget]
 --@QType int=0, @ID Varchar(50)=NULL,  @IDName Varchar(100)=NULL
@QType int=0, @Year int=NULL,  @Month int=NULL

AS
BEGIN
if(@QType=0)
begin
Select ProductID,ProductName,GenericName,PackSize,TP  from dbo.vw_Product

Select '2010' as Year union select '2011' as year union Select '2012' as Year union Select '2013' as Year union select '2014' as year union
Select '2015' as Year union select '2016' as year union Select '2017' as Year union select '2018' as year union Select '2019' as Year union
select '2020' as year

select 'Jan' as Month, 1 as ID union select 'Feb' as Month, 2 as ID union select 'Mar' as Month, 3 as ID union select 'Apr' as Month, 4 as ID union
select 'May' as Month, 5 as ID union select 'Jun' as Month, 6 as ID union select 'Jul' as Month, 7 as ID union select 'Aug' as Month, 8 as ID union
select 'Sep' as Month, 9 as ID union select 'Oct' as Month, 10 as ID union select 'Nov' as Month, 11 as ID union select 'Dec' as Month, 12 as ID order by ID

--select * from STMaster

--Select * from STChild
END
if(@QType=1)
BEGIN
select Year, Month, IsLocked, Location, MachineNameIP, Transfered, HOTransfered, Addedby, DateAdded, Updatedby, DateUpdated
from STMaster where Year=@Year and Month=@Month

select s.Year, s.Month, s.ProductID,   p.ProductName, p.GenericName, p.PackSize, s.TP, s.TargetQty, s.actualqty, s.TotalValue,  s.actualvalue , s.Location, s.MachineNameIP, s.Transfered, s.HOTransfered, s.Addedby, s.DateAdded, s.Updatedby, s.DateUpdated
from STChild as s  join vw_Product as p on  s.ProductID = p.ProductID
      where Year=@Year and Month=@Month

END
if(@QType=2)
BEGIN
declare @PreDate datetime,@PreYear int,@PreMonth int
select @PreDate=DATEADD("dd",-1,convert(datetime,CONVERT(varchar(10),@Month)+'/01/'+CONVERT(varchar(10),@Year),101))
Select @PreYear=YEAR(@PreDate)
Select @PreMonth=Month(@PreDate)
--select @PreDate,@PreYear,@PreMonth
---
select s.Year, s.Month, s.ProductID,   p.ProductName, p.GenericName, p.PackSize, s.TP, s.TargetQty, s.actualqty, s.TotalValue,  s.actualvalue , s.Location, s.MachineNameIP, s.Transfered, s.HOTransfered, s.Addedby, s.DateAdded, s.Updatedby, s.DateUpdated
from STChild as s  join vw_Product as p on  s.ProductID = p.ProductID
      where Year=@PreYear and Month=@PreMonth

END
END

sql query 2012

1. Max Value
select MAX(NoofTabCap) from ProductGenerics

4. 2nd Height Value

select MAX(NoofTabCap) from ProductGenerics
where NoofTabCap != (select MAX(NoofTabCap) from ProductGenerics )



5.  create procedure
create procedure InsertCustomer
(  
    @Name nvarchar(50),
    @Address nvarchar(50)
)
as insert into dbo.Customer(Name,Address) VALUES(@Name,@Address)

use test
exec dbo.InsertCustomer  @Name=aa, @address=bb


6.  Sum of 2 Number
CREATE FUNCTION fx_SumTwoValues
( @Val1 int, @Val2 int )
RETURNS int
AS
BEGIN
  RETURN (@Val1+@Val2)
END

SELECT dbo.fx_SumTwoValues(12,12) AS SumOfTwoValues


7. DateDiff function is used to calculate the number of days between the two dates.

CREATE FUNCTION DaysInYear (@Year INT)
RETURNS INT
AS
BEGIN
    DECLARE @Jan1 DATETIME
    DECLARE @NextJan1 DATETIME
    IF @Year < 1753 OR @Year > 9998
        RETURN -1
    SET @Jan1 = dateadd(yyyy, @Year-1900, 0)
    SET @NextJan1 = dateadd(yyyy, 1, @Jan1)
    RETURN datediff(d, @Jan1, @NextJan1)
END

PRINT dbo.DaysInYear(2012)  -- 366
PRINT dbo.DaysInYear(2011)  -- 365
PRINT dbo.DaysInYear(1752)  -- -1


8. How to found deuplicate value in the table

With the SQL statement below you can find duplicate values in any table, just change the tablefield into the column you want to search and change the table into the name of the table you need to search.

In your recordset you will see the tablefield and how many times it is found as a duplicate.

SELECT     tablefield, COUNT(tablefield) AS dup_count
FROM         table
GROUP BY tablefield
HAVING     (COUNT(tablefield) > 1)

Some further tempering with the statement gets the complete records that are double. (yeah yeah.. no * should be used in the SELECT) It's just for demonstrating folks!!

SELECT *
FROM table
WHERE tablefield IN (
 SELECT tablefield
 FROM table
 GROUP BY tablefield
 HAVING (COUNT(tablefield ) > 1)
)



9. Pass valid year and month
int totalDays = DateTime.DaysInMonth(2007, 6);



10. Query to Find First and Last Day of Current Month

DECLARE @mydate DATETIME
SELECT @mydate = GETDATE()
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)),@mydate),101) ,
'Last Day of Previous Month'
UNION

SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101) AS Date_Value,
'First Day of Current Month' AS Date_Type
UNION

SELECT CONVERT(VARCHAR(25),@mydate,101) AS Date_Value, 'Today' AS Date_Type
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)),101) ,
'Last Day of Current Month'
UNION

SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))-1),DATEADD(mm,1,@mydate)),101) ,
'First Day of Next Month'
GO


-----------------------------------------------------------------------------------------------------------
DECLARE @date DATETIME
SET @date='2008-02-03'
SELECT DATEADD(dd, -DAY(DATEADD(m,1,@date)), DATEADD(m,1,@date))
            AS LastDayOfMonth
GO
-----------------------------------------------------------------------------------------------------------
DECLARE @date DATETIME
    SET @date='2007-02-03'
SELECT DATEADD(dd, -DAY(DATEADD(m,1,@date)), DATEADD(m,1,@date))
            AS LastDayOfMonth
GO

Output

LastDayOfMonth
-----------------------
2008-02-29 00:00:00.000

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




-----------------------------------------------------------------------------------------------------------
if(@QType=1)
  BEGIN

 
select s.Year, s.Month, s.ProductID,   p.ProductName, p.GenericName, p.PackSize, s.TP, s.TargetQty, s.actualqty, s.TotalValue,  s.actualvalue , s.Location, s.MachineNameIP, s.Transfered, s.HOTransfered, s.Addedby, s.DateAdded, s.Updatedby, s.DateUpdated
from STChild as s  join vw_Product as p on  s.ProductID = p.ProductID
where Year=@Year and Month=@Month
 
  END


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

Operator Precedence

DECLARE @MyNumber int
SET @MyNumber = 2 * (4 + (5 - 3) )
-- Evaluates to 2 * (4 + 2) which then evaluates to 2 * 6, and 
-- yields an expression result of 12.
SELECT @MyNumber

DECLARE @MyNumber int
SET @MyNumber = 2 * (4 + 5)
-- Evaluates to 2 * 9 which yields an expression result of 18.
SELECT @MyNumber

DECLARE @MyNumber int
SET @MyNumber = 2 * 4 + 5
-- Evaluates to 8 + 5 which yields an expression result of 13.
SELECT @MyNumber

DECLARE @MyNumber int
SET @MyNumber = 4 - 2 + 27
-- Evaluates to 2 + 27 which yields an expression result of 29.
SELECT @MyNumber

-----------------------------------------------------------------------------------------------------------
Report 

if {rptDiscount;1.IsActive}=false
then chrw(168)
else chrw(254)
Font : wingdings


------------------------------------------------------------------------------------------------------------
Delete all but top n from database table in SQL

DELETE FROM Table WHERE ID NOT IN (SELECT TOP 10 ID FROM Table)
select * from AdjustmentChild
DELETE FROM AdjustmentChild WHERE AdjustmentID NOT IN (SELECT TOP 70 AdjustmentID FROM AdjustmentChild)

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

Delete Top Data from Table
use xsd
Select * from B_DailyCallReportMaterial

use xsd
SELECT sysobjects.Name AS TableName, sysindexes.ROWS AS TotalRows
FROM sysobjects
INNER JOIN sysindexes ON sysobjects.id = sysindexes.id
WHERE TYPE = 'U' AND sysindexes.IndId < 2
ORDER BY sysindexes.ROWS DESC


use xsd
delete top (9911116) from bak_salesorderdetail
------------------------------------------------------------------------------------------------------------