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
>
>
