Help need converting date field

• Briceston

  SSCrazy

  Points: 2899

  More actions

  September 8, 2017 at 10:06 am

  #325226

  Hi,

  I have a date field that I'm trying to convert the value from DD-M-YY (11-Aug-16) to MM/DD/YYYY (08/11/2016).
  I have tried the below convert statement, but it returns the same values I'm trying to convert.
  
  
Select
t1.ID,
CONVERT(Varchar(10),t1.servicedate,101) as date_
From  Table_01 as t1


• Thom A

  SSC Guru

  Points: 99130

  More actions

  September 8, 2017 at 10:14 am

  #1958757

  I assume, that this value is therefore stored as a varchar? Also, just to confirm, are you storing them as dd-M-yy (08-9-17) or dd-MMM-yy (08-Aug-17) (note M returns the Month number with no leading 0, and MMM returns the first 3 characters of the month's name (and is language specific)). I've assumed, using your example, you mean dd-MMM-yy. Either way, this should work:
  CREATE TABLE #Date (servicedate varchar(9));

  INSERT INTO #Date
VALUES ('08-Aug-17'),('11-Aug-17');
GO

  SELECT *
FROM #Date;
GO
SELECT CONVERT(varchar(10),CONVERT(date,servicedate),101) AS formatteddate, servicedate
FROM #Date;

  GO
DROP TABLE #Date;


  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Briceston

  SSCrazy

  Points: 2899

  More actions

  September 8, 2017 at 10:53 am

  #1958760

  Thom A - Friday, September 8, 2017 10:14 AM

  I assume, that this value is therefore stored as a varchar? Also, just to confirm, are you storing them as dd-M-yy (08-9-17) or dd-MMM-yy (08-Aug-17) (note M returns the Month number with no leading 0, and MMM returns the first 3 characters of the month's name (and is language specific)). I've assumed, using your example, you mean dd-MMM-yy. Either way, this should work:
  CREATE TABLE #Date (servicedate varchar(9));

  INSERT INTO #Date
  VALUES ('08-Aug-17'),('11-Aug-17');
  GO

  SELECT *
  FROM #Date;
  GO
  SELECT CONVERT(varchar(10),CONVERT(date,servicedate),101) AS formatteddate, servicedate
  FROM #Date;

  GO
DROP TABLE #Date;


  Your solution worked great. Thanks.

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  September 8, 2017 at 11:28 am

  #1958765

  I must remind you that all days should be properly stored using the date data types. Format should be used for display only (or maybe some calculation hacks).

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Briceston

  SSCrazy

  Points: 2899

  More actions

  September 8, 2017 at 1:05 pm

  #1958790

  Luis Cazares - Friday, September 8, 2017 11:28 AM

  I must remind you that all days should be properly stored using the date data types. Format should be used for display only (or maybe some calculation hacks).

  Thank you for this insight.