Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

datetime to 01 december 1990

Posted on 2006-07-13
15
Medium Priority
?
337 Views
Last Modified: 2008-02-26
Hi All,

Is there a way to convert a datetime field to a string with format 11 decemeber 1990 in a select statment?
0
Comment
Question by:DaFou
  • 5
  • 4
  • 3
  • +1
15 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17098672
SELECT CAST(DAY(urDatetimeColumn)as varchar)+'-'+DATENAME (mm,urDatetimeColumn)+'-'+CAST(Year(urDatetimeColumn) as varchar)
0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 400 total points
ID: 17098682
you can easily convert to    DD-MMM-YYYY format using

SELECT CONVERT(varchar(11),GETDATE(),113)
0
 
LVL 2

Author Comment

by:DaFou
ID: 17098684
wicked, can i also set the language? i want to be able to set it in dutch
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17098827
DaFou,

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

Aneesh
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 17099065
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
 
LVL 2

Author Comment

by:DaFou
ID: 17099505
Lowfatspread,

using your suggestion renders english month names. how can i get Dutch month names?
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 800 total points
ID: 17099759
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17099780
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 800 total points
ID: 17101863
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 17106318
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 17106320
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 17106328
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
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 800 total points
ID: 17106352
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17108417
Interesting use of CROSS JOIN.
0
 
LVL 2

Author Comment

by:DaFou
ID: 17136490
wow guys, thx alot. give me a few to decide how to devide the points
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

876 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question