Post reply

Problem with Dates !!!!!!!!!!!!

  • December 25, 2013 at 5:09 am

    #301645

    Consider the below query

    CREATE TABLE #TEMP (Id int,VALUE NVARCHAR(50))

    INSERT INTO #TEMP VALUES(1,'')

    INSERT INTO #TEMP VALUES(2,'2013/08')

    DECLARE @FirstDate date,

    @EndDate date,

    SELECT @FirstDate=VALUE FROM #TEMP WHERE ID=1

    SELECT @EndDate=VALUE FROM #TEMP WHERE ID=2

    i want my FirstDate and @EndDate variable to store the values as both that of ' ' (blank) and a date.

    I tried by kepping the datatype as Varchar but it did not work, i tried with convert but it too did not work.

    So is it possible to make my variable such that it stores both ' '(blank) and a date.

    Thanks

  • December 25, 2013 at 10:52 am

    #1675895

    The closes i can come to this problem is

    SELECT LEFT(CONVERT(DATE,VALUE,111),7) FROM #TEMP WHERE ID=2

    still it is not working. Any one who can help !!!!!!!!

  • December 25, 2013 at 11:23 am

    #1675899

    Shadab Shah (12/25/2013)

    The closes i can come to this problem is

    SELECT LEFT(CONVERT(DATE,VALUE,111),7) FROM #TEMP WHERE ID=2

    I'm not entirely sure what you are trying to accomplish, but I think this is what you are looking for.

    SELECT

    case when VALUE = '' then '' else

    LEFT(CONVERT(DATE,VALUE + '/01',111),7)

    end

    FROM #temp

    __________________________________________________________________________________________________________
    How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • December 25, 2013 at 7:00 pm

    #1675909

    I am trying to store the value return by the select in variable. I took that variable as varchar as well as date but nohing seems to work.....

  • December 25, 2013 at 8:57 pm

    #1675914

    If you wanted to store NULL and a DATE value, then it would be trivial. What's the point storing ''? Is that not the same as Null (unknown)? If not, what does BLANK mean?

  • December 28, 2013 at 5:20 am

    #1676208

    I strongly vote against a NVARCHAR data type to store date values (especially, with a length of 50 characters being allowed).

    What's the reason for using NVARCHAR instead of DATETIME?


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • December 31, 2013 at 9:29 am

    #1676617

    Hi.

    1. The posters who said that a blank date is a bad idea are correct. If you are using the script as a demo to show what you are encountering in the application's data, then convert blanks to NULL with a CASE. Much easier to work with NULL.

    2. I'm not sure that a DATE type of field will handle converting partial dates. I think that if the date is not valid, such as a year-month that we are implying from your example, then you must choose then to make the date a NULL (invalid), or if it is a "circa" date, you must complete it with a day.

    On the other hand, if it is a JULIAN date, you need to do this (copied from http://www.ehow.com/how_12073756_convert-julian-date-sql.html):

    CREATE FUNCTION convert_julian_to_datetime(@julian_date char(5)) RETURNS datetime AS

    BEGIN

    RETURN (SELECT DATEADD(day, CONVERT(int,RIGHT(@julian_date,3)) - 1, CONVERT(datetime, LEFT(@julian_date,2) + '0101', 112)))END

    Thanks

    John.

  • January 1, 2014 at 11:57 am

    #1676745

    Shadab Shah (12/25/2013)

    I am trying to store the value return by the select in variable. I took that variable as varchar as well as date but nohing seems to work.....

    That is correct. You cannot store a blank in a DATE datatype. The blank will automatically be converted to 1900-01-01. To wit, you're simply using the wrong datatype for this problem.

    If you have a reporting requirement, let's see the code for that instead of the simplified example that you original posted. Then, we might be able to help.

    --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)

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

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