0

I have a requirement. I have datetime field and I want data in datatype=date

Existing date: 2019-11-13 00: 00: 00: 000 ; datatype=datetime Expected output require: 11/13/2019 (mm/dd/yyyy) ;

datatype= date

Please help me.

Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
Dinesh
  • 67
  • 9
  • 3
    [DateTime have no display format.](https://stackoverflow.com/questions/30032915/how-to-cast-the-datetime-to-time/30033028#30033028) If you want a specific format, you need a string. – Zohar Peled Feb 13 '19 at 11:12

3 Answers3

1

If the core requirement is a right type then:

SET DATEFORMAT MDY;
SELECT CAST(GETDATE() as DATE);

Explicit DATEFORMAT added becaise the output depends on a language settings, so can be yyyy/mm/dd or mm/dd/yyyy, some apps can be sensitive to this, as example SSRS.

However, if there is still a requirement to get value in a precisely right format on a database side, then consider to use a FORMAT statement:

SELECT FORMAT(GETDATE(), 'MM/dd/yyyy')
Alexander Volok
  • 5,630
  • 3
  • 17
  • 33
0

You can use CONVERT() :

SELECT t.datecol, CONVERT(VARCHAR(10), t.datecol, 101) 
FROM table t;

Your desired date format requires varchar type, if you want date only then you can do instead : 

SELECT t.datecol, CONVERT(DATE, t.datecol) 
FROM table t;

If you don't want to convert the type, then these type of conversation should do in presentation layer instead.

Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
0

I have datetime field and I want data in datatype=date

If you want the data type as DATE, in that case you can try like following. 

 SELECT CAST(YourDateTimeColumn AS DATE) from [YourTable]

Formatting part you should be doing in UI.

PSK
  • 17,547
  • 5
  • 32
  • 43
  • I'm getting in yyyy-mm-dd. I want in mm/dd/yyyy and output datatype=date – Dinesh Feb 13 '19 at 11:08
  • If you want the datatype as date, in that case you need to use the default format only. Whatever formatting you want, you need to do in the UI. – PSK Feb 13 '19 at 11:09