July 5, 2006 at 9:48 am
What's the best way to get a yyyymmdd field into something useful for publication...
i.e.
12/5/1950
or even December 5, 1950.
Thanks in advance.
July 5, 2006 at 10:00 am
Hello Ryan,
I didn't get you on the requirement but I thought that you need to get the date in yyyymmdd format or the month date, yyyy format.
If so, can you try these...
select convert(varchar(12), getdate(), 101)
select convert(varchar(20), getdate(), 107)
Thanks and have a nice day!!!
Lucky
July 5, 2006 at 10:03 am
Ryan,
What datatype is the originating column?
Greg
Greg
July 5, 2006 at 11:16 am
bigint
July 5, 2006 at 11:43 am
This is what I understand that you want. Be sure to tell us how it was that a datetime got stored as a bigint instead. I'm fascinated.
DECLARE @SillyDate bigint
SELECT @SillyDate=19501205
SELECT SUBSTRING(sillyString,5,2)
+'/'+SUBSTRING(sillyString,7,2)
+'/'+SUBSTRING(sillyString,1,4)
FROM (SELECT [sillyString]=CONVERT(VARCHAR(8),@sillyDate))f
/*----------
12/05/1950
or for the other format you wanted.....*/
DECLARE @SillyDate bigint
SELECT @SillyDate=19501205
SELECT DATENAME(MONTH,f.thedate)
+' '+DATENAME(DAY,f.thedate)
+', '+DATENAME(YEAR,f.thedate)
FROM (SELECT [thedate]=CONVERT(datetime,CONVERT(VARCHAR(8),@sillyDate),101))f
/*
-----------------
December 5, 1950
*/Best wishes,
Phil Factor
July 5, 2006 at 12:01 pm
I'm still a bit confused....I've got some 200 records under the column name birthday, which is bigint yyyymmdd ...
So I'm a little confused where the name of my table fits in and where the column name fits in...
Sorry for being dense.
July 5, 2006 at 12:55 pm
SELECT CONVERT(SMALLDATETIME,(CAST YourBIGINTColumn AS CHAR(8)),112)
FROM dbo.YourTable
This assumes that your BIGINT is always in full YYYYMMDD format that is February 9th this year was 20060209
July 5, 2006 at 4:04 pm
Quite. Sorry. My fault for not explaining fully. Just as David has shown, but for the other date format you wanted.....
--depending on what you are doing with the data you could do...
SELECT DATENAME(MONTH,f.thedate)
+' '+DATENAME(DAY,f.thedate)
+', '+DATENAME(YEAR,f.thedate)
FROM (SELECT [thedate]=CONVERT(datetime,CONVERT(CHAR(8),Birthday),101) FROM yourTable)f
--
--or you could do it as a user function
--
---------------------------------------------
CREATE function dbo.ufsBigintToStringDate
--Returns a date in the format mmmm dd, yyyy
--from a bigint in the form yyyymmdd
(
@BirthdayAsBigint bigint
)
RETURNS VARCHAR(80) AS
BEGIN
DECLARE @StringRepresentation VARCHAR(80)
DECLARE @DateTimeRepresentation Datetime
SELECT @DateTimeRepresentation=CONVERT(datetime,CONVERT(CHAR(8),@BirthdayAsBigint),101)
SELECT @StringRepresentation=
DATENAME(MONTH,@DateTimeRepresentation)
+' '+DATENAME(DAY,@DateTimeRepresentation)
+', '+DATENAME(YEAR,@DateTimeRepresentation)
RETURN (@StringRepresentation)
END
----------------------------------------------
--and then use it as follows
SELECT dbo.ufsBigintToStringDate(birthday) FROM yourTable
I hope that does the trick! Thanks for coming up with an interesting problem
Best wishes,
Phil Factor
July 7, 2006 at 2:22 am
Below is a user-defined function that takes a date/time (of type DATETIME but can be passed in the Unseperated String format - yyyymmdd) and a format (string such as 'dd/mm/yyyy' or 'mm/dd/yy', etc) from which it returns a formated date & time.
It is fairly easy to use and has proved useful on occassions.
I couldn't work out how to attach files (not even sure if it is possible) so I've
reporduced the entire UDF below.
CREATE FUNCTION udf_FormattedDate(
@DateTime DATETIME,
@Format VARCHAR(50))
RETURNS VARCHAR(50)
WITH ENCRYPTION
AS
/***************************************************************************************************
* Converts a given date/time from DATETIME to VARCHAR in a given format. Valid date/time-part
* components, which can be divided with separator characters, for the @Format string are:
*
* - dd : 2-digit day (01-31)
* - mm : 2-digit month (01-12)
* - mmm : first 3 characters of month name (Jan, Feb, Mar, etc)
* - mmmm : full month name (January, February, March, etc)
* - yy : last 2 digits of year (00-99)
* - yyyy : 4-digit year
* - hh : hours (00-23)
* - nn : minutes (00-59)
* - ss : seconds (00-59)
* - ms : milliseconds (000-999)
* - AM : indicates a 12-hour clock
*
* -------------------------------------------------------------------------------------------------
* PARAMETER:
* @DateTime - Is an expression that returns a DATETIME, or a character string in date format.
* @Format - Is a character string that specifies the required format.
*
* RETURN:
* VARCHAR(50) - Is the specified @DateTime expressed in the specified @Format.
**************************************************************************************************/
BEGIN
DECLARE @dd CHAR(2)
DECLARE @FrmttdDateTime VARCHAR(50)
DECLARE @hh CHAR(2)
DECLARE @mm CHAR(2)
DECLARE @mmm CHAR(3)
DECLARE @mmmm VARCHAR(10)
DECLARE @ms CHAR(3)
DECLARE @nn CHAR(2)
DECLARE @ss CHAR(2)
DECLARE @yy CHAR(2)
DECLARE @yyyy CHAR(4)
IF (@DateTime IS NULL)
SET @FrmttdDateTime = NULL
ELSE
BEGIN
SET @FrmttdDateTime = @Format
SET @dd = RIGHT('0' + CAST(DAY(@DateTime) AS VARCHAR), 2)
SET @mm = RIGHT('0' + CAST(MONTH(@DateTime) AS VARCHAR), 2)
SET @mmmm = DATENAME(m, @DateTime)
SET @mmm = LEFT(@mmmm, 3)
SET @yyyy = CAST(YEAR(@DateTime) AS VARCHAR)
SET @yy = RIGHT(@yyyy, 2)
SET @hh = RIGHT('0' + CAST(DATEPART(hh, @DateTime) AS VARCHAR), 2)
SET @nn = RIGHT('0' + CAST(DATEPART(n, @DateTime) AS VARCHAR), 2)
SET @ss = RIGHT('0' + CAST(DATEPART(ss, @DateTime) AS VARCHAR), 2)
SET @ms = RIGHT('00' + CAST(DATEPART(ms, @DateTime) AS VARCHAR), 3)
IF (CHARINDEX('AM', @Format) > 0)
BEGIN
IF (@hh > '12')
BEGIN
SET @hh = CAST(CAST(@hh AS TINYINT) - 12 AS CHAR(2))
SET @FrmttdDateTime = REPLACE(@FrmttdDateTime, 'AM', 'PM')
END
ELSE
IF (@hh < '10')
SET @hh = RIGHT(@hh, 1)
END
SET @FrmttdDateTime = REPLACE(@FrmttdDateTime, 'dd', @dd)
SET @FrmttdDateTime = REPLACE(@FrmttdDateTime, 'mmmm', @mmmm)
SET @FrmttdDateTime = REPLACE(@FrmttdDateTime, 'mmm', @mmm)
SET @FrmttdDateTime = REPLACE(@FrmttdDateTime, 'mm', @mm)
SET @FrmttdDateTime = REPLACE(@FrmttdDateTime, 'yyyy', @yyyy)
SET @FrmttdDateTime = REPLACE(@FrmttdDateTime, 'yy', @yy)
SET @FrmttdDateTime = REPLACE(@FrmttdDateTime, 'hh', @hh)
SET @FrmttdDateTime = REPLACE(@FrmttdDateTime, 'nn', @nn)
SET @FrmttdDateTime = REPLACE(@FrmttdDateTime, 'ss', @ss)
SET @FrmttdDateTime = REPLACE(@FrmttdDateTime, 'ms', @ms)
END
RETURN(@FrmttdDateTime)
END
July 12, 2006 at 10:48 pm
You are correct... cannot attach files... nicely formatted code. Welcome aboard!
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply