SQL SERVER – Get Time in Hour:Minute Format from a Datetime – Get Date Part Only from Datetime

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)

About these ads

149 thoughts on “SQL SERVER – Get Time in Hour:Minute Format from a Datetime – Get Date Part Only from Datetime

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

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

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

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

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

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

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

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

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

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

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

  11. @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
    */

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

  13. @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
    */

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

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

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

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

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

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

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

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

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

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

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

  25. SELECT
    CONVERT(VARCHAR(8),GETDATE(),108) AS HourMinuteSecond

    Is the following code returning the time of the server in military format?

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

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

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

    • Dim FTime = DateTime.Parse(FromTime)
      Dim TTime = DateTime.Parse(ToTime)
      Dim TimeDiffetent = TTime – FTime
      txtDifferent = TimeDifferent

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

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

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

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

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

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

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

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

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

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

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

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

  39. select convert(varchar,DATEPART(hh,getdate())-12) + ‘ : ‘ + convert(varchar,DATEPART(N,getdate())) +’ ‘+ RIGHT(CONVERT(VARCHAR,GETDATE(),109),2)

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

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

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

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

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

  45. Pingback: SQL SERVER – Lots of Date Functions – Find Right One to Use – Quiz – Puzzle – 27 of 31 « SQL Server Journey with SQL Authority

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

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

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

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

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

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

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

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

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

  55. 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’);

  56. 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? :).

  57. how i can convert time into datatime which returns today’s date and time from dB.

    let’s say i have time only in MY DB. 12:30 PM, when i use CONVERT(DATETIME, a.StartTime), it always return 1900-01-01 12:30:00.000. is there any way i can get today’s date.

    like 2013-07-11 12:30:00:000

  58. Pingback: SQL SERVER – Weekly Series – Memory Lane – #041 | 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