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

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

Click to Download Scripts

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

About these ads

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

      • 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…

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

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

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

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

  5. 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??

  6. Pingback: SQL SERVER – Display Datetime in Specific Format – SQL in Sixty Seconds #033 – Video « SQL Server Journey with SQL Authority

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

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

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

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

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