Convert Datetime into date data type in SQLserver

Question

I have to remove time stamp from a datetime datatype in SQL server and want to keep the output data type as date with this format -> mm/dd/yyyy (This outpu should be displayed as date data type not as a string data type ). I am using SQL server 2012.

Please help

Thanks

You'll have to go into a bit more detail. Do you have a table that you're trying to change the data type of? Are you simply trying to change this in a select statement? — Rich Benner, Nov 14 '16 at 15:28
Thanks for your time Rich, Its a date column from the date dimension table. since it is a datetime column , i see the timestamp. i dont want to see the timestamp in my output and want to see the date in this format -> mm/dd/yyyy — Drake, Nov 14 '16 at 17:49
Ok cool, check Gordon's answer. I think that should cover it — Rich Benner, Nov 14 '16 at 17:55

score 4 · Answer 1 · edited Jan 03 '22 at 11:25

4

In SQL Server, a date is essentially a datetime with no time component. You can do the conversion simply by doing:

cast(datetimecol as date)

Both, though are stored using an internal format. The output format is controlled by internationalization settings (or the database default). You can output either type of column as a string whose format is mm/dd/yyyy by using conversion format 101:

select convert(varchar(10), <col>, 101)

The output is then a string with the format you want.

edited Jan 03 '22 at 11:25

Nimantha

6,405
6
28
69

answered Nov 14 '16 at 15:30

Gordon Linoff

1,242,037
58
646
786

Thanks Gordon, I want the output type as date without the time stamps in mm/dd/yyyy format – Drake Nov 14 '16 at 17:48
@Drake . . . Then focus on converting the value to a *string* not a *date*. – Gordon Linoff Nov 15 '16 at 02:19

Wojciech Zieliński · Answer 2 · 2016-11-15T08:29:29.217

0

Have you tried CONVERT(date, [FieldName]) ?

Please find documentation on this and CAST() here.

edited Nov 15 '16 at 08:29

answered Nov 14 '16 at 15:31

Wojciech Zieliński

34
6

yes i did, so with this approach the data type is date and i dont see the timestamp but it gives me the date in this format -> yyyy-mm-dd, – Drake Nov 14 '16 at 17:51
CONVERT(date, [FieldName], 101) – Wojciech Zieliński Nov 15 '16 at 08:28

Convert Datetime into date data type in SQLserver

2 Answers2