I have seen scores of expert developers getting perplexed with SQL Server in finding time only from datetime datatype. Let us have a quick glance look at the solution.
SQL Server 2000/2005
SELECT
CONVERT(VARCHAR(8),GETDATE(),108) AS HourMinuteSecond,
CONVERT(VARCHAR(8),GETDATE(),101) AS DateOnly
GO
SQL Server 2008
SELECT
CONVERT(TIME,GETDATE()) AS HourMinuteSecond,
CONVERT(DATE,GETDATE(),101) AS DateOnly
GO
I hope the above solution is clear to you all.
Quick Video on the same subject
Here is the video discussing the same concepts.
Reference : Pinal Dave (http://blog.SQLAuthority.com)
This one is nice and SQL Server 2008 data types Time and Date is also useful
And if you dont want those ‘nanoseconds’ in SQL Server 2008, you can specify a precision along with the TIME data type.
SELECT
CONVERT(TIME(0),GETDATE()) AS HourMinuteSecond,
CONVERT(DATE,GETDATE(),101) AS DateOnly
GO
/*
HourMinuteSecond DateOnly
—————- ———-
13:27:26 2009-08-06
*/
If i want only the hours and minutes and not the seconds how can i use
SELECT
LEFT(CONVERT(TIME(0),GETDATE()) ,5) AS [HourMinuteSecond]
GO
/*
HourMinuteSecond
————————
10:48
*/
and as an added bonus that converting a date/time value to VarChar does not offer, you can still add this result with time…(above +10 mins below)
SELECT
LEFT(DATEADD(mi, 10, (CONVERT(TIME(0),GETDATE()))),5) AS [HourMinuteSecond]
GO
/*
HourMinuteSecond
————————
10:58
*/
Daniel S. Gurrola II
No Acronyms, Affiliations, Certifications or other Nausea – it’s just…me.
Well
Is it good practice to compare date(s) using these operators > , < , <= , … ?
or any other and smart way to do it …
I am using in this way
…
WHERE CONVERT(nvarchar(8), dbo.deposit_m.do_deposition, 112) BETWEEN @fdate AND @tdate
…
please give some suggestions
Thanks again
Use
WHERE
dbo.deposit_m.do_deposition >=@fdate AND
dbo.deposit_m.do_deposition <=@tdate
its better to use datediff function for date comparison as
dbo.deposit_m.do_deposition <=@tdate
will fail as @tdate time portion will have 12:00 AM so the query will not bring info for the last day of the condition if deposit_m.do_deposition has time portion.
If the date column has index, usage of datediff function will deny its usage
This is very good datatype of sql server 08 .
I was trying to separate data & time finally MS done it.
Thanks for this great demo.
It’s always good to review the basics.
Thanx Pinal.
CONVERT(VARCHAR(10),GETDATE(),101) AS DateOnly will return: 08/06/2009
Yes as a varchar and not as a Date
But for SQL 2005 the date in year shows only first two digits not the complete year and i have a doubt in the script
CONVERT(VARCHAR(8),GETDATE(),108) AS HourMinuteSecond,
CONVERT(VARCHAR(8),GETDATE(),101) AS DateOnly
what about the parameters 108 and 101 what they actually do?
Read about CONVERT function in SQL Server help file
It has examples with explanation
Thanks for nice post again.
What’s difference in 2008 and 2005/2000 at this point of view?
Because I ran the same script in both 2005 and 2008. Gave me same result.
First method converts the dates to VARCHARs whereas the second query is specific to version 2008 that converts to DATE and TIME seperately
Nice tips for sql2008,
but i think sir,
Varchar conversion format is much useful then time conversion in sql2008.
@Kandregula
Please see the help file for CONVERT. There is a table explaining all the date format arguments.
K.K.B,
It is not a good idea to use
“WHERE CONVERT(nvarchar(8), dbo.deposit_m.do_deposition, 112) BETWEEN @fdate AND @tdate”.
SQL Query optimizer will not be able to create the optimum plan for this query because of the CONVERT() function applied around the column “deposit_m.do_deposition”. Any index that exists on the column will not be used.
So the alternate is
WHERE
dbo.deposit_m.do_deposition >=@fdate AND
dbo.deposit_m.do_deposition <=@tdate
Hey, I am a starter.
CONVERT(VARCHAR(8),GETDATE(),101) AS DateOnly
It showed 07/14/20
I think to see date correctly, it shud be
CONVERT(VARCHAR(10),GETDATE(),101) AS DateOnly
And I tried
SELECT
CONVERT(TIME,GETDATE()) AS HourMinuteSecond,
CONVERT(DATE,GETDATE(),101) AS DateOnly
GO
on SQL Server 2008, Express edition. It compiled successfully but on executing it gave me error:
Msg 243, Level 16, State 1, Line 1
Type TIME is not a defined system type.
Msg 243, Level 16, State 1, Line 1
Type DATE is not a defined system type.
Any feedback is appreciated.
Thanks.
Geetika,
It looks like the server instance on which you are executing the query is not SQL Server 2008. To verify this, try executing “SELECT @@VERSION” and the results will tell you whether it is SQL SErver 2008 or not.
A related tip that I use in many stored procs when dealing with dates:
When I receive a date as an input argument and I want to be sure that date does not have a time associated with it, I just re-assign the date variable using CONVERT, as follows:
(Assuming @Date is the name of the input argument)
SET @Date = CONVERT(varchar(20), @Date, 101)
That one line strips off any time component (actually leaving it set to 00:00:00) while retaining the date component.
Converting to varchar is not a good practise.Refer this post to know you can effectively remove time part. See the last examples
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/21/understanding-datetime-column-part-iii.aspx
**********
Hey, I am a starter.
CONVERT(VARCHAR(8),GETDATE(),101) AS DateOnly
It showed 07/14/20
**********
Change to:
CONVERT(VARCHAR(10),GETDATE(),101) AS DateOnly
Thanks Jacob, yeah the sql server instance turned out to be SQL Server 2005 Enterprise edition.
@Speednet,
A better option is to use the DATEADD() function. In your example a datetime value is converted to VARCHAR and then converted back to DATETIME. A DATETIME value is stored internally as a number. The following example demonstrates a more optimized way of removing the TIME part from a DATETIME value.
DECLARE @d DATETIME
SELECT @d = GETDATE()
SELECT @d
/*
———————–
2009-08-08 09:56:38.793
*/
– Remove the time part
SELECT @d = DATEADD(d, 0, DATEDIFF(d, 0, @d ))
SELECT @d
/*
———————–
2009-08-08 00:00:00.000
*/
hello sir , my question is to inserting the different date format in datetime datatype only not to converting to varchar,
ie i need dd-mm-yyyy format of data in datetime datatype ….pls share ur knowledge !!!!
@lohith,
DATETIME values are stored internally as numbers and hence the question of storing the data in a certain format does not exist.
The format comes only when you parse a date string or when you display a date string. The parsing can be managed by using ‘SET DATEFORMAT’ and display can be managed by using CONVERT() function with appropriate style flags.
Here is an example of SET DATEFORMAT
SET DATEFORMAT DMY
SELECT CAST(’31-12-2009′ AS DATETIME)
/*
———————–
2009-12-31 00:00:00.000
*/
SET DATEFORMAT MDY
SELECT CAST(’12-31-2009′ AS DATETIME)
/*
———————–
2009-12-31 00:00:00.000
*/
Dear Pinal,
Congratulations for your great work,
Could you please tell me how to extract hour and minute only from a datetime.
ie I dont want seconds.
Thank You
Vivek
Dear Pinal,
I got the answer.
SELECT CONVERT(VARCHAR(5),GETDATE(),108) AS HourMinuteSecond
Thank You
Regards
Vivek
I want 10.30 PM not 22:30
Hi Vasu,
You can display time in AM/PM format as:
DECLARE @dt DATETIME
SELECT @dt = ’2009-11-08 20:00′
SELECT CONVERT(VARCHAR(5),@dt,108) + ‘ ‘ + RIGHT(CONVERT(VARCHAR,@dt, 109),2)
Thanks,
Tejas
Hi,
Ignore my last comment
Please check it out: http://tejasnshah.wordpress.com/2008/12/22/sql-server-get-time-in-ampm-format/
Thanks,
Tejas
hi tejas
i need to combine two column information into one column can u plz help me
thanks
Nishar
Hi Nishar,
Can you post some sample data that you have with expected OUTPUT.
Thanks,
Tejas
select column1+column2 as Column3 from tablename
Would wish to be pointed to write an sql statement that return hourly records of a given day.
Looking for 24 records for every hours of the day?
Yes so as to return the hourly count
Try this:
DECLARE @dates TABLE (dt DateTime)
INSERT INTO @dates (dt) SELECT ’2009-10-01′
SELECT
DATEADD(hour, number, dt) AS HourPart
FROM @dates
CROSS JOIN master..spt_values s
WHERE type = ‘p’ AND number between 0 AND 23
/*
HourPart
———————–
2009-10-01 00:00:00.000
2009-10-01 01:00:00.000
2009-10-01 02:00:00.000
2009-10-01 03:00:00.000
2009-10-01 04:00:00.000
2009-10-01 05:00:00.000
2009-10-01 06:00:00.000
2009-10-01 07:00:00.000
2009-10-01 08:00:00.000
2009-10-01 09:00:00.000
2009-10-01 10:00:00.000
2009-10-01 11:00:00.000
2009-10-01 12:00:00.000
2009-10-01 13:00:00.000
2009-10-01 14:00:00.000
2009-10-01 15:00:00.000
2009-10-01 16:00:00.000
2009-10-01 17:00:00.000
2009-10-01 18:00:00.000
2009-10-01 19:00:00.000
2009-10-01 20:00:00.000
2009-10-01 21:00:00.000
2009-10-01 22:00:00.000
2009-10-01 23:00:00.000
*/
http://syntaxhelp.com/SQLServer/Breaking-a-date-by-hour-into-24-parts
Hello all – I am a newbie,
can someone tell me how do I modify this to remove the ‘AM’ from the result
SELECT CONVERT(VARCHAR(10), DATEADD(d,+1,GETDATE()),101)+ ‘ ‘ + RIGHT (CONVERT(VARCHAR, GETDATE(), 100),7)
Hi Donna,
Could you please let us know, in which format do you want the datetime?
If you just want to remove AM/PM from the above query. You can make it possible as:
SELECT CONVERT(VARCHAR(10), DATEADD(d,+1,GETDATE()),101)+ ‘ ‘ +
LEFT(RIGHT (CONVERT(VARCHAR, GETDATE(), 100),7),5)
Its better if you post in which format do you want result.
Thanks,
Tejas
SQLYoga.com
@Donna
Please look at the list of date conversions directly supported by SQL Server: http://msdn.microsoft.com/en-us/library/ms187928.aspx
SELECT CONVERT(VARCHAR, GETDATE(), 107) + ‘ ‘ + CONVERT(VARCHAR, GETDATE(), 108)
You are my hero
Note that if you format dates using sql, all your DATEs become VARCHARs which can’t be used to make use of date related functions until re-converted back to DATETIME
If you use front end application, you should do formation there
Madhivanan
In SQL server 2008, the time and date functions are separated. This article is very useful!!!
I created this function to return AM/PM time from a datetime variable. I use it in a SELECT statement to nicely return AM/PM separately from the date.
CREATE FUNCTION [dbo].[funcAMPMTimeFromDate]
(
@MyDateTimeValue as datetime
)
RETURNS char(8)
AS
BEGIN
– Declare the return variable here
DECLARE @MyTime as char(8)
SET @MyTime = case when (datepart(hh,@MyDateTimeValue) > 12)
then rtrim(cast((datepart(hh,@MyDateTimeValue)-12) as char(2))) + ‘:’ +
case when (datepart(mi,@MyDateTimeValue)<10) then '0' + cast(datepart(mi,@MyDateTimeValue) as char(1)) + ' PM'
else cast(datepart(mi,@MyDateTimeValue) as char(2)) + ' PM' END
when (datepart(hh,@MyDateTimeValue) = 0)
then '12:' +
case when (datepart(mi,@MyDateTimeValue)<10) then '0' + cast(datepart(mi,@MyDateTimeValue) as char(1)) + ' AM'
else cast(datepart(mi,@MyDateTimeValue) as char(2)) + ' AM' END
else cast(datepart(hh,@MyDateTimeValue) as char(2)) + ':' +
case when (datepart(mi,@MyDateTimeValue)<10) then '0' + cast(datepart(mi,@MyDateTimeValue) as char(1)) + ' AM'
else cast(datepart(mi,@MyDateTimeValue) as char(2)) + ' AM' END END
– Return the result of the function
RETURN @MyTime
END
You dont need to complicate that much
It is as simple as
select right(convert(char(20),getdate(),0),7)
cast(convert(int,getdate())as datetime)
This, I think would be the simplest way to display only the date and send it as a date type value.
Sorry, not to display but for computations that require the time be removed or set to “0:00″
This not only removes time part but also increases day by 1
The correct and effecient method is
dateadd(day,datediff(day,0,getdate()),0)
Using cast(convert(int,getdate())as datetime) did not add one to the date. It simply removes the fraction of the date without rounding.
Did you test your solution?
Ok
See if you get the same number for these two
select
convert(int,getdate()),
datediff(day,0,getdate())
Hi,
i have the following data from an IIS log file:
cs-username (multiple user’s)
date (only 1 day of data)
time (displayed as Hour:Minute:Second AM or PM)
I am trying to capture the first time the user access a site. My problem is that IIS captures mulitple times during the day for certain users. How can I query where the username will pull up once with the time showing only once.
Thanks,
Tiffany
Hi,
i have the following data from an IIS log file:
cs-username (multiple user’s)
date (only 1 day of data)
time (displayed as Hour:Minute:Second AM or PM)
I am trying to capture the first time the user access a site. My problem is that IIS captures mulitple times during the day for certain users. How can I query where the username will pull up once with the time showing only once.
Thanks,
Antiffia
Hi All,
I select query in SQL Server 2005
with filed round(100/Exchrate,2)
then result :18960.00000000000000000000000
but I want the result is : 18960.00
Kindly help me.
Regards,
Nam Chau
Use
cast(100/Exchrate as decimal(14,2))
great stuff.
Thanks man for the great blog it is really so informative .
But i guess the code for SQL 2000/2005 is missing the year part so
SELECT
CONVERT(VARCHAR(8),GETDATE(),108) AS HourMinuteSecond,
CONVERT(VARCHAR(8),GETDATE(),101) AS DateOnly
GO
Should be
SELECT
CONVERT(VARCHAR(8),GETDATE(),108) AS HourMinuteSecond,
CONVERT(VARCHAR(10),GETDATE(),101) AS DateOnly
GO
Regards,
Ziek
SELECT
CONVERT(VARCHAR(8),GETDATE(),108) AS HourMinuteSecond
Is the following code returning the time of the server in military format?
Yes. It is in 24 hour format
Is there any better way to display HHMM in SQL 2005?
SELECT substring(convert (nchar(5), getdate(),14),1,2) + substring(convert (nchar(5), getdate(),14),4,2)
Thanks,
Raj
Yes. Here is the shortcut
select replace(convert(varchar(5),getdate(),108),’:',”)
short cut for SQL 2008
select CAST(GETDATE() as date) as DateOnly
declared arrivaltime varchar
declared completedtime varchar
values is 10:00 and 11:00
i want to find difference between the hours
thanks in advance
plz send to mail if possible
select datediff(hour,cast(arrivaltime as datetime),cast(completedtime as datetime)) from your_table
I have a similar requirement but I need the difference of minutes also.
That is, I have startTime as varchar
endTime as varchar
Values are 10:00 AM and 7:30 PM
I need to find the difference between these two times. How to do that? I tried the above said query but that gives only the difference in hours. I need the time also. In this case I need 09:30 as result.
Please help. Thanks
Try this
declare @startTime varchar(10),@endTime varchar(10)
select @startTime =’10:00 AM’, @endtime =’7:30 PM’
select convert(varchar(10),@endtime-cast(@starttime as datetime),108)
Hi All,
how to maintainance beter SQL system, when we have a lot of transactions, how to make sure that it’s run ok when we have about 500 000 rows transactions a day ?
Pinal,
Great tip for handling only date portion, but I find the following to display date in YYYY-MM-DD format and will allowing sorting for this value
SELECT
CONVERT(VARCHAR(10),GETDATE(),120) AS DateOnly
GO
Note that if you use front end application, you should do formation there
hello all,
how can i retrieve only the hour?
select datepart(hour,getdate())
hello all,
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE dbo.[GetClassInfo]
@TimeNow int,
@DayId nvarchar(MAX)
AS
BEGIN
SELECT TeacherId,ClassName from ClassInfo where
TimeId=@TimeNow and DayId=@DayId
END
GO
timenow has a hour and so i want timeid to be hour as well how do i go about converting timeid to just an integer hour value?
Can you post some sample data with expected output?
thanks in advance
Hello Frnds,
Please Try this may be its solve your problem:-
select substring(CONVERT(varchar,getdate(),100),12,LEN(CONVERT(varchar,getdate(),100)))
Output of this statement is ” 5:32PM ”
Thank you
It is as simple as
select right(CONVERT(varchar,getdate(),100),7)
Also leave formation to front end application
Hello friends,
i have a scenario like below:
Need to extract data from a table from previous day 08:30 AM to current day 08:29 AM.
Thanks in advance.
Where
datecol>=dateadd(day,datediff(day,0,getdate()),’8:30′)-1 and
datecol<dateadd(day,datediff(day,0,getdate()),'8:30')+1
CONVERT(VARCHAR(8),GETDATE(),101) AS DateOnly
is cutting off the year part.
i think it should be:
CONVERT(VARCHAR(10),GETDATE(),101) AS DateOnly
Pinal, how would i convert a time like 4:23:23 AM into 04:23:23 AM. I mean it’s just padding zero’s if there isn’t a built in convert code. I want to use it with masked edit control of the ajax control toolkit. Stupid thing will not accept 4:23:23 AM as valid
right(’0′+time,8)
GOOD ONE.
Hello there, i got time on my query 21:10:00 how to get only 20:10 ?
many thanks
thnks buddy
Hello, I am a newbie to SQL server. How do I use the time only retrieval function to get the time from a database field e.g. I have a field called sDateTime which is a datetime field
figured it out
CONVERT(TIME(0), Fieldname)
Thanks for the direction though
select convert(varchar(12),sDatetime,108) from table
Hi Madhivanan,
i want to calculate how much time spending in office
based on logintime and logout time,
condition is :9 hours perday
i want to display spending time ,reminingtime
plz help me
Hi Pinal,
Plz help me
Post some sample data and expected result
DECLARE @Logintime VARCHAR(36)
set @Logintime=’11:35am’
DECLARE @Logouttime VARCHAR(10)
DECLARE @diff VARCHAR(36),
@hr VARCHAR(30),
@min VARCHAR(2),
@sec VARCHAR(30),
@resttime varchar(36)
,@rtime VARCHAR(30)
DECLARE @NewLineChar AS CHAR(2)
SET @NewLineChar= CHAR(13) + CHAR(10)
SET @Logouttime = (SELECT right(Getdate(), 8)AS t)
SET @sec = Abs(Datediff(SECOND, @Logintime, @Logouttime))
SET @hr = @sec / 3600
SET @min = ( @sec – ( @hr * 3600 ) ) / 60
SET @sec = ( @sec – ( @hr * 3600 ) – ( @min * 60 ) )
IF CAST(@hr AS INT) < 10
SET @hr = '0' + @hr
IF CAST(@min AS INT) < 10
SET @min = '0' + @min
IF CAST(@sec AS INT) < 10
SET @sec = '0' + @sec
SET @diff = @hr + ':' + @min + ':' + @sec
print @diff
PRINT 'YOU ARE SPENDING IN OFFICE :'+' ' + @diff + ' ' + 'HOURS'
IF CAST(@hr AS INT) < 9
print 'YOU WILL GET LOSS OF PAY'
ELSE
PRINT 'SUCCESSFULLY CONSIDERED YOUR ATTENDENCE FOR THIS DAY '
Hi madhivanan
This is my query output is
08:16:00
YOU ARE SPENDING IN OFFICE : 08:16:00 HOURS
YOU WILL GET LOSS OF PAY
Wait For Rest of Time:
I want to display Rest of Time
Hello Sir.
I want to know that how to get only Hour & minute from Datetime field.
I have to make one Window service so that i need to check Hour and minute from database.
please help me in this
SELECT DATEPART(HOUR,GETDATE()),DATEPART(MINUTE,GETDATE())
Try this
select Convert(varchar(5),getdate(),114)
select convert(varchar,DATEPART(hh,getdate())-12) + ‘ : ‘ + convert(varchar,DATEPART(N,getdate())) +’ ‘+ RIGHT(CONVERT(VARCHAR,GETDATE(),109),2)
yes
this formul select convert(varchar,DATEPART(hh,getdate())) + ‘:’ + convert(varchar,DATEPART(N,getdate())) is what i seach since 1 hour.
Thanks very much.
Do you know how add 0 before hour?
For example 1;30 -> 01:30
and the same thing with minute?
thanks
Use convert function with style 108. It will append it automatically
Thanks you but it s a wrong way.
I try
SELECT (convert(varchar,DATEPART(hh,getdate())) + ‘:’ + convert(varchar,DATEPART(N,getdate()),108)) as t
but i obtained
9:2
I need 09:02
This way is good for Hour.
SELECT
CAST(
CASE WHEN LEN(DATEPART(hh,getdate())) = 1
THEN ’0′ + CAST( DATEPART(hh,getdate()) AS varchar)
ELSE CAST( DATEPART(hh,getdate())AS varchar) END
AS varchar)
But it isn’t smart
You can easily do this
SELECT convert(varchar(5),getdate(),108) as t
It’s work.
Thanks you very much.
Hello friends
I have a case
Where do I get a month records from a table and the condition is that q is the time 22 pm to 6 am the following day,
so far I have this.
DATEPART (hh, date) = 22 and date> = ’20110901 ‘and date <'20111001'
But I have no results, can you please help with syntax,
Hello friends
I have a case
Where do I get a month records from a table and the condition is that the hours are from 22 pm to 6 am the following day,
so far I have this.
DATEPART (hh, date) = 22 and date> = ’20110901 ‘and date <'20111001'
But I have no results, can you please help with syntax,
where datecol>’20110901 22:00:00′ and date_col<'20111001 06:00:00'
I want to get the Date and the hour part.
To get the date i use select convert(varchar(10),getdate(),111) which gives me
2011/11/25 as Output.
But i want the hour part as well something like this:
2011/11/25 11 AM
Here 11 Am is the Hour part.How cn i achieve this?
Select Convert(varchar(10),getdate(),101)
union all
select GETDATE()
result :
2011-12-05 00:00:00.000
2011-12-05 07:08:38.853
Required :
2011-12-05
2011-12-05 07:08:38.853
Can you please help me to solve the problem.
It is not possible. The implicit convertion happens. Why do you want to do this?
Hi all
I have checkin date as varchar(10)
as well as checkout date as varchar(10)
and i have got all the record in the table between this dates including this date field
so what i do
You need to convert these two varchars into datetime before doing any comparison
but i got the same result
using the query like this
Where checkinDate>=’12/12/2011′ And checkoutDate,=’12/31/2012′
[...] Difference Between DATETIME and DATETIME2 Difference Between GETDATE and SYSDATETIME Get Time in Hour:Minute Format from a Datetime – Get Date Part Only from Datetime Datetime Function TODATETIMEOFFSET Example Information Related to DATETIME and DATETIME2 Datetime [...]
Hi,
This is a neat site for information. Can anybody help us with the following: I’m trying to get the time part of a DateTime then convert it to minutes.
Eg. DateTime – 2012-03-20 07:30:00.000
Minutes – 450
What would be the most efficient way of getting the minutes in sql?
Thanks in advance
Sharon
Any Idea how to display Date & Time in following format:
“02/28/2012 12:59 AM ET”
??
its: “02/28/2012 11:59 AM ET”
I am using the exact same way to display date only but it shows in our application dashboard with full DateTime stamp in data grid..
i typed in SQL server 2005,
SELECT CONVERT(VARCHAR(10),GETDATE(),101) as Date
Any idea????
Pinal,
Once again you have come through with the goods. You are the man.
Good solution…Thanx Pinal
This is a BAD solution – converting to a string and hoping to accurately lop off the time portion with a substring, then converting it back, is a HACK.
To get the response like 3:16PM
SELECT RIGHT(LTRIM(RIGHT(Convert(varchar,GETDATE(),100),8)),8) AS ONLY_TIME
I have a table with a datetime field, and I would like to find just records from today, after 10:15am.
I have :
where TIME_STAMP >= cast(floor(cast(GETDATE() as float)) as datetime)
and LEFT(CONVERT(TIME(2),TIME_STAMP) ,5) > ’10:15′
which works fine in SQL Server 10, but our prod database is SQL Server 9.
Any ideas on an efficient way to do this?
SELECT CONVERT(VARCHAR(19), GETDATE(), 1)
+ ‘ ‘ +
RIGHT(CONVERT(VARCHAR(17), GETDATE(), 9),5)
–REPLACE(RIGHT(CONVERT(VARCHAR(50), GETDATE(), 100),7),RIGHT(RIGHT(CONVERT(VARCHAR(50), GETDATE(), 100),7),2),”)
+’ ‘+
RIGHT(CONVERT(VARCHAR(19), GETDATE(), 100),2)AS ‘Date’
Result will be : 08/23/12 4:16 PM
[...] Get Time in Hour:Minute Format from a Datetime – Get Date Part Only from Datetime [...]
i want 10-OCT-2012 10:50:10 PM
[...] Get Time in Hour:Minute Format from a Datetime – Get Date Part Only from Datetime [...]
Dear All,
Can any one help me,
i have two columns of one table as @ABC as DateTime and @xyz as DateTime – Datatype
i want to subtract only time and i am trying like this
for ABC = 21/02/2012 6:10:00 PM and XYZ = 01/01/2001 12:00:00 AM -> 1st Row
CONVERT(varchar(10), dbo.checkingtime.ABC – dbo.checkingtime.XYZ, 108)
and i am getting the result as 18:10 but i want the result as 05:50 in Hours and Minutes only
is it possible ? ? ?
Awaiting from the HelpingNature People
Hi
I want to convert ’2013-01-15 11:23:49.930′
output =’2013-01-15 11:00:00.000′
Please help.
Hi ALL,
i want to convert dateformat ’2013-01-15 11:23:34.930′ into 2013-01-15 11:00:00.000 this format please help.
select dateadd(hour,datediff(hour,0,date_col),0) from your_table
sir how to find sum (datetime) for group by operation
SUM will work only form number datatypes
I wrote a small function which returns you table having Time Diffence as on of its attribute. Function is as follow:-
CREATE FUNCTION dbo.TimeDiff (@STARTDATE datetime,@ENDDATE datetime)
RETURNS @TIMEDIFF TABLE (ID int IDENTITY(1, 1) NOT NULL, TimeDiff TIME NULL) AS
BEGIN
DECLARE @24DATE DATETIME
SET @24DATE = ’23:59:59.000′
IF (@STARTDATE > @ENDDATE)
INSERT INTO @TIMEDIFF
SELECT DATEADD (SECOND,1, CONVERT(TIME(0), ( @24DATE -(@ENDDATE – @STARTDATE))))
ELSE
INSERT INTO @TIMEDIFF
SELECT CONVERT(TIME(0), ( (@ENDDATE – @STARTDATE)))
RETURN
END
GO
SELECT * FROM TimeDiff(’10:15:45′,’14:00:15′)
Result
ID TimeDiff
1 03:44:30.0000000
I wanted to be able to format dates in a similar way to the VB6 FORMAT() function where you give it a date and a picture of the date and it returns a string in that format.
I have written this as a SQL function and use it quite often
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(‘Format’) AND type in (N’FN’, N’IF’, N’TF’, N’FS’, N’FT’)) DROP FUNCTION Format
GO
Create Function Format(@Date DateTime,@Fmt Char(30)) Returns Char(30) as
Begin
– @Fmt can be dd/mm/yyyy, yyyy-mm-dd, dd-mmm-yyyy, dd/mm/yy, ddd dd/mm/yyyy, dddd dd-mmmm-yyyy, dddd mmmm dd, yyyy etc
– @Fmt can also include HH:mm, HH:mm:ss, HH:mm:ss:ttt formats – always in 24 hour format at this stage
– FD is First Day-of-month, LD is Last Day-of-month, FYYY is financial year, FM is month in financial year, MN is minutes of hour
Select @Fmt=Replace(@Fmt,’FD’,’01′);
Select @Fmt=Replace(@Fmt,’LD’,right(rtrim(cast(100+Day(DateAdd(Day,-1,DateAdd(Month,1,DateAdd(Day,1-Day(@Date),@Date)))) as Char(3))),2));
Select @Fmt=Replace(@Fmt,’FM’,Right(RTrim(Cast(100+Case When Month(@Date)>6 then Month(@Date)-6 else Month(@Date)+6 End as char(3))),2));
Select @Fmt=Replace(@Fmt,’FYYY’,Right(RTrim(Cast(10000+Case When Month(@Date)>6 then Year(@Date) else Year(@Date)-1 End as char(5))),4));
Select @Fmt=Replace(@Fmt,’FY’,Right(RTrim(Cast(10000+Case When Month(@Date)>6 then Year(@Date) else Year(@Date)-1 End as char(5))),2));
Select @Fmt=Replace(@Fmt,’HH’,left(Cast(Cast(@Date as Time) as char(10)),2));
Select @Fmt=Replace(@Fmt,’:MM’,SubString(Cast(Cast(@Date as Time) as char(12)),3,3));
Select @Fmt=Replace(@Fmt,’MN’,SubString(Cast(Cast(@Date as Time) as char(12)),4,2));
Select @Fmt=Replace(@Fmt,’SS’,SubString(Cast(Cast(@Date as Time) as char(12)),7,2));
Select @Fmt=Replace(@Fmt,’TTT’,SubString(Cast(Cast(@Date as Time) as char(12)),10,3));
Select @Fmt=Replace(@Fmt,’TT’,SubString(Cast(Cast(@Date as Time) as char(12)),10,2));
Select @Fmt=Replace(@Fmt,’DDDD’,DATENAME(dw,@Date));
Select @Fmt=Replace(@Fmt,’DDD’,Left(DATENAME(dw,@Date),3));
Select @Fmt=Replace(@Fmt,’DD’,Right(Rtrim(cast(100+day(@Date) as Char(3))),2));
Select @Fmt=Replace(@Fmt,’MMMM’,DATENAME(month,@Date));
Select @Fmt=Replace(@Fmt,’MMM’,Left(DATENAME(month,@Date),3));
Select @Fmt=Replace(@Fmt,’MM’,Right(Rtrim(cast(100+month(@Date) as Char(3))),2));
Select @Fmt=Replace(@Fmt,’YYYY’,Right(Rtrim(cast(10000+Year(@Date) as Char(5))),4));
Select @Fmt=Replace(@Fmt,’YY’,Right(Rtrim(cast(10000+Year(@Date) as Char(6))),2));
Return ltrim(rtrim(@Fmt));
End
GO
SELECT getdate(),
dbo.Format(getDate(),’ddd dd-mmm-yyyy HH:mm:ss.ttt’),
dbo.Format(getDate(),’HH:mn’),
dbo.Format(getDate(),’fd-mmm-yyyy’),
dbo.Format(getDate(),’LD-mmm-yyyy’),
dbo.Format(GetDate(),’FYYY-FM’);
I need to get the number of hours from two date formats.. i.e i need to get number of hours spent by a staff on a project i have decidded to use start time and end time.. solution? :).
03:07:30.8570000 i got this as time and what is this “8570000″ here…??