There are three ways to retrieve the current DateTime in SQL SERVER. CURRENT_TIMESTAMP, GETDATE(), {fn NOW()}
CURRENT_TIMESTAMP
CURRENT_TIMESTAMP is a nondeterministic function. Views and expressions that reference this column cannot be indexed. CURRENT_TIMESTAMP can be used to print the current date and time every time that the report is produced.
GETDATE()
GETDATE is a nondeterministic function. Views and expressions that reference this column cannot be indexed. GETDATE can be used to print the current date and time every time that the report is produced.
{fn Now()}
The {fn Now()} is an ODBC canonical function which can be used in T-SQL since the OLE DB provider for SQL Server supports them. {fn Now()} can be used to print the current date and time every time that the report is produced.
If you run following script in Query Analyzer. I will give you the same results. If you see the execution plan there is no performance difference. It is the same for all the three select statements.
SELECT CURRENT_TIMESTAMP
GO
SELECT {fn NOW()}
GO
SELECT GETDATE()
GOPerformance:
There is absolutely no difference in using any of them. As they are absolutely the same.
My Preference:
I like GETDATE(). Why? Why bother when they are the same!!!
Quick Video on the same subject about datetime
[youtube=http://www.youtube.com/watch?v=BL5GO-jH3HA]Reference: Pinal Dave (https://blog.sqlauthority.com)
458 Comments. Leave new
im doing proj in .Net (c#)..i want execution time of query..how do i get it ? Also plz gv tips on tuning of queries..
Is it possible to insert on column with date fields that insert automatical after other registry on the same table was inserted?
Regards,
Pinal,
I have a table which stores daily transactions and I’m trying to generate a report that captures only transactions less than two weeks from the run date of the report and using ztransactiondate = GETDATE()-14 which is not working to get my results. Any suggestions,
Thanks,
Raul
Hello Raul,
Use greater than (>) operator to get all transaction of last two weeks as below:
ztransactiondate >= GETDATE()-14
Regards,
Pinal Dave
Hello sir…
Good Morning…
I want to Display Birthdate of My Site User On My Home Page
That Information Give ago 3 Days
Example
My Client Name Is :- Sachin
His Birth DAte :- 05-12-1987
Today Date Is :- 3-12-2009
So, Today On My Home Page Display “Sachin” Name Nad His BirthDate…
Thanks
Regards
[Removed phone and mobile number]
— SQL Server T-SQL date and datetime formats – sql date / datetime format
— Date time formats – mssql datetime – sql server date formats – sql dates format
— MSSQL getdate returns current system date and time in standard internal format
— 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
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 date formatting function – convert datetime to string
————
— SQL datetime functions – SQL date functions – SQL datetime formatting
— SQL Server date formats – sql server date datetime – sql date formatting
— T-SQL convert dates – T-SQL date formats – Transact-SQL date formats
— Formatting dates sql server – sql convert datetime format
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 – sql datetime date
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 – – sql hh mm ss – sql yyyy mm dd
— 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: 2010-10-23T19:20:16.003
SELECT [ISO8601]=CONVERT(varchar,GETDATE(),126)
————
— SQL Server datetime formats – Format dates SQL Server 2005 / 2008
— Century date format MM/DD/YYYY usage in a query
SELECT TOP (1)
SalesOrderID,
OrderDate = CONVERT(char(10), OrderDate, 101),
OrderDateTime = OrderDate
FROM AdventureWorks.Sales.SalesOrderHeader
/*
SalesOrderID OrderDate OrderDateTime
43697 07/01/2001 2001-07-01 00:00:00.000
*/
— SQL update datetime column – SQL datetime DATEADD – datetime function
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
/*
SalesOrderID OrderDate OrderDateTime
75123 07/31/04 2004-07-31 00:00:00.000
*/
————
— SQL convert datetime to char – sql date string concatenation: + (plus) operator
PRINT ‘Style 110: ‘+CONVERT(CHAR(10),GETDATE(),110) — Style 110: 07-10-2012
PRINT ‘Style 111: ‘+CONVERT(CHAR(10),GETDATE(),111) — Style 111: 2012/07/10
PRINT ‘Style 112: ‘+CONVERT(CHAR(8), GETDATE(),112) — Style 112: 20120710
————
— Combining different style formats for date & time
— Datetime formats – sql times format – 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
————
— T-SQL first day of week and last day of week
SELECT FirstDateOfWeek = dateadd(dd,-DATEPART(dw,GETDATE()) + 1,GETDATE())
SELECT LastDateOfWeek = dateadd(dd,7 – DATEPART(dw,GETDATE()),GETDATE())
— 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 – Format dates SQL Server 2008
— SQL table-valued function – SQL user-defined function – UDF
— SQL Server age calculation – date difference
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….= ‘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 (4 bytes) date range:
January 1, 1900 through June 6, 2079
DATETIME (8 bytes) date range:
January 1, 1753 through December 31, 9999
DATETIME2 (8 bytes) date range (SQL Server 2008):
January 1,1 AD through December 31, 9999 AD
DATE (3 bytes) 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 = ‘1997-11-01’ AND
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
————
— SQL Server 2008 T-SQL find next Monday for a given date
DECLARE @DateTime DATETIME = '2012-12-31'
SELECT NextMondaysDate=DATEADD(dd,(DATEDIFF(dd, 0, @DateTime) / 7 * 7) + 7, 0),
WeekDayName=DATENAME(dw,DATEADD(dd,(DATEDIFF(dd, 0, @DateTime) / 7 * 7) + 7, 0));
/*
NextMondaysDate WeekDayName
2013-01-07 00:00:00.000 Monday
*/
————
————
— 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
————
Hi Pinal,
How can i fetch the current datetime using user defined function? I think we may not user getdate() function with in the user defined function.thanks in advance.
Create a view like
create view date_view
as
select getdate() as today
Now use this view in your function to retreive current date
Thanks Madhivanan.
Hello Pinal,
I want to write a function for daylight saving. can you tell me how to find out last sunday of month in sql 2000.
Regard,
Ajay
@Ajay
SELECT
DATEADD
(
d,
CASE DATEPART(dw, Next_Month.Date)
WHEN 1 THEN 7
ELSE DATEPART(dw, Next_Month.Date) – 1
END * -1,
Next_Month.Date
)
FROM
(SELECT DATEADD(m, DATEDIFF(m, 0, GETDATE()) + 1, 0) Date) Next_Month;
datename(month,getdate()) as month
O/p: march
Note month name will come with this use it
i inserted 400 records in to sql server 2008 in particular day. after some day i realised that inserted data was wrong. how to delete that data.
@SIVA NANDA REDDY
If you have a date in the data
DELETE FROM table
WHERE date-col >= DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0)
AND date-col < DATEADD(d, DATEDIFF(d, 0, GETDATE()) + 1, 0);
Hiiiiiii
How can i get previous date of current year or how can we get current session in that format yyyy-yy plz help
@Amit
To get yesterday: DATEADD(d, -1 GETDATE())
For available date formats, please see the help fil on CONVERT()
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008/ms187928(v=sql.100)
hello sir i am working on SQL that is on ORACLE server ….i want to insert current date and time in one of my tables..what should be its format…i tried the following..but htis is only giving me current system date..
insert into appliance_reading values(to_date(sysdate,’DD-MON-YYYY:HH12:MI:SSAM’));
please correct the following……and ur info is really valuable…thank u sir!
@Salma
Change SYSDATE to a date time string in the picture format listed. For example:
insert into appliance_reading values(to_date(’16-APR-2010:07:55:00AM’,’DD-MON-YYYY:HH12:MI:SSAM’));
It should be
insert into appliance_reading values(to_date(sysdate,'DD-MON-YYYY:HH12:MI:SSAM'));
Also note that this site is for MS SQL Server
For oracle questions, post at http://www.orafaq.com
My previous query should be
insert into appliance_reading values(to_date(sysdate,’DD-MON-YYYY:HH24:MI:SSAM’));
Hi ! i am new to this but i have a doubt regarding sql dates.
i am getting an old date from database and i have to change only the date to current date. i am doing this in java but facing problems with java.sql.date and java.sql.date.
Please help me!!
Can you give us more informations?
If you want to change it to current date, use getdate() function
I am executing query in sql server. Is it possible to bypass optimization module??? im using sql server 2005..
Is there any service to put off optimizer in sql server?? can it be done??
Dear All,
Hi i need a little help from u guys… we have an application where client wants to stop there users acess the application after 7.30 pm just they need to open the appication but they should not able to login the application untill admin gives permission……this is where i got struck kindly help me out guys
You chan check the condition based on current date
if getdate()>=dateadd(day,datediff(day,0,getdate()),'7:30')
Thanks,
You helped me a lot
Also refer this to know how Datetime column works in SQL Server
very good answer. it is very helpful for me.
Hi Everyone,
This is part of select statement query in Access and I am trying to replicate in SQL 2008 server and i am really struggling, Please can someone help
Here is the query.
Select fname,lname,
DateSerial(2010,[Month],[Day])-[S Date] AS [Days Late],
IIf((DateSerial(2010,[Month],[Day])-[S Date]<=14),"1","0") AS [0 – 2 Week Marker],
IIf((DateSerial(2010,[Month],[Day])-[S Date]14),”1″,”0″) AS [2 – 4 Week Marker],
IIf((DateSerial(2010,[Month],[Day])-[S Date]28),”1″,”0″) AS [4 – 6 Week Marker],
IIf((DateSerial(2010,[Month],[Day])-[S Date]42),”1″,”0″) AS [6 – 13 Week Marker],
IIf((DateSerial(2010,[Month],[Day])-[S Date]>91),”1″,”0″) AS [13 Week Marker]
from Tickets
Where
((DateSerial(2010,[Month],[Day])-[S Date])>14))
ORDER BY DateSerial(2010,[Month],[Day])-[S Date] DESC , IIf((DateSerial(2010,[Month],[Day])-[S Date]<=14),"1","0");
Hi Sohail,
Sorry I haven’t the time to look at the above at the moment, but to point you in the (hopefully) right direction, you are going to want to use a CASE statement and the CONVERT function.
Gareth.