Post reply

Sql server Date conversion

  • September 2, 2019 at 12:29 am

    #3676237

     

    Here is dob data type is date

    select name,salary,dob from person where dob>='2019-08-30'-- Displaying expected Result set

    I want to display the date style like '30/08/2019' so I tried the below query

    select name,salary,convert(nvarchar(12),dob,103) as DateofBirth from person

    where convert(nvarchar(12),dob,103)>='30/08/2019'            --not giving expected result set

    Its displaying the results  less than the '30/08/2019'  date aslo?

    Please correct the query please

  • September 2, 2019 at 4:36 am

    #3676252

    What is the data type of your DOB field?

    How to post data/code on a forum to get the best help.

  • September 2, 2019 at 6:54 am

    #3676264

    remove the convert on the where clause - for display purposes you only need to change the select list.

    and this really should be done on the visualization layer not on the database.

  • September 2, 2019 at 8:30 am

    #3676282

    select name,salary,convert(nvarchar(12),dob,103) as DateofBirth from person

    where dob>='2019-08-30'

    • This reply was modified 4 years, 10 months ago by  SQLEx.

  • September 2, 2019 at 9:52 am

    #3676295

    But  I want to display the date style like '30/08/2019' ?!!

  • September 2, 2019 at 9:55 am

    #3676296

    The reason that you're getting the incorrect result is because you're expected a varchar value to have the same ordering a date type value; that isn't true. Take today's date, which in SQL Server is stored as a binary value but we'll use the ISO format yyyyMMdd to represent it (20190902) and then your conversion giving the varchar value '02/09/2019'. Now we'll compare that value to Saturday's date, the 31st August 2019, which will be 20190831 and '31/08/2019' respectively.

    Now, is the date 20190902 less than (before) or greater than (after) 20190831? The answer is greater than (after); and this is quite clear to anyone familiar with dates.

    Now, is the varchar '02/09/2019' less than or greater than '31/08/2019'? The answer is that it is less than (before). Yes, that's right, the "date" '02/09/2019' is before the the "date" '31/08/2019' when expressed as a varchar with that format.

    So why is that the case? Well, because it isn't a date, it's a string, and strings order by each character left to right. So, what are the first characters of those strings? Well you have '0' and '3' respectively, so is '0' less than'3'? Clearly it is, therefore  '02/09/2019' must be before '31/08/2019'. It doesn't matter if the string represents a date, the fact is it isn't one; it's a varchar and SQL Server will treat it as one.

    If you're ever doing date comparisons, never use a formatted varchar; it will only cause you problems; use an ISO format (yyyyMMdd or yyyy-MM-ddThh:mm:ss.sss). In fact, if you're ever working with dates in general, don't use a varchar to store them. The date and time data types exist for a reason. Ideally you should never return a varchar for a date/time in the SELECT either; however there are times where you might (for example, when generating XML data in SQL Server it's best to define the format). The presentation layer should be defining the way the date is displayed and should receive a date/time data type from the RDBMS. Then the presentation layer knows it's a date (so can handle it accordingly too) and easily format it as that's what it's good at.

    • This reply was modified 4 years, 10 months ago by  Thom A.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • September 2, 2019 at 4:47 pm

    #3676395

    Great Explanation and Good finger tips ..Thank you very much for that.

    but as a learner still i am unable write the query.

    can you write the query so i will try to understand

     

    thank you

     

  • September 2, 2019 at 8:58 pm

    #3676449

    The problem is that (as Thom has said previously) you are trying to convert the date to a text string and then returning all records where the converted date (i.e. a text string) is greater than or equal to the text expression:

    select name,salary,convert(nvarchar(12),dob,103) as DateofBirth from person

    where convert(nvarchar(12),dob,103)>='30/08/2019'

    Try:

    select name,salary,convert(nvarchar(12),dob,103) as DateofBirth from person

    where dob >= '20190830'    as said by SQLEx previously

    The last expression ('20190830') unambiguously converts to a date that SQL server can understand as the 30th August 2019. I would recommend you use that format

    So that you understand the concepts you may want to look at the Stairway series of articles on this website, particularly the https://qa.sqlservercentral.com/stairways/stairway-to-t-sql-dml page and subsequent articles

    Also, and as said before, it's not necessary to convert the date as shown in the SSMS query to a date in a particular format (unless this is a homework question and it's been requested by a tutor) - let the reporting solution or the application do that:

    select name, salary, dob as DateofBirth from person

    where dob >= '20190830'

    We all understand that you want to see the date in that format, but what we are saying is that you do not need to do that in SSMS - if you want to be a professional systems developer you need to understand that it's not the right place to do that. If you can say how the data is going to be presented (for example using SSRS, Crystal Reports or Excel) or what you have been asked to do then I'm sure someone will be able to help you

  • September 3, 2019 at 10:54 am

    #3676814

    Thank you very much for your explanation. Now I am very clear

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

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