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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy