• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 339
  • Last Modified:

datetime to 01 december 1990

Hi All,

Is there a way to convert a datetime field to a string with format 11 decemeber 1990 in a select statment?
0
DaFou
Asked:
DaFou
  • 5
  • 4
  • 3
  • +1
4 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT CAST(DAY(urDatetimeColumn)as varchar)+'-'+DATENAME (mm,urDatetimeColumn)+'-'+CAST(Year(urDatetimeColumn) as varchar)
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
you can easily convert to    DD-MMM-YYYY format using

SELECT CONVERT(varchar(11),GETDATE(),113)
0
 
DaFouAuthor Commented:
wicked, can i also set the language? i want to be able to set it in dutch
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
Aneesh RetnakaranDatabase AdministratorCommented:
DaFou,

I am not aware of any method yet... you can handle this at the front end

Aneesh
0
 
LowfatspreadCommented:
like this

select stuff(left(convert(varchar(30),TheDate,113),len(convert(varchar(30),TheDate,113))-13) , 4,3,datename(m,Thedate))
 from (select getdate() as Thedate
          union select '20061201' union select '20061231 01:01:01.000') as x
 
0
 
DaFouAuthor Commented:
Lowfatspread,

using your suggestion renders english month names. how can i get Dutch month names?
0
 
Anthony PerkinsCommented:
Select      CAST(Day(YourColumnName) As varchar(2)) +
      CASE
            WHEN MONTH(YourColumnName) = 1 THEN ' Januari '
            WHEN MONTH(YourColumnName) = 2 THEN ' Februari '
            WHEN MONTH(YourColumnName) = 3 THEN ' Maart '
            WHEN MONTH(YourColumnName) = 3 THEN ' April '
...

      END +
      CAST(Year(YourColumnName) As varchar(4))
0
 
Anthony PerkinsCommented:
Typo, this:
 WHEN MONTH(YourColumnName) = 3 THEN ' April '

Should have been:
 WHEN MONTH(YourColumnName) = 4 THEN ' April '

I will leave the rest of the months as an exercise for you.
0
 
Anthony PerkinsCommented:
This might be a tad simpler:
Declare @MonthNames varchar(120)
Set @MonthNames = 'januari  februari maart    april    mei      juni     juli     augustus septemberoktober  november december'
Select      CAST(Day(YourColumnName) As varchar(2)) + ' ' +
      RTRIM(SUBSTRING(@MonthNames, (MONTH(YourColumnName) - 1) * 9 + 1, 9)) + ' ' +
      CAST(Year(YourColumnName) As varchar(4))

Or if you prefer it all in statement:
Select      CAST(Day(YourColumnName) As varchar(2)) + ' ' +
      RTRIM(SUBSTRING('januari  februari maart    april    mei      juni     juli     augustus septemberoktober  november december', (MONTH(YourColumnName) - 1) * 9 + 1, 9)) + ' ' +
      CAST(Year(YourColumnName) As varchar(4))
0
 
LowfatspreadCommented:
borrowing from acperkins...

select stuff(left(convert(varchar(30),TheDate,113),len(convert(varchar(30),TheDate,113))-13) , 4,3,rtrim(substring(Mname,(Month(thedate)-1)*9 +1,9))
 from (select getdate() as Thedate
          union select '20061201' union select '20061231 01:01:01.000') as x
cross join (select 'januari  februari maart    april    mei      juni     juli     augustus septemberoktober  november december' as Mname) as y
     
0
 
LowfatspreadCommented:
sorry

select stuff(left(convert(varchar(30),TheDate,113),len(convert(varchar(30),TheDate,113))-13) , 4,3,rtrim(substring(Mname,(Month(thedate)-1)*9 +1,9)))
 from (select getdate() as Thedate
          union select '20061201' union select '20061231 01:01:01.000') as x
cross join (select 'januari  februari maart    april    mei      juni     juli     augustus septemberoktober  november december' as Mname) as y
0
 
LowfatspreadCommented:
sorry

select stuff(left(convert(varchar(30),TheDate,113),len(convert(varchar(30),TheDate,113))-13) , 4,3,rtrim(substring(Mname,(Month(thedate)-1)*9 +1,9)))
 from (select getdate() as Thedate
          union select '20061201' union select '20061231 01:01:01.000') as x
cross join (select 'januari  februari maart    april    mei      juni     juli     augustus septemberoktober  november december' as Mname) as y
0
 
LowfatspreadCommented:
actually we don't need the left expression at all.. so its

select stuff(convert(char(11),TheDate,113) , 4,3,rtrim(substring(Mname,(Month(thedate)-1)*9 +1,9)))
 from (select getdate() as Thedate
          union select '20061201' union select '20061231 01:01:01.000') as x
cross join (select 'januari  februari maart    april    mei      juni     juli     augustus septemberoktober  november december' as Mname) as y


convert date to DD mmm YYYY
then replace the 3 character month name (starting at byte 4) with its full length Dutch equivalent...

0
 
Anthony PerkinsCommented:
Interesting use of CROSS JOIN.
0
 
DaFouAuthor Commented:
wow guys, thx alot. give me a few to decide how to devide the points
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now