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
[youtube=https://www.youtube.com/watch?v=BL5GO-jH3HA]
Related Tips in SQL in Sixty Seconds:
- Retrieve Current Date Time in SQL Server CURRENT_TIMESTAMP, GETDATE(), {fn NOW()}
- Get Time in Hour:Minute Format from a Datetime – Get Date Part Only from Datetime
- Get Current System Date Time
- Get Date Time in Any Format – UDF – User Defined Functions
- Date and Time Functions – EOMONTH() – A Quick Introduction
- DATE and TIME in SQL Server 2008
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)
25 Comments. Leave new
It should be VARCHAR(10) in place of VARCHAR(8) in DateOnly for SQL 2000/2005
Good One. I’ve tried it on SQL Server 2005. I should try it on other versions too.
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
The last section of this post will have more information
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?
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.
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.
Thank u Dave.
Your site has saved me tons of times ^_^
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??
i want to display one date column of all the values expect to date below dates
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
I want that system take current date dynamically & displays the current months all days dynamically. Is it possible then tell me
Thanks for your valuable post.
It helped me a lot.
Thanks a lot for your post, it was easy to apply and very helpful.
can u please help me how to get a date without month & year from database in oracle?
hai sir i want minus 3 hr back and current time table data display in sql
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 ?
Thank you for this article, very usefull for me
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?
Hi, how do I get the current month’s records in a view in SQL Server 2008 R2?
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
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..
What is the correlation between two values? Explicit conversion from data type numeric to time is not allowed.