1

I am having below formatted Date time value in my SQL Server database table.

2017-02-01 14:31:53.000
2017-09-01 14:54:11.000

I want to swap the Month & Date values like

2017-01-02 14:31:53.000 
2017-01-09 14:54:11.000

Is there any way to achieve it? There are multiple records with such format.

I tried using Excel to convert the same and create query. but it is not working.

Vijay
  • 176
  • 1
  • 3
  • 12
  • 1
    It would be better to let sql server handle the storing part. You need to convert the datetime coming from db to whatever format you want. – Chetan Jan 16 '17 at 10:44
  • Actually there was some error while entering data in SQL before by previous application. It swapped the places of month & date. Now I am going to correct it... The dates you are seeing are 2 & 9 of January 2017. – Vijay Jan 16 '17 at 10:52
  • DateTime are stored without it's display format. If you are storing DateTime values in a varchar column you should change it to a DateTime column. – Zohar Peled Jan 16 '17 at 10:55
  • Column's data type is date time only. – Vijay Jan 16 '17 at 10:58
  • But it did not threw error due to Months value was not greater than 12. – Vijay Jan 16 '17 at 10:59
  • Ok. You can wrire code to retrieve such records, convert datetime to string, swap month and date by string operation, convert it back to datetime using proper format string and update it to db using parameterized queries. – Chetan Jan 16 '17 at 11:00
  • Which version of sql server are you using? – SqlZim Jan 16 '17 at 16:27
  • Solved it by writing application to convert date time in desired format.... – Vijay Jan 23 '17 at 07:29

2 Answers2

0

As I wrote in my comment, DateTime are stored without display format.
If you want to select the dates in a specific format, you need to use either Format() (available in versions 2012 or higher) or CONVERT() (supports only predefined formats)

Now, assuming your desired format is yyyy-mm-dd hh:mi:ss, you can use convert with 120 style:

SELECT CONVERT(char(19), DateTimeColumn, 120) As FormattedDateTime
FROM TableName
Community
  • 1
  • 1
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
0
/*
@formatString(varchar)
- the format string to use (Examples "dd mm yyyy", "mmm.dd.yy")
Description:

Formats a given date based on the format specified in @formatString
d - one digit day (when applicable)
dd- two digit day
ddd- short day name
dddd- long day name
m- one digit month (when applicable)
mm- two digit month
mmm- short month name
mmmm- long month name
yy- two digit year
yyyy- four digit year
*/
create function dbo.fnFormatDate
(
 @inputDate datetime,
 @formatString varchar(25)
)
returns varchar(20) as
begin
declare @returnValue varchar(25)

-- Declare local vairables
declare @formattedDate varchar(25),
@day varchar(20), @month varchar(20), @year varchar(20),
@dayFormat varchar(5), @monthFormat varchar(5), @yearFormat varchar(5)

set @dayFormat = ''
set @monthFormat = ''
set @yearFormat = ''

-- Convert the supplied date to day mon year (25 Jan 2008)
set @formattedDate = convert(varchar, @inputDate, 106)

-- If the format string contains a format for the day
if charindex('d', @formatString) > 0
 -- Get the day format string
 set @dayFormat = master.dbo.fn_pcre_replace(@formatString, '.*?(d{1,4}).*',     '$1')

-- If the format string contains a format for the month
if charindex('m', @formatString) > 0
-- Get the month format string
set @monthFormat = master.dbo.fn_pcre_replace(@formatString, '.*?    (m{1,4}|M{1,4}).*', '$1')

-- If the format string contains a format for the year if charindex('y', @formatString) > 0 -- Get the year format string set @yearFormat = master.dbo.fn_pcre_replace(@formatString, '.?(y{2,4}).', '$1')

-- Format the day value based on the format string for the day select@day = case @dayFormat when 'dd' then master.dbo.fn_pcre_replace(@formattedDate, '^(\d+).*', '$1') when 'ddd' then substring(datename(dw, @formattedDate), 1, 3) when 'dddd' then datename(dw, @formattedDate) else convert(varchar, day(@formattedDate)) end

-- Format the month value based on the format string for the month select@month = case @monthFormat when 'mm' then master.dbo.fn_pcre_replace(convert(varchar, @inputDate, 101), '^(\d+)/.*', '$1') when 'mmm' then master.dbo.fn_pcre_replace(@formattedDate, '\d+\s(\w+)\s\d+', '$1') when 'mmmm' then datename(m, @formattedDate) else convert(varchar, month(@formattedDate)) end

-- Format the year value based on the format string for the year select@year = case @yearFormat
when 'yy' then substring(convert(varchar, year(@formattedDate)), 3, 2) else convert(varchar, year(@formattedDate)) end

set @returnValue = @formatString

-- If the day format was specified if @dayFormat <> '' -- Replace the day format string with the actual day value set @returnValue = master.dbo.fn_pcre_replace(@returnValue, @dayFormat, @day)

-- If the month format was specified if @monthFormat <> '' -- Replace the month format string with the actual month set @returnValue = master.dbo.fn_pcre_replace(@returnValue, @monthFormat, @month)

-- If the year format was specified if @yearFormat <> '' -- Replace the year format string with the actual year set @returnValue = master.dbo.fn_pcre_replace(@returnValue, @yearFormat, @year)

-- Return the formated value return @returnValue end

muffaddal shakir
  • 81
  • 1