How to change date format in SQL server

Question

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 18-Aug-19 22:36pm

irfanansari

Updated 5-Mar-21 21:02pm

Add a Solution

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)

5 solutions

Solution 1

Check out this: SET DATEFORMAT (Transact-SQL) - SQL Server | Microsoft Docs[^]

Posted 18-Aug-19 22:40pm

Maciej Los

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:

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[^]

Posted 18-Aug-19 23:07pm

CHill60

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:

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[^]

Posted 19-Aug-19 0:31am

Patrice T

Solution 6

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)

Posted 5-Mar-21 21:02pm

Member 15091642

Updated 5-Mar-21 21:04pm

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Accepted Answer · 2019-08-18T22:58:00

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.