Make date from several strings

Hello all

Someone gave me this:

Month Day Year Hour Min Sec
9 9 2006 15 9 36

And I need to make a descent date format out of it which looks like this:

Sun Sept 09 15:09:36 CEST 2006

I can concatenate the whole thing but I'm stuck with the "sunday" part.

I'm sure there are some experst out there who know how to do this is in 1 minute;)

Regards

Worf

to get the name wrap left(convert(varchar(30),dateadd(d,day-1,'19000101'),100),3)

around your Day column

example

select left(convert(varchar(30),dateadd(d,dayCol-1,'19000101'),100),3) from(
select 9 as dayCol, 9 as m, 2006 as y)x

Denis The SQL Menace
http://sqlservercode.blogspot.com/

|||

Exellent!

Many thanks!

Worf

|||

So far I have the following code but it gives me an error:

Conversion failed when converting datetime from character string.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[MakeDate] (@.Month int, @.Day int, @.Year int, @.Hour int, @.Min int, @.Sec int)
RETURNS nvarchar(50)
AS
BEGIN

DECLARE @.C_Date nvarchar(50)
DECLARE @.TempMonth varchar(3)
DECLARE @.TempWeekDay varchar(3)

SET @.TempMonth = LEFT(CONVERT(nvarchar,(DATENAME(month, (@.Year + @.Month + @.Day)))), 3)
SET @.TempWeekDay = LEFT(CONVERT(nvarchar,(DATENAME(weekday, (@.Year + @.Month + @.Day)))),3)
SET @.C_Date = @.TempWeekDay + ' ' + @.TempMonth +' '+ @.Day +' ' + @.Hour +':'+ @.Min +':'+ @.Sec + ' CEST ' + @.Year

RETURN(@.C_Date)

END;

when eexecuting it looks like this:

SELECT [myDatabase].[dbo].[MakeDate] (08,23,2006,12,23,45)

Sigh

Worf

|||

How about this?

CREATE FUNCTION [dbo].[MakeDate] (@.Month int, @.Day int, @.Year int, @.Hour

int, @.Min int, @.Sec int)

RETURNS nvarchar(50)

AS

BEGIN

DECLARE @.C_Date nvarchar(50)

DECLARE @.TempMonth varchar(3)

DECLARE @.TempWeekDay varchar(3)

DECLARE @.MonthNames VARCHAR(100)

DECLARE @.DT datetime

SET @.DT = DATEADD(minute,@.Min,

DATEADD(second,@.Sec,

DATEADD(hour,@.Hour,

DATEADD(day,@.Day,

DATEADD(month,@.Month-1,

DATEADD(year,@.Year-1900,-1))))))

SET @.MonthNames = 'Jan Feb Mar Apr May Jun Jul Aug SeptOct Nov Dec'

DECLARE @.DayNames VARCHAR(100)

SET @.DayNames = 'Sun Mon Tue Wed Thu Fri Sat'

SET @.TempMonth = RTRIM(SUBSTRING(@.MonthNames,(DATEDIFF(month,0,@.DT)%12)*4+1,4))

SET @.TempWeekDay = LEFT(DATENAME(weekday,DATEDIFF(day,0,@.DT)),3)

SET @.C_Date = @.TempWeekDay + ' ' + @.TempMonth +' '

+ RTRIM(DATEPART(Day,@.DT)) +' ' + CONVERT(CHAR(8),@.DT,8)

+ ' CEST ' + RTRIM(DATEPART(Year,@.DT))

RETURN(@.C_Date)

END

GO

SELECT dbo.MakeDate(2,13,2004,11,12,4)

go

This won't work except for English, but Sept is not the

SQL Server short month name, so I didn't use DATENAME

on the month part. This will also accept invalid

parts, and change November 32 to Dec 2, for example.

Test thoroughly!

Steve Kass

Drew University

http://www.stevekass.com

Worf@.discussions.microsoft.com wrote:

> Hello all

>

> Someone gave me this:

>

> Month Day Year Hour Min Sec

> 9 9 2006 15 9 36

>

> And I need to make a descent date format out of it which looks like

> this:

>

> Sun Sept 09 15:09:36 CEST 2006

>

> I can concatenate the whole thing but I'm stuck with the "sunday" part.

>

> I'm sure there are some experst out there who know how to do this is in

> 1 minute;)

>

> Regards

>

> Worf

>

>