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'
No comments:
Post a Comment