Datetime data type and its format

  • I have two tables

    Table1 Col1 datetime

    Table2 COLA datetime

    I have to select their dates in an equal where lclause. However for some reasons that are unknown to me, Col1 has this format 2008-05-01 11:03:35.203 and COLA has this format 5/1/2008 11:03:35 AM

    How can I 'instruct' COLA to be in the same format as Col1?

    Thanks

  • The format should not affect the determination of equality as SQL Server stores datatime as a numeric behind the scenes. As far as forcing the data to look the same you can use set dateformat m/d/y in your queries or you can do an explicit convert(varchar(15), data_col, 101) or whatever format you want.

    BTW - I don't know why they would have different formats in display.

  • Somehow when col1 has 2008-05-01 11:16:23.067 and COLA

    has 5/1/2008 11:16:23 AM the equal condition fails ( I think the millisecs) got dropped.

    When I did an insert into COLA I did have a convert

    CONVERT(VARCHAR(30),GetDate(),21) to have COL1 format but it still ends up with just 5/1/2008 11:16:23 AM.

    Thanks

  • If the milliseconds are being dropped the data is being stored as smalldatetime or prior to the insert it is smalldatetime. To get equality you need to convert the datatime value (with milliseconds) to smalldatetime. If you really need milliseconds then the process inserting into ColA needs to be sure to be using datetime all the way through the process. I really think the column is defined as smalldatetime.

  • The format doesn't matter. Dates and times are stored in SQL as numbers. The missing milliseconds will cause it to not be equal.

    In datetime, dates are stored as the number of days since 1 Jan 1900, and the number of 1/300th of a second since midnight. Smalldatetime uses the same start, but doesn't store are many bytes of data (only accurate to minutes). Per your original statement, both columns are datetime, not smalldatetime, so that's not the problem (unless you miswrote that).

    To try this out for yourself, try:

    select cast(0 as datetime)

    select cast(1 as datetime)

    select cast(.001 as datetime)

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • yes both columns are of data type datetime.

    Thanks, I will give it a try.

  • How are the column being populated? Somehow the data in ColA is being created without the milliseconds, which tells me at some level it is being used as a smalldatetime. If it is being passed using a smalldatetime variable and then inserted you will not have the milliseconds.

    Try this:

    Declare @smalldate smalldatetime, @date datetime, @datefromsmalldate datetime

    Set @date = getdate()

    Set @smalldate = @date

    Set @datefromsmalldate = @smalldate

    Select @date as full_date, @smalldate as smalldate, @datefromsmalldate as date_from_small_date

  • declare @CreateDate varchar(50) ,@RowsFound int

    set @CreateDate=CONVERT(VARCHAR(30),GetDate(),21)

    set @RowsFound=0

    select @RowsFound=count(*) from dbo.RECON_MASTER M where

    M.INSTANCE=@INSTANCE

    and M.ACCOUNT_NAME=@ACCOUNT_NAME

    if @RowsFound > 0

    Begin

    -- last column is col1

    insert into dbo.RECON_MASTER_HISTORY

    select M.*,@CreateDaTe from dbo.RECON_MASTER M

    where

    M.INSTANCE=@INSTANCE

    and M.ACCOUNT_NAME=@ACCOUNT_NAME

    -- this is COLA that is updated with the exact same value

    insert into dbo.RECON_MASTER_HISTORY_2EXCEL

    (INSTANCE,ACCOUNT_NAME, COSTCENTER,HistCreateDate)

    values (@INSTANCE,@ACCOUNT_NAME, @COSTCENTER,@CreateDate)

    end

    Thanks!

Viewing 8 posts - 1 through 7 (of 7 total)

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