Click here to Skip to main content
14,325,789 members
Rate this:
Please Sign up or sign in to vote.
See more:
is there any way to change format date in sql server as if select a field with date it is by default take date in format yyyy-mm-dd and is any way in sql server it take like dd-mm-yyyy


if i use query as
select format(datee,'dd - MM - yyyy') from sale2 where datee > ='2019-01-01' and datee <= (select format(getdate(),'dd - MM - yyyy'))


then it is take works but if i use datee='01-01-2019' it is not works

What I have tried:

if i use query as 
<pre>select format(datee,'dd - MM - yyyy') from sale2 where datee > ='2019-01-01' and datee <= (select format(getdate(),'dd - MM - yyyy'))


then it is take works but if i use datee='01-01-2019' it is not works
Posted
Updated 19-Aug-19 0:30am
Comments
irfanansari 19-Aug-19 4:37am
   
i also use
select format(datee,'dd - MM - yyyy') from sale2 where datee >= CONVERT(VARCHAR(10),'02-01-2019', 110) and datee <=CONVERT(VARCHAR(10), getdate(), 110)
Rate this:
Please Sign up or sign in to vote.

Solution 2

While Maciej Los is right, it's probably a very bad thing to do.
Generally speaking, SQL should not care about date formats - that's a presentation function, and should be sorted out in your presentation language, as that is the only place that has access to the user preferences. SQL should store and retrieve format agnostic date / datetime data which has been processed (or will be processed) bu the user interface software. All SQL should get is an DateTime value (preferably UTC so it is time zone and "daylight saving" agnostic as well) via a parameterised query, and return a DateTime value in the same format.

Formatting a date or datetime value in SQL assumes what the user wants - and SQL has no information about the actual user at all, only your app that communicates with the DB has that. The only use for setting a dateformat in SQL is related to "interpretation" of date strings passed to SQL, and that's a bad idea as it can leave your app wide open to SQL Injection which can damage or destroy your DB.
   
Comments
Maciej Los 19-Aug-19 6:23am
   
Very well said!
Rate this:
Please Sign up or sign in to vote.

Solution 1

   
Rate this:
Please Sign up or sign in to vote.

Solution 3

I prefer to address this problem by using unambiguous date formats - always pass dates around as YYYY-MM-DD or YYYYMMDD. This should work for all versions of SQL - SQL Server unambiguous date strings - Matt Randle[^]

You should not have to format dates to use them in queries - to just get the date part of getdate() try this instead:
select format(datee,'dd - MM - yyyy') from sale2 where datee > ='2019-01-01' and datee <= dateadd(dd, datediff(dd, 0, getdate()), 0)
which I adapted from Some Common Date Routines – SQLServerCentral[^]
   
Rate this:
Please Sign up or sign in to vote.

Solution 4

Quote:
is there any way to change format date in sql server

It is a bad idea, use DATE data type , it specially crafted to handle dates and allow dates comparisons.
Assuming datee is date data type in your table, I would use:
select format(datee,'dd - MM - yyyy') from sale2 where datee > =DATE '2019-01-01' and datee <= getdate()

or
select datee from sale2 where datee > =DATE '2019-01-01' and datee <= getdate()

and let formatting be done accordingly to client local date format.
Dates and Times: Date and Time Literals[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100