SQL SERVER – Get Date and Time From Current DateTime – SQL in Sixty Seconds #025 – Video

SQL SERVER - Get Date and Time From Current DateTime - SQL in Sixty Seconds #025 - Video sqlinsixtyseconds

This is 25th video of series SQL in Sixty Seconds we started a few months ago. Even though this is 25th video it seems like we have just started this few days ago. The best part of this SQL in Sixty Seconds is that one can learn something new in less than sixty seconds. There are many concepts which are not new for many but just we all have 60 seconds to refresh our memories. In this video I have touched a very simple question which I receive very frequently on this blog.

Q1) How to get current date time?
Q2) How to get Only Date from datetime?
Q3) How to get Only Time from datetime?

I have created a sixty second video on this subject and hopefully this will help many beginners in the SQL Server field. This sixty second video describes the same. Here is a similar script which I have used in the video.

SELECT GETDATE()
GO
-- 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;
GO

Related Tips in SQL in Sixty Seconds:

I encourage you to submit your ideas for SQL in Sixty Seconds. We will try to accommodate as many as we can.

If we like your idea we promise to share with you educational material.

Image Credit: Movie Gone in 60 Seconds

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , ,
Previous Post
SQL SERVER – Why Do We Need Master Data Management – Importance and Significance of Master Data Management (MDM)
Next Post
SQL SERVER – Core Concepts – Elasticity, Scalability and ACID Properties – Exploring NuoDB an Elastically Scalable Database System

Related Posts

25 Comments. Leave new

  • It should be VARCHAR(10) in place of VARCHAR(8) in DateOnly for SQL 2000/2005

    Reply
    • Correct.

      I missed that in video as well.

      Reply
      • Sir i have created a Campaign and sent around 5000 email,,whenever a person reads the mail i get the details in my database containing read date and time..Now i want to calculate Average Read Time: x Days y Hours z Minutes…I have used the query select ViewedOn from Campaign_Email_View where CampaignID=18 and got 4198 data…

  • Good One. I’ve tried it on SQL Server 2005. I should try it on other versions too.

    Reply
  • I created a function some time ago which finds the date from datetime (converting a date to a number and then taking the integer) and then can return all sorts of dates based on that. Might be usefule for some:

    /*===========================================================================================
    * Function to return dates from a given date
    * Time is stripped off the given date
    * Second parameter determines which date will be returned as follows:
    * LDLM = Last Day of Last Month
    * FDTM = First Day of This Month
    * FDNM = First Day of Next Month
    * LDTM = Last Day of This Month
    * LDNM = Last day of Next Month
    * FDLM = First day of Last Month
    *
    * The default if a second parameter value is not supplied is Last Day of This Month
    * BUT if the value is not supplied the keyword “DEFAULT” must be
    * e.g. dbo.DaysOfTheMonth (‘2010-04-14’,DEFAULT)
    * Might as well supply the real value (LDTM)!
    * (included for demo purposes)
    ===========================================================================================*/

    ALTER FUNCTION
    [dbo].[DaysOfTheMonth]
    (
    @Date Datetime,
    @Day CHAR(4) = ‘LDTM’
    )
    RETURNS DATETIME
    AS
    BEGIN
    DECLARE
    @LastDayOfLastMonth DATETIME,
    @FirstDayOfLastMonth DATETIME,
    @FirstDayOfThisMonth DATETIME,
    @FirstDayOfNextMonth DATETIME,
    @LastDayOfThisMonth DATETIME,
    @LastDayOfNextMonth DATETIME,
    @Result DATETIME
    SELECT
    /*1.*/ @Date = CONVERT(INT,CONVERT(DECIMAL(18,2),@InputDate)),
    /*2.*/ @LastDayOfLastMonth = @Date – DAY(@Date),
    @FirstDayOfLastMonth = @LastDayOfLastMonth – DAY(@LastDayOfLastMonth) + 1,
    /*3.*/ @FirstDayOfThisMonth = @LastDayOfLastMonth + 1,
    /*4.*/ @FirstDayOfNextMonth = DATEADD(m,1,@FirstDayOfThisMonth),
    /*5.*/ @LastDayOfThisMonth = @FirstDayOfNextMonth – 1,
    /*6.*/ @LastDayOfNextMonth = DATEADD(m,1,@FirstDayOfNextMonth)-1

    /*
    1. First, strip off the time
    2. Last day of last month is the given date minus the days
    3. First day of this month is the last day of last month plus one day
    4. First day of next month is the first day of this month plus one month
    5. Last day of this month is the first day of next month minus one day
    6. Last day of next month is the first day of next month plus one month minus one day
    (This can’t be last day of this month plus one month i.e. 28th Feb + 1 month = 28th Mar)
    */

    /*Now set the result according to the second parameter supplied*/
    SET @Result =
    Case
    WHEN @Day = ‘LDLM’ THEN @LastDayOfLastMonth
    WHEN @Day = ‘FDTM’ THEN @FirstDayOfThisMonth
    WHEN @Day = ‘FDNM’ THEN @FirstDayOfNextMonth
    WHEN @Day = ‘LDTM’ THEN @LastDayOfThisMonth
    WHEN @Day = ‘LDNM’ THEN @LastDayOfNextMonth
    WHEN @Day = ‘FDLM’ THEN @FirstDayOfLastMonth
    ELSE
    NULL
    END
    /*and return the result*/
    RETURN @Result
    END

    Reply
  • I’ve always used

    DATEADD(day, DATEDIFF(day, 0, [datehere]),0)

    in SQL Server 2005 because I once heard it’s faster than the Convert method, which presumably converts the date to and from a varchar. Thoughts?

    Reply
  • I tried it in sqlserver 2008 R2 but it’s not working in this version….

    it’s giving below error
    Msg 243, Level 16, State 1, Line 1
    Type TIME is not a defined system type.
    Msg 243, Level 16, State 1, Line 2
    Type DATE is not a defined system type.

    Reply
  • Dear Venkat

    I tried the below queries in SQLServer 2008 R2 and it is working fine..

    select getdate()
    select cast(getdate() as date)
    select cast(getdate() as time)

    select convert(date,getdate())
    select convert(time,getdate())

    select CONVERT(varchar(10),getdate(),101)
    select CONVERT(varchar(10),getdate(),108)

    May be this helps..

    Let me know if you have any difficulties.

    Reply
  • Hi,

    If we are using the getdate() in a script which is used in an application. Will it take the server date or will it take the user’s System date??

    Reply
  • i want to display one date column of all the values expect to date below dates

    Reply
  • Hi

    SQL Server Management Studio Express

    I need to write a stored procedure, which has the same result as in Crystal Reports XI.

    In Crystal Reports is like that:

    @record selection
    …..
    and ({@Year}={?Year} or {@year}=({?Year}-1))
    and date({@dep arr})<date(year(currentdate),month(currentdate),1)

    where the formulas are:
    @dep arr
    if {M.MType} ="Import" then {M.ArrivalDate}
    else if {M.MType} ="Export" then {M.DepartureDate}

    @Year
    if {M.MType} ="Import" then {%yeararr}
    else if {M.MType} ="Export" then {%yeardep}

    %yeararr
    year("M"."ArrivalDate")

    %yeardep
    year("M"."DepartureDate")

    Please help

    Reply
  • I want that system take current date dynamically & displays the current months all days dynamically. Is it possible then tell me

    Reply
  • Thanks for your valuable post.
    It helped me a lot.

    Reply
  • Thanks a lot for your post, it was easy to apply and very helpful.

    Reply
  • can u please help me how to get a date without month & year from database in oracle?

    Reply
  • hai sir i want minus 3 hr back and current time table data display in sql

    Reply
  • Can you get date from datetimepicker on winform, I created a S.P where I want SQL server to get getdate() , not current, but the value which is showing on my Winform datetimepicker ?

    Reply
  • Thank you for this article, very usefull for me

    Reply
  • Hi Pinal
    Understand this will be using your current server time. If your server time set to 1pm, GETDATE() it will be 1pm.
    is there any method can retrieve real online date and time beside using Server time?

    Reply
  • Isaa Kalii (@IsaaMoFyah)
    October 1, 2014 5:21 pm

    Hi, how do I get the current month’s records in a view in SQL Server 2008 R2?

    Reply
  • Dear Mr. Pinal, kindly need your help (I’m sorry for my bad English),
    I’m already get result time using SQL Script : CONVERT(VARCHAR(8), DateJob, 108) as ‘Time’, for example Time1 and Time2, but the datatype result of above SQL Script is in varchar.
    My Question is I want to get a time from DateJob and then substract the time result (subtract between two times : Time2-Time1), how the correct sql script?
    Thanks for your help

    Reply
  • I have float value 1.09796836411745 and need output in time 02:21:0463 …

    how to get output using sql select ..i.e. select floattotime from table..

    Reply
    • What is the correlation between two values? Explicit conversion from data type numeric to time is not allowed.

      Reply

Leave a Reply

Menu