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/
So far I have the following code but it gives me an error:
Conversion failed when converting datetime from character string.
set
ANSI_NULLS ONSET
@TempMonth = LEFT(CONVERT(nvarchar,(DATENAME(month, (@Year + @Month + @Day)))), 3)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
>
>