0

I have 2 tables in my database.

  1. Customers_Details
  2. Transaction

Both tables have a date column. I want to change the date format from YYYY-MM-DD to DD-MM-YYYY permanently for both tables.

Can i do it in one query or do I have to write separate query for each table?

The query which I have written is not changing the format of date permanently. 

select 
    [customer_ID], [Gender], [Area_code],
    convert(varchar, dob, 105) as DOB 
from 
    [dbo].[customer_details]
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jatin Gupta
  • 33
  • 8
  • 11
    SQL Server's `DATE` type is a binary type that records a timestamp as a number; there is no formatting data embedded into it. It is formatted for presentation by clients. So the short answer to your question is "no", while the long one depends on what you're using to display the dates. – Jeroen Mostert Aug 26 '19 at 10:30
  • 3
    [Bad habits to kick : declaring VARCHAR without (length)](https://sqlblog.org/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length) - you should **always** provide a length for any `varchar` variables and parameters that you use – marc_s Aug 26 '19 at 11:42
  • [How does SQL Server store date values?](https://stackoverflow.com/questions/30032915/how-to-cast-the-datetime-to-time/30033028#30033028) – Zohar Peled Aug 26 '19 at 11:45

2 Answers2

0

dates are stored in an internal format. You wouldn't want to look at that string of bits anyway. But you do want to store the data that way -- to use date functions, so ordering is correct, and so on.

What you can do is add a computed column that has the string form that you want:

alter table [dbo].[customer_details] add dob_ddmmyyyy as (convert(varchar(10), dob, 105);

You can then use dob_ddmmyyyy in queries.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    It's worth noting that it's unlikely you'll want to query that column if you're using range queries (or if you're not going to `PERSIST`) the column. `'12/05/1957'` is **after** `'09/12/2018'`. – Thom A Aug 26 '19 at 11:14
0

You can use following method, (tested in SQL 2014 and higher)

select FORMAT (GETDATE(), 'yyyy-MMM-dd')

In your case it would be as follows:

 select [customer_ID],[Gender],[Area_code], 
        format(dob, 'dd-MM-yyyy') as DOB 
 from   [dbo].[customer_details]
Shekar Kola
  • 1,287
  • 9
  • 15