Click here to Skip to main content
14,926,216 members
Please Sign up or sign in to vote.
1.00/5 (1 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 5-Mar-21 21:02pm
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)

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!
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:
SQL
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[^]
   
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:
SQL
select format(datee,'dd - MM - yyyy') from sale2 where datee > =DATE '2019-01-01' and datee <= getdate()

or
SQL
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[^]
   
if your data type is DateTime then you no need to convert it, in later you can convert its format as you want but if your are using varchar or nvarchar then you can use CONVERT() as below code

SELECT convert(datetime, '23/10/2020', 103)
   
v2

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900