SQL SERVER – 2008 – Get Current System Date Time

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

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

42 thoughts on “SQL SERVER – 2008 – Get Current System Date Time

  1. 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

  2. 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.

  3. @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,

  4. 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.

  5. Pingback: SQL SERVER - Get Date Time in Any Format - UDF - User Defined Functions Journey to SQL Authority with Pinal Dave

  6. 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

  7. hello sir….

    i posted a statement in a particular time..how to differentiate the current time and my posted time..in sql…

  8. 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.

  9. 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!

  10. 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……….

  11. 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 !!

  12. 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???)

    • 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);

  13. i want a qurey that minus system date and my date in data base like i want to use it on searching select * from emp where systemdate-mydate>=120days
    can you plz tell me the qurey for that i am confused how to minus sysdate and mydate
    mydate is my column name kindly mail me the solution

  14. Hi,i made databse is in vusualstudio 2010 i want to get date automatically from my system without entering date manually in text box.i need to get date automatically from system and store it in databse with other enteries……………….
    please tell me how to do it???????

  15. Pingback: SQL SERVER – Get Date and Time From Current DateTime – SQL in Sixty Seconds #025 – Video « SQL Server Journey with SQL Authority

  16. hello sir this is chandru i have developed chit fund software i need to make autoupdated when purticular date changed to next date kindly help me sir

  17. Pingback: SQL SERVER – Weekly Series – Memory Lane – #040 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s