Sql server Date conversion

Question

Post reply

Sql server Date conversion

shiva996

Old Hand

Points: 350
More actions
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

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

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

DesNorton SSC-Insane Points: 24386 More actions · Answer 1

What is the data type of your DOB field?

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

frederico_fonseca SSCoach Points: 16154 More actions · Answer 2

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.

SQLEx Grasshopper Points: 11 More actions · Answer 3

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

where dob>='2019-08-30'

This reply was modified 5 years, 2 months ago by SQLEx.

shiva996 Old Hand Points: 350 More actions · Answer 4

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

Thom A SSC Guru Points: 99130 More actions · Answer 5

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 5 years, 2 months ago by Thom A.

Thom~

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

shiva996 Old Hand Points: 350 More actions · Answer 6

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

irgp SSC Journeyman Points: 80 More actions · Answer 7

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://www.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

shiva996 Old Hand Points: 350 More actions · Answer 8

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