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?
LVL 2
DaFouAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT CAST(DAY(urDatetimeColumn)as varchar)+'-'+DATENAME (mm,urDatetimeColumn)+'-'+CAST(Year(urDatetimeColumn) as varchar)
0
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
you can easily convert to    DD-MMM-YYYY format using

SELECT CONVERT(varchar(11),GETDATE(),113)
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

 
DaFouAuthor Commented:
wicked, can i also set the language? i want to be able to set it in dutch
0
 
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:
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 PerkinsConnect With a Mentor Commented:
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
 
LowfatspreadConnect With a Mentor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.