Post reply

Date Format

  • June 25, 2004 at 9:09 am

    #108105

    Hi all,

    This is something that have me confused. When ran in Query analyzer

    create table #temp ( myDate datetime )

    insert into #temp values ( getdate() )

    select cast(myDate as datetime(1)) as myDate from #temp

    select cast(myDate as datetime(101)) as myDate from #temp

    select cast(myDate as datetime(3)) as myDate from #temp

    select cast(myDate as datetime(103)) as myDate from #temp

    drop table #temp

    ...output

    (1 row(s) affected)

    myDate                                                

    ------------------------------------------------------

    2004-06-25 10:47:35.867

    (1 row(s) affected)

    myDate                                                

    ------------------------------------------------------

    2004-06-25 10:47:35.867

    (1 row(s) affected)

    myDate                                                

    ------------------------------------------------------

    2004-06-25 10:47:35.867

    (1 row(s) affected)

    myDate                                                

    ------------------------------------------------------

    2004-06-25 10:47:35.867

    (1 row(s) affected)

    I would have expected the following format ( from BOL )

    1 101 USA mm/dd/yy

    3 103 British/French dd/mm/yy

    What I'm looking for is something that will return me only the date part of a date time; like to_date function in Oracle. I'm fairly new to SQL Server. I was able to achieve through the use of CAST(CONVERT(VARCHAR(10),myDate,102) AS DATETIME) but I don't like this double converting when I should be able to use only one cast with the proper format.

    Thanks in advance

     

     

     

  • June 25, 2004 at 10:08 am

    #511995

    According to BOL, CAST does not accept a STYLE input. Only CONVERT accepts a STYLE input.

    -SQLBill

  • June 25, 2004 at 10:42 am

    #512004

    Hi..i think u mean....gettin date only from

    datetime..,..in that case

    CONVERT stmts wud help u the most...

    chk out styles like 112, 114

    hope this helps

  • June 25, 2004 at 10:57 am

    #512008

    "What I'm looking for is something that will return me only the date part of a date time..."

    The equivalent of Oracle's TRUNC is this:

    select cast(convert(varchar(15), getdate(), 101) as datetime) which returns a datetime datatype without the time - by convention we make it 2004-06-25 00:00:00.000 since datetime always stores the time component.  Many people hide this in a UDF.  I don't bother - unecessary overhead. 

    The styles refer to the varchar version of a datetime.  You may convert at display time in a select statement.  You were casting datetime as datetime, doing nothing.  Here is what you meant to do:

    create table #temp ( myDate datetime )

    insert into #temp values ( getdate() )

    select convert(varchar(15),myDate, 1) as myVarcharDate from #temp

    select convert(varchar(15),myDate, 101) as myVarcharDate from #temp

    select convert(varchar(15),myDate, 3) as myVarcharDate from #temp

    select convert(varchar(15),myDate, 103) as myVarcharDate from #temp

    drop table #temp

     

    [font="Courier New"]ZenDada[/font]

  • June 25, 2004 at 11:38 am

    #512017

    Thank you all for your input!!!

    This forum as proven to be very effective I will continue posting question and hopefully one day change my stat of Newbie to something else

Viewing 5 posts - 1 through 4 (of 4 total)

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