Post reply

DateTime input with time always at 00:00:00.0000

  • March 18, 2010 at 9:58 pm

    #146094

    I have a stored proc that has an input variable like:

    @SubsEnd DATETIME='12/31/1900'

    My standard format from my client is MM/dd/yyyy

    So like 03/21/2010 is inputted from a field at the client.(vb.net/asp.net)

    But when this date is inserted into the TBL Datetime field from the variable @SubsEnd I get

    2010/03/21 01:25:01 or whatever

    I just want to tweak the @SubsEnd so that the data in the TBL field has time at 00:00:00.000 every time.

    How can I reset the time part to ZERO or MIDNIGHT? I dont want to do this at the client, prefer at the server.

    Any ideas???:-)

  • March 18, 2010 at 10:15 pm

    #1136435

    This is my attempt to assist you 😀

    DECLARE

    @int date,

    @idate datetime

    SET @int = (SELECT CONVERT(date,getdate()))

    SET @iDate = (SELECT CONVERT(datetime, @int))

    SELECT @int AS Initial_Conversion, @idate AS Result

    --or try CAST & convert

    SELECT IntegerToDatetime = CAST(convert(varchar,@iDate) AS datetime)

    GO

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

  • March 18, 2010 at 11:10 pm

    #1136444

    You sent me on correct highway thanks...:-)

    This is the code the will work for me..

    DECLARE @iDate DATETIME,

    @val INT

    SET @iDate = '12/31/2010 01:25:35'

    SET @val = (SELECT CAST(@iDate AS INT))

    SET @iDate = (SELECT CONVERT(datetime, @val))

    SELECT @iDate AS 'A', CONVERT(CHAR(10), @iDate, 101) AS 'B'

  • March 18, 2010 at 11:15 pm

    #1136446

    glad to be of some help.

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

  • March 19, 2010 at 7:40 am

    #1136712

    Thats a nice one by Henrico too, here is a classic one..

    DECLARE @iDate DATETIME,

    @val INT

    SET @iDate = '12/31/2010 01:25:35'

    SELECT DATEADD( day, 0, DATEDIFF( day, 0, @iDate))

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

  • March 19, 2010 at 10:38 am

    #1136888

    Thanks

  • March 19, 2010 at 11:18 am

    #1136918

    SELECT CAST(FLOOR(CAST(GETDATE() as FLOAT)) AS DateTime)

    Does this define something about me or is it just vanity?

  • March 21, 2010 at 5:50 am

    #1137329

    rich-521822 (3/19/2010)

    SELECT CAST(FLOOR(CAST(GETDATE() as FLOAT)) AS DateTime)

    Casting a date to a float relies on undocumented internal behaviour, and is therefore a bad idea. In 2008, none of the new date/time types can be manipulated in this way, so it is a good example of a bad habit that needs kicking 😉

    Converting to an INT is similar: 2008 and onward do not allow explicit casts to numeric types.

    In addition, only one of the methods presented so far is SARGable - meaning that an index seek might be used. The other methods all require an index scan at least. The SARGable method is the one based on the built-in date functions DATEADD and DATEDIFF:

    SELECT DATEADD(DAY, DATEDIFF(DAY, '2000-01-01', GETDATE()), '2000-01-01');

    That is the code Nabha posted, but I prefer to use a real date rather than a numeric zero.

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • March 21, 2010 at 11:29 am

    #1137359

    Whats with the ';' in the SQL statement

    '2000-01-01');

    ???

    I dont use ';' in SQL 2005, should I ??

  • March 21, 2010 at 3:38 pm

    #1137368

    Digs (3/21/2010)

    Whats with the ';' in the SQL statement

    '2000-01-01');

    ???

    I dont use ';' in SQL 2005, should I ??

    You should start getting in the habit of terminating your SQL statements with a semi-colon. In 2005 there are a couple of statements that require it (e.g. the statement preceding a common table expression must be terminated).

    In 2008 - there are more areas that require the statement to be terminated.

    The semi-colon is a statement terminator.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • March 21, 2010 at 10:20 pm

    #1137405

    Agreed on the FLOAT conversion. Bad thing to do for the future.

    Same goes with the CONVERT not because it's a bad thing for the future but because it's comparatively slow in the face of scalability.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • March 22, 2010 at 6:26 am

    #1137524

    Digs (3/18/2010)

    You sent me on correct highway thanks...:-)

    This is the code the will work for me..

    DECLARE @iDate DATETIME,

    @val INT

    SET @iDate = '12/31/2010 01:25:35'

    SET @val = (SELECT CAST(@iDate AS INT))

    SET @iDate = (SELECT CONVERT(datetime, @val))

    SELECT @iDate AS 'A', CONVERT(CHAR(10), @iDate, 101) AS 'B'

    Casting to an int like this is a really bad idea. The cast from datetime to int uses rounding so if the datetime is in the afternoon it will get rounded to the following day.

    Try using SET @iDate = '12/31/2010 14:25:35' in the above script and see what happens...

    You should really use the code posted by Nabha.

    /SG

  • March 22, 2010 at 6:32 am

    #1137534

    Paul White NZ (3/21/2010)

    In addition, only one of the methods presented so far is SARGable - meaning that an index seek might be used. The other methods all require an index scan at least. The SARGable method is the one based on the built-in date functions DATEADD and DATEDIFF:

    SELECT DATEADD(DAY, DATEDIFF(DAY, '2000-01-01', GETDATE()), '2000-01-01');

    What exactly do you mean with SARGable in this context ?

    I guess that you mean that if you have an indexed datetime column in a table you could find all entries in a particular day using a query like this:

    SELECT MyDate

    FROM table

    WHERE DATEADD(DAY, DATEDIFF(DAY, '2000-01-01', MyDate), '2000-01-01') = '20100301'

    This does not seem to work for me - I always get an index scan. (SQL Server 2008)

    Am I missing something ?

  • March 22, 2010 at 6:47 am

    #1137544

    select cast(cast(getdate() as varchar(12)) as datetime)

    gives

    2010-03-22 00:00:00.000

    John.

  • March 22, 2010 at 7:23 am

    #1137582

    john.campbell-1020429 (3/22/2010)

    select cast(cast(getdate() as varchar(12)) as datetime)

    gives

    2010-03-22 00:00:00.000

    John.

    Like I said in my previous post above... that's a bad idea for performance.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 15 posts - 1 through 15 (of 23 total)

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