Get time only from datetime in SQL Server 2000

  • Hi guys..

    I want to convert from datetime to time part only. Like this sample :

    6/4/2009 3:50:06 PM to 3:50:06 PM

    I really appreciate for the help

    Thank,

    YuzZ

  • declare @time datetime

    select @time = getdate()

    select @time, convert(char(8),@time,108) as OnlyTime1,

    dateadd(dd, datediff(dd,@time,'19000101'),@time) as OnlyTime2

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Thx for your help Adi.

    But, if i want to create table like this :

    CREATE TABLE [WeatherInfo] (

    [WeatherID] [int] NOT NULL ,

    [DateNow] [datetime] NOT NULL ,

    [TimeNow] [datetime] NOT NULL ,

    [Until] [datetime] NOT NULL ,

    [Temperatur] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,

    [Symbol] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,

    CONSTRAINT [PK_WeatherInfo] PRIMARY KEY CLUSTERED

    (

    [WeatherID]

    ) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    then i want insert some values :

    INSERT INTO [PMCS].[dbo].[WeatherInfo]([WeatherID], [DateNow], [TimeNow], [Until], [Temperatur], [Symbol])

    VALUES(, , , , , )

    i want the result like this :

    1 - 2009-06-04 - 16:29:23 - 18:29:23 - 30C - Hot

    Thx before 🙂

  • Do you want it returned as datetime or as a string?

    _____________
    Code for TallyGenerator

  • As string... thx

  • Upss sorry.... as datetime 🙂

  • DATEADD(dd, DATEDIFF(dd, 0, DateTimeValue), 0)

    _____________
    Code for TallyGenerator

  • yuzzelpiccici (6/4/2009)


    Thx for your help Adi.

    But, if i want to create table like this :

    CREATE TABLE [WeatherInfo] (

    [WeatherID] [int] NOT NULL ,

    [DateNow] [datetime] NOT NULL ,

    [TimeNow] [datetime] NOT NULL ,

    [Until] [datetime] NOT NULL ,

    [Temperatur] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,

    [Symbol] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,

    CONSTRAINT [PK_WeatherInfo] PRIMARY KEY CLUSTERED

    (

    [WeatherID]

    ) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    then i want insert some values :

    INSERT INTO [PMCS].[dbo].[WeatherInfo]([WeatherID], [DateNow], [TimeNow], [Until], [Temperatur], [Symbol])

    VALUES(, , , , , )

    i want the result like this :

    1 - 2009-06-04 - 16:29:23 - 18:29:23 - 30C - Hot

    Thx before 🙂

    You can use the convert function. When you want to get the date part you can use style 101 and when you want to use the time part you can use style 108. Here is an example:

    declare @StartDate datetime

    declare @EndDate datetime

    set @StartDate = '2009-06-04 16:29:23'

    set @EndDate = '2009-06-04 18:29:23'

    INSERT INTO [dbo].[WeatherInfo]([WeatherID], [DateNow], [TimeNow], [Until], [Temperatur], [Symbol])

    VALUES(1, convert(char(10),@StartDate,101), convert(char(10),@StartDate,108), convert(char(10),@EndDate,108), '30C', 'Hot')

    select * from [WeatherInfo]

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Thx for the helps guys 🙂

  • Adi,

    conversion through char is the slowest and resource consuming way to do this.

    Don't use it any more.

    _____________
    Code for TallyGenerator

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply