CODEWITHSUNDEEP
sqldatetime
M H
M H

Reputation: 2183

How do you convert SQL mm/dd/yy to mm/dd only?

How do you convert SQL mm/dd/yy datetime to mm/dd only? On Microsoft server.

Thanks all.

Upvotes: 1

Views: 106

Answers (4)

Matt Johnson-Pint
Matt Johnson-Pint

Reputation: 241555

I realize this isn't directly answering your question the way you asked it, but the best advice I can give is: Don't.

Instead, send back the field in its native datetime type. The database is not the place to be doing formatting. Instead, format the date in your application code.

For example, if you are calling SQL Server from a C#/.NET application, you could retrieve the value from a DataReader like this:

DateTime dt = (DateTime) reader["YourDateTime"];

Then you would format it as a string like this:

string s = dt.ToString("MM/dd");

This will ensure that the date is formatted correctly. If you are using a different language to call SQL Server, there are probably similar methods in that language.

One of the problems with the other approach mentioned (trunacating the string) is that the original value might not be formatted in mm/dd/yyyy to begin with. That all depends on the environment settings where the SQL Server is running. If you run the same code on an environment with dd/mm/yyyy settings, you would have unexpected results. This is avoided by using the native data type, the way I described.

Upvotes: 0

Paul Maxwell
Paul Maxwell

Reputation: 35583

With dates and times it is an extremely common mistake to believe that what you see is what is stored. If the field is date, datetime, smalldatetime or datetime2 then what is stored are integers, not strings. So if the field is one of these, then:

convert(varchar(5),[date_field],1)

or

format([date_field],'MM/dd') -- mssql 2012 onward

If the information is a string already then left() will do the job.

Upvotes: 3

Donal
Donal

Reputation: 32713

You can use LEFT to just return the day and month:

SELECT LEFT('12/12/2000', 5)

Upvotes: 0

Pieter Geerkens
Pieter Geerkens

Reputation: 11893

Since you have specified an input format, the input must already be a string. Simply truncate with 

cast(dateIn as char(5)).

Upvotes: 1

Related Questions