October 24, 2006 at 6:48 am
Hello,
Could someone possibly let me know how i use SQL to change the date format from DD-MM-YYYY to YYYMMDD.
I'm struggling to do this in SQL.
Thanks,
October 24, 2006 at 7:07 am
You may try like this.
SET DATEFORMAT dmy
GO
SELECT CONVERT(VARCHAR(10),CAST('24-10-2006' AS DATETIME),112)
Ram
October 24, 2006 at 8:49 am
Great Thankyou for your prompt reply.
Out of Interest is it possible to split a date down so I could just have the Year, or the month or date
Eg :-
2006/12/31 to 31 or
2006/12/31 to 12 ?
Thanks.
October 25, 2006 at 1:20 am
There is a T-SQL function called DATEPART (see BOL) that can return you the year, quarter, month, day, etc.
Alternatively the user-defined function (UDF) below, which provides date & time formatting options, might be of some use.
CREATE FUNCTION ufn_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
October 25, 2006 at 6:00 am
You can use the built in function DAY(<your date> ), MONTH(<your date> ) and YEAR(<your date> ).
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply