Converting a date

  • How would I convert a date field that outputs, for example, Dec 1 2015 12:00AM into 12/01/2015? I need to use it in a date range.

  • cory.bullard76 (12/2/2015)


    How would I convert a date field that outputs, for example, Dec 1 2015 12:00AM into 12/01/2015? I need to use it in a date range.

    If it it truly a date column you don't need to convert anything. I suspect however from your example that it isn't a date column? If it is not a datetime you can cast/convert it easily enough but you should consider changing the datatype to store datetime information in a datetime datatype.

    https://msdn.microsoft.com/en-us/library/ms187928.aspx

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • It maybe a datetime....but, I'd like to take that field and run the prior days data. Could I do that with a field that displays data in that format?

  • Ok, it is a date time field type....and I show records for Dec 1 2015.....but, when I say that field = 12/01/2015 I get 0 results

  • cory.bullard76 (12/2/2015)


    It maybe a datetime....but, I'd like to take that field and run the prior days data. Could I do that with a field that displays data in that format?

    Columns do not display data in a format. Is the column a datetime? Can it be reliably converted to a datetime if it isn't?

    Generally speaking for this type of query you would do something along these lines.

    where YourColumn >= dateadd(day, datediff(day, 0, GETDATE()) - 1, 0)

    AND YourColumn < dateadd(day, datediff(day, 0, getdate()), 0)

    This is using a number of the date functions from this great post. http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/[/url]

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • cory.bullard76 (12/2/2015)


    Ok, it is a date time field type....and I show records for Dec 1 2015.....but, when I say that field = 12/01/2015 I get 0 results

    That is because it is a datetime which also has the time value. So a row where the time is 8am is NOT equal to value with no time. You can cast your column as a DATE or see the code I posted above.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean Lange (12/2/2015)


    cory.bullard76 (12/2/2015)


    Ok, it is a date time field type....and I show records for Dec 1 2015.....but, when I say that field = 12/01/2015 I get 0 results

    That is because it is a datetime which also has the time value. So a row where the time is 8am is NOT equal to value with no time. You can cast your column as a DATE or see the code I posted above.

    You also need to use single quotes, otherwise it'll perform 2 integer divisions which will finally result in 0. Zero will be converted into date 01/01/1900.

    You're statement could be like this:

    field = '12/01/2015'

    Or even better, using the ISO format which won't depend on language settings:

    field = '20151201'

    Or as Sean said, using a range.

    field >= '20151201' AND field < '20151202'

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • cory.bullard76 (12/2/2015)


    Ok, it is a date time field type....and I show records for Dec 1 2015.....but, when I say that field = 12/01/2015 I get 0 results

    Where somedatetimefield >= '2015-12-01 00:00:01'

    and somedatetimefiled <= '2015-12-01 23:59:59'

  • yakko_Warner (12/2/2015)


    cory.bullard76 (12/2/2015)


    Ok, it is a date time field type....and I show records for Dec 1 2015.....but, when I say that field = 12/01/2015 I get 0 results

    Where somedatetimefield >= '2015-12-01 00:00:01'

    and somedatetimefiled <= '2015-12-01 23:59:59'

    Careful. With datetime, the maximum value for the time component in a given day is 23:59:59.997, so that will miss any row from that day that is greater than 23:59:59.000 and less than or equal to 23:59:59.997, as well as any row with a time component between 00:00:00.000 and 00:00:00.997. While that may be a fairly small risk depending on your data, the fix is so easy there's no point not to be safe. It's safest to just use midnight of the next day as an exclusive upper bound with 'less than', and midnight of the desired day as an inclusive lower bound with 'greater than or equal to'.

    CREATE TABLE #TestDateTime (somedatetimefield DATETIME);

    INSERT INTO #TestDateTime

    VALUES

    ('2015-12-01 00:00:00.997'),

    ('2015-12-01 13:33'),

    ('2015-11-30 18:47'),

    ('2015-12-01 23:59:59.5');

    --Misses the first and fourth rows above

    SELECT * FROM #TestDateTime

    Where somedatetimefield >= '2015-12-01 00:00:01'

    and somedatetimefield <= '2015-12-01 23:59:59';

    --This is safer, and also isn't dependent on the minimum and maximum values

    --for the datatype used (datetime or datetime2, for example).

    SELECT * FROM #TestDateTime

    Where somedatetimefield >= '20151201'

    and somedatetimefield < '20151202';

    DROP TABLE #TestDateTime;

    Cheers!

  • Thanks Jacob, That's exactly what i was trying to figure out.

    I wasn't sure if you left the time portion off if that would work.

    However as your awesome response is very very helpful and educational at the same time.

  • yakko_Warner (12/2/2015)


    cory.bullard76 (12/2/2015)


    Ok, it is a date time field type....and I show records for Dec 1 2015.....but, when I say that field = 12/01/2015 I get 0 results

    Where somedatetimefield >= '2015-12-01 00:00:01'

    and somedatetimefiled <= '2015-12-01 23:59:59'

    You can also try:

    Where cast(somedatetimefield as date) = '2015-12-01'

    Don Simpson



    I'm not sure about Heisenberg.

  • A CAST on the database column would likely preclude the use of an available index, while using the native values would allow index usage, if available. The >= and < pair is the best choice.

  • PhilPacha (12/4/2015)


    A CAST on the database column would likely preclude the use of an available index, while using the native values would allow index usage, if available. The >= and < pair is the best choice.

    You're right. However, casting a datetime column to date, still allows an index to be used. I'm not sure why would it happen like that, but it does. You could test it if you want.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Thanks for the update about datetime --> date; I wasn't aware of that.

  • PhilPacha (12/4/2015)


    Thanks for the update about datetime --> date; I wasn't aware of that.

    I just wanted to include a test because people shouldn't just trust anything they read online. 😉

    --===== Conditionally drop the test table to make reruns easier.

    IF OBJECT_ID('tempdb..#SomeTestTable','U') IS NOT NULL

    DROP TABLE #SomeTestTable

    ;

    --===== Declare some obviously named variables

    DECLARE @NumberOfRows INT,

    @StartDate DATETIME,

    @EndDate DATETIME,

    @Days INT --This is the "range"

    ;

    --===== Preset the variables to known values

    SELECT @NumberOfRows = 1000000,

    @StartDate = '20150101', --Inclusive

    @EndDate = '20200101', --Exclusive

    @Days = DATEDIFF(dd,@StartDate,@EndDate)

    ;

    --===== Create the test table with "random constrained" integers and floats

    -- within the parameters identified in the variables above.

    SELECT TOP (@NumberOfRows)

    SomeRandomDateTime = RAND(CHECKSUM(NEWID())) * @Days + @StartDate

    INTO #SomeTestTable

    FROM sys.all_columns ac1

    CROSS JOIN sys.all_columns ac2

    ;

    --===== Create an index on the datetime column

    CREATE INDEX IXDateDatetime ON #SomeTestTable(SomeRandomDateTime);

    ;

    --===== Show ten rows of the table

    SELECT *

    FROM #SomeTestTable

    WHERE CAST(SomeRandomDateTime as date) = CAST(GETDATE() AS date);

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

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

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