How to get current system date time in SQL Server?
First thing which comes to many users is using following script.
SELECT GETDATE() AS CurrentDateTime
Above method is not the only method to retrieve the current system date time for SQL Server. SQL Server 2008 has many different function which provides current system date time in different format and little difference in details.
SELECT 'SYSDATETIME' AS FunctionName, SYSDATETIME() AS DateTimeFormat UNION ALL SELECT 'SYSDATETIMEOFFSET', SYSDATETIMEOFFSET() UNION ALL SELECT 'SYSUTCDATETIME', SYSUTCDATETIME() UNION ALL SELECT 'CURRENT_TIMESTAMP', CURRENT_TIMESTAMP UNION ALL SELECT 'GETDATE', GETDATE() UNION ALL SELECT 'GETUTCDATE', GETUTCDATE()
In SQL Server 2008, use any of the above function depending on your need.
Quick Video on the same subject
[youtube=http://www.youtube.com/watch?v=BL5GO-jH3HA]Reference : Pinal Dave (https://blog.sqlauthority.com)
39 Comments. Leave new
Hi
Can we get the System Date format. For ex. Suppose any body having the system date format is MM/DD/YYYY. Now we need get this Format MM/DD/YYYY. If DD/MM/YYYY means, we need to get the Format DD/MM/YYYY. is it possible?
Regards
Sachin
Date formation is the job of the front end application
You shoudl use convert function in sql, only if you dont output the results in front end application
Hej, thanks for the useful post. Sachin, it´s quite simple to change the format of the date. When you implement the script above there you should see the string where to change it.
@sachin,
We have something called styles in convert function, you can use them to get the desired output for datetime column,
this will be written like this,
select (convert (varchar(10), column_name , style_no)
These are the styles that are available in Sql Server,
Style ID Style Type
0 or 100 (*) mon dd yyyy hh:miAM (or PM)
101 mm/dd/yyyy
102 yy.mm.dd
103 dd/mm/yy
104 dd.mm.yy
105 dd-mm-yy
106 dd mon yy
107 Mon dd, yy
108 hh:mm:ss
9 or 109 (*) mon dd yyyy hh:mi:ss:mmmAM (or PM)
110 mm-dd-yy
111 yy/mm/dd
112 yymmdd
13 or 113 (*) dd mon yyyy hh:mm:ss:mmm(24h)
114 hh:mi:ss:mmm(24h)
20 or 120 (*) yyyy-mm-dd hh:mi:ss(24h)
21 or 121 (*) yyyy-mm-dd hh:mi:ss.mmm(24h)
126(***) yyyy-mm-ddThh:mm:ss.mmm(no spaces)
130* dd mon yyyy hh:mi:ss:mmmAM
131* dd/mm/yy hh:mi:ss:mmmAM
to know more about this, read “convert” in books online.
Your example :
select convert (varchar(10), getdate(), 101)
result : 08/01/2008
Hope this helps,
Thanks,
nice article
Sachin,
We have a function which will returns the date & time in required format.
CREATE FUNCTION [dbo].[ufsFormat]
(
@Date datetime,
@fORMAT VARCHAR(80)
)
RETURNS NVARCHAR(80)
AS
BEGIN
DECLARE @Dateformat INT
DECLARE @ReturnedDate VARCHAR(80)
DECLARE @TwelveHourClock INT
DECLARE @Before INT
DECLARE @pos INT
DECLARE @Escape INT
SELECT @ReturnedDate=’error! unrecognised format ‘+@format
SELECT @DateFormat=CASE @format
WHEN ‘mmm dd yyyy hh:mm AM/PM’ THEN 100
WHEN ‘mm/dd/yy’ THEN 1
WHEN ‘mm/dd/yyyy’ THEN 101
WHEN ‘yy.mm.dd’ THEN 2
WHEN ‘dd/mm/yy’ THEN 3
WHEN ‘dd.mm.yy’ THEN 4
WHEN ‘dd-mm-yy’ THEN 5
WHEN ‘dd Mmm yy’ THEN 6
WHEN ‘Mmm dd, yy’ THEN 7
WHEN ‘hh:mm:ss’ THEN 8
WHEN ‘yyyy.mm.dd’ THEN 102
WHEN ‘dd/mm/yyyy’ THEN 103
WHEN ‘dd.mm.yyyy’ THEN 104
WHEN ‘dd-mm-yyyy’ THEN 105
WHEN ‘dd Mmm yyyy’ THEN 106
WHEN ‘Mmm dd, yyyy’ THEN 107
WHEN ‘Mmm dd yyyy hh:mm:ss:ms AM/PM’ THEN 9
WHEN ‘Mmm dd yyyy hh:mi:ss:mmm AM/PM’ THEN 9
WHEN ‘Mmm dd yy hh:mm:ss:ms AM/PM’ THEN 109
WHEN ‘mm-dd-yy’ THEN 10
WHEN ‘mm-dd-yyyy’ THEN 110
WHEN ‘yy/mm/dd’ THEN 11
WHEN ‘yyyy/mm/dd’ THEN 111
WHEN ‘yymmdd’ THEN 12
WHEN ‘yyyymmdd’ THEN 112
WHEN ‘dd Mmm yyyy hh:mm:ss:Ms’ THEN 113
WHEN ‘hh:mm:ss:Ms’ THEN 14
WHEN ‘yyyy-mm-dd hh:mm:ss’ THEN 120
WHEN ‘yyyy-mm-dd hh:mm:ss.Ms’ THEN 121
WHEN ‘yyyy-mm-ddThh:mm:ss.Ms’ THEN 126
WHEN ‘dd Mmm yyyy hh:mm:ss:ms AM/PM’ THEN 130
WHEN ‘dd/mm/yy hh:mm:ss:ms AM/PM’ THEN 131
WHEN ‘RFC822’ THEN -2
WHEN ‘dd Mmm yyyy hh:mm’ THEN -4
ELSE -1 END
SELECT @ReturnedDate=’error! unrecognised format ‘ +@format+CONVERT(VARCHAR(10),@DateFormat)
IF @DateFormat>=0
SELECT @ReturnedDate=CONVERT(VARCHAR(80),@Date,@DateFormat)
–check for favourite and custom formats that can be done quickly
ELSE IF @DateFormat=-2–then it is RFC822 format
SELECT @ReturnedDate=LEFT(DATENAME(dw, @Date),3) + ‘, ‘ + STUFF(CONVERT(NVARCHAR,@Date,113),21,4,’ GMT’)
ELSE IF @DateFormat=-4–then it is european day format with minutes
SELECT @ReturnedDate=CONVERT(CHAR(17),@Date,113)
ELSE
BEGIN
SELECT @Before=LEN(@format)
SELECT @Format=REPLACE(REPLACE(REPLACE( @Format,’AM/PM’,’#’),’AM’,’#’),’PM’,’#’)
SELECT @TwelveHourClock=CASE WHEN @Before >LEN(@format) THEN 109 ELSE 113 END, @ReturnedDate=”
WHILE (1=1)–forever
BEGIN
SELECT @pos=PATINDEX(‘%[yqmidwhs:#]%’,@format+’ ‘)
IF @pos=0–no more date format strings
BEGIN
SELECT @ReturnedDate=@ReturnedDate+@format
BREAK
END
IF @pos>1–some stuff to pass through first
BEGIN
SELECT @escape=CHARINDEX (‘\’,@Format+’\’) –is it a literal character that is escaped?
IF @escape<@pos BEGIN
SET @ReturnedDate=@ReturnedDate+SUBSTRING(@Format,1,@escape-1) +SUBSTRING(@format,@escape+1,1)
SET @format=RTRIM(SUBSTRING(@Format,@Escape+2,80))
CONTINUE
END
SET @ReturnedDate=@ReturnedDate+SUBSTRING(@Format,1,@pos-1)
SET @format=RTRIM(SUBSTRING(@Format,@pos,80))
END
SELECT @pos=PATINDEX(‘%[^yqmidwhs:#]%’,@format+’ ‘)–get the end
SELECT @ReturnedDate=@ReturnedDate+–‘(‘+substring(@Format,1,@pos-1)+’)’+
CASE SUBSTRING(@Format,1,@pos-1)
–Mmmths as 1–12
WHEN ‘M’ THEN CONVERT(VARCHAR(2),DATEPART(MONTH,@Date))
–Mmmths as 01–12
WHEN ‘Mm’ THEN CONVERT(CHAR(2),@Date,101)
–Mmmths as Jan–Dec
WHEN ‘Mmm’ THEN CONVERT(CHAR(3),DATENAME(MONTH,@Date))
–Mmmths as January–December
WHEN ‘Mmmm’ THEN DATENAME(MONTH,@Date)
–Mmmths as the first letter of the Mmmth
WHEN ‘Mmmmm’ THEN CONVERT(CHAR(1),DATENAME(MONTH,@Date))
–Days as 1–31
WHEN ‘D’ THEN CONVERT(VARCHAR(2),DATEPART(DAY,@Date))
–Days as 01–31
WHEN ‘Dd’ THEN CONVERT(CHAR(2),@date,103)
–Days as Sun–Sat
WHEN ‘Ddd’ THEN CONVERT(CHAR(3),DATENAME(weekday,@Date))
–Days as Sunday–Saturday
WHEN ‘Dddd’ THEN DATENAME(weekday,@Date)
–Years as 00–99
WHEN ‘Yy’ THEN CONVERT(CHAR(2),@Date,12)
–Years as 1900–9999
WHEN ‘Yyyy’ THEN DATENAME(YEAR,@Date)
WHEN ‘hh:mm:ss’ THEN SUBSTRING(CONVERT(CHAR(30),@date,@TwelveHourClock),13,8)
WHEN ‘hh:mm:ss:ms’ THEN SUBSTRING(CONVERT(CHAR(30),@date,@TwelveHourClock),13,12)
WHEN ‘h:mm:ss’ THEN SUBSTRING(CONVERT(CHAR(30),@date,@TwelveHourClock),13,8)
–the SQL Server BOL syntax, for compatibility
WHEN ‘hh:mi:ss:mmm’ THEN SUBSTRING(CONVERT(CHAR(30),@date,@TwelveHourClock),13,12)
WHEN ‘h:mm:ss:ms’ THEN SUBSTRING(CONVERT(CHAR(30),@date,@TwelveHourClock),13,12)
WHEN ‘H:m:s’ THEN SUBSTRING(REPLACE(‘:’+SUBSTRING(CONVERT(CHAR(30), @Date,@TwelveHourClock),13,8),’:0′,’:’),2,30)
WHEN ‘H:m:s:ms’ THEN SUBSTRING(REPLACE(‘:’+SUBSTRING(CONVERT(CHAR(30), @Date,@TwelveHourClock),13,12),’:0′,’:’),2,30)
–Hours as 00–23
WHEN ‘hh’ THEN REPLACE(SUBSTRING(CONVERT(CHAR(30), @Date,@TwelveHourClock),13,2),’ ‘,’0’)
–Hours as 0–23
WHEN ‘h’ THEN LTRIM(SUBSTRING(CONVERT(CHAR(30), @Date,@TwelveHourClock),13,2))
–Minutes as 00–59
WHEN ‘Mi’ THEN DATENAME(minute,@date)
WHEN ‘mm’ THEN DATENAME(minute,@date)
WHEN ‘m’ THEN CONVERT(VARCHAR(2),DATEPART(minute,@date))
–Seconds as 0–59
WHEN ‘ss’ THEN DATENAME(second,@date)
–Seconds as 0–59
WHEN ‘S’ THEN CONVERT(VARCHAR(2),DATEPART(second,@date))
–AM/PM
WHEN ‘ms’ THEN DATENAME(millisecond,@date)
WHEN ‘mmm’ THEN DATENAME(millisecond,@date)
WHEN ‘dy’ THEN DATENAME(dy,@date)
WHEN ‘qq’ THEN DATENAME(qq,@date)
WHEN ‘ww’ THEN DATENAME(ww,@date)
WHEN ‘#’ THEN REVERSE(SUBSTRING(REVERSE(CONVERT(CHAR(26), @date,109)),1,2))
ELSE
SUBSTRING(@Format,1,@pos-1)
END
SET @format=RTRIM(SUBSTRING(@Format,@pos,80))
END END
RETURN @ReturnedDate
Hope the above function will help you.
can i get the function in sql server-2000 which gives only the current date excluding time. or any other method to format available functions
select dateadd(day,datediff(day,0,getdate()),0)
Hi Pinal,
Good info. But i think u have missed :
select {fn NOW()}
Hi,
I need the time in 12hrs am/pm format
For eg – 5:31 PM
Can anyone help me out?
Where do you want to show data?
If you use front end application do formation there
Hey its very Useful
hello sir….
i posted a statement in a particular time..how to differentiate the current time and my posted time..in sql…
declare @d datetime
set @d=getdate()
your_statement
select datediff(seconds,@d,getdate())
I have a column Date_flow with data type as datetime, and i want to select the hours as two hours, five hours, yesterday, two days ago from current datetime.
I tried with sub string but as it is not a character it wont work.
Can anyone help me.
ex
select datepart(hour,Date_flow ) from your_table
Hi
I need date on format of yymm. then what is syntax using convert function.
please help
Formation shoule be done in front end application
Otherwise use
select right(convert(char(6),getdate(),112),4)
select convert(char(4),getdate(), 12)
What if I need the current “day” in a dynamic view … see below … what would i use?
CREATE VIEW ViewName_VW as
SELECT table.field1
, table.field2
, table.field3
, table.field4
, table.field5
, table.CreateDate
FROM table
WHERE table.CreateDate Like ‘Oct 5 2010%’
(but CreateDate value will change daily …)
Should I not create such a view?
Or should I create the view in a different way?
Thanks!
Where clause should be
WHERE table.CreateDate >=dateadd(day,datediff(day,0,getdate()),0) and table.CreateDate<dateadd(day,datediff(day,0,getdate())+1,0)
How can I select fields that are created in the past 12hrs? I have a column with creation date in the table.
Hello sir i m using get Method to get current datetime in sql server 2008 but when i upload my application on server and server r in UK then in morning, here r some date difference in india and uk so my system fetch date of server means 2nd march while in india that time 3rd march.
so it is possible that my upload sqlserver fetch the client system date time means india(DataTime)…
Please Help me sir……….
Hi Madhivanan,
select dateadd(day,datediff(day,0,getdate()),0)
gives strange result for 2011-03-29 23:59:00.000
returns 2011-03-10 00:00:00.000
Any one else noticed that one !!
Can you post the actual code you have used?
sorry… my mistake – it is give the correct result!!
with TabDates as
(
select dt MeasTime, id stationid, WMOIndex from
(
select p_from_date + level – 1 dt from
(
select to_date(‘1980-11-01 00:00:00′,’yyyy-mm-dd hh24:mi:ss’) as p_from_date,
to_date( ‘1980-11-03 00:00:00′,’yyyy-mm-dd hh24:mi:ss’) as p_to_date
from dual
)
connect by level = to_date( ‘1980-11-01 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)- 7/24 and MeasTime = to_date( ‘1980-11-01 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)- 7/24 and MeasTime = to_date( ‘1980-11-01 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)- 7/24 and MeasTime = to_date( ‘1980-11-01 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)- 7/24 and MeasTime < to_date( '1980-11-03 00:00:00', 'YYYY-MM-DD HH24:MI:SS') – 7/24 group by trunc(meastime+7/24, 'dd'), stationid) Tab_TempWet
where
TabDates.stationid = Tab_PressStation.stationid(+)
and TabDates.meastime = Tab_PressStation.meastime(+)
and TabDates.stationid = Tab_PressQFF.stationid(+)
and TabDates.meastime = Tab_PressQFF.meastime(+)
and TabDates.stationid = Tab_TempDry.stationid(+)
and TabDates.meastime = Tab_TempDry.meastime(+)
and TabDates.stationid = Tab_TempWet.stationid(+)
and TabDates.meastime = Tab_TempWet.meastime(+)
order by TabDates.meastime, TabDates.WMOIndex
( This is my sql script. Now can u help me how to get the output in hourly like this "00.00 until 23.00". It's my script is an error???)
How to make the current date as the column name.
please Help.
Why do you want to do this?
DECLARE @sqlquery NVARCHAR(max);
DECLARE @colname NVARCHAR(max);
SET @colname = CONVERT(NVARCHAR(max),GETDATE(),0);
SET @sqlquery =N’select top 10 ORDINAL_POSITION as @col from master.INFORMATION_SCHEMA.COLUMNS ‘;
SET @sqlquery = REPLACE(@sqlquery, N’@col’, ”” +@colname+ ””);
EXEC(@sqlquery);