Home Forums SQL Server 2008 T-SQL (SS2K8) Date Format RE: Date Format

  • October 6, 2010 at 6:42 am

    #1232450

    LIYA (10/6/2010)

    Hi All,

    i have a table with following structure

    CREATE TABLE [dbo].[DateFormat](

    [WDate] [smalldatetime] NULL

    ) ON [PRIMARY]

    insert into DateFormat

    select '2010-06-18 07:33:17' union all

    select '2010-06-18 07:40:18' union all

    select '2010-06-18 18:40:10' union all

    select '2010-06-18 14:45:17' union all

    i need the result as follows

    Wdate

    18/06/2010 07:33:17 AM

    18/06/2010 07:40:18 AM

    18/06/2010 06:40:10 AM

    18/06/2010 02:45:17 AM

    I tried the following code

    SELECT CONVERT(varchar(12), WDate, 103) + ' ' + SUBSTRING(CONVERT(varchar(28), WDate, 9), 13, 15) AS Date

    FROM Dateformat

    i got the result as follows . but it's having the mi sec part like

    18/06/2010 07:33:17:000AM

    Hi

    One thing dont forget that smalldatetime will not give you seconds so i have kept as datetime

    DECLARE @Var AS DATETIME

    SET @Var = '2010-06-18 17:33:23'

    Select Convert(varchar,@Var,103)+' '+Convert(varchar,Case when DATEPART( HOUR, @Var )>12 then DATEPART( HOUR, @Var )-12 else DATEPART( HOUR, @Var ) end )+':'+Convert(varchar,DATEPART( MINUTE, @Var ))+':'+Convert(varchar(5),DATEPART( SS, @Var ))

    + ' ' +CASE WHEN DATEPART( HOUR, @Var) > 12 THEN 'PM' ELSE 'AM' END

    Thanks

    Parthi

    Thanks
    Parthi