June 23, 2013 at 10:40 am
I'm trying to restrict a query to go back five years ago.
I have the following query which is not in the format that I need.
SELECT convert(varchar(11), DateAdd(yy, -5, GetDate()))
Returns:
Jun 23 2008
What I need is the following:
'2008-06-23'
Thank you.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 23, 2013 at 10:58 am
SELECT convert(varchar(10), DateAdd(yy, -5, GetDate()),120)
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 23, 2013 at 3:03 pm
Hey, thank you very much!
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 24, 2013 at 1:45 am
Since we're talking about SQL Server 2008 here, any issue with using the date type?
SELECT CONVERT(DATE, DateAdd(yy, - 5, GetDate()));
June 24, 2013 at 10:36 am
I'm using the Derived Date in the WHERE Clause.
WHERE Effective_Date <
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 24, 2013 at 10:38 am
Thanks for the help guys.
I'm using the Data calculation in the WHERE Clause.
WHERE Effective_Date < CONVERT(DATE, DateAdd(yy, - 5, GetDate()));
Does it matter which method I use as far as performance?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 24, 2013 at 10:52 am
Welsh Corgi (6/24/2013)
Does it matter which method I use as far as performance?
If you're only applying the convert to a variable/static value, like GETDATE(), it doesn't matter at all from a performance point of view. If you have to apply it to a column, then the latter is preferable in most scenarios as it's (mostly) SARGable.
July 12, 2013 at 12:18 pm
Create a date variable , assign the value of CONVERT(DATE, DateAdd(yy, - 5, GetDate())) to the variable and use the variable instead in your query. This may be more efficient.
----------------------------------------------------
July 12, 2013 at 12:28 pm
mmartin1 (7/12/2013)
Create a date variable , assign the value ofCONVERT(DATE, DateAdd(yy, - 5, GetDate()))to the variable and use the variable instead in your query. This may be more efficient.
How does using a variable here make it more efficient? The optimizer is smart enough that it does this calculation one time because it is using a constant and not a column.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 12, 2013 at 12:31 pm
Right. It seems intuitive. I've found that has not always been the case with my queries.
----------------------------------------------------
July 12, 2013 at 12:38 pm
mmartin1 (7/12/2013)
Right. It seems intuitive. I've found that has not always been the case with my queries.
I have not experienced that. I would love to see an example of it. I have seen lots of examples proving that it is no different.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 12, 2013 at 3:35 pm
Welsh Corgi (6/24/2013)
Thanks for the help guys.I'm using the Data calculation in the WHERE Clause.
WHERE Effective_Date < CONVERT(DATE, DateAdd(yy, - 5, GetDate()));
Does it matter which method I use as far as performance?
You do need to cast the calculated value as exactly the same data type as the table column. Otherwise, SQL might be forced to do an implicit conversion on the table column, which could cause a huge performance hit. (Btw, this applies to other data types as well, not just datetime-related types.)
In this case, you seem to be doing that already, so you should be OK.
I've not had any issues with a GETDATE() constant vs a variable either, but that could easily happen if the data type of the variable was different from the result of a calculation.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 12, 2013 at 6:33 pm
I didn't invent the code use in this function so can't take credit (or blame), but it comes in handy sometimes. I did turn it into an itvf and added a few extra formatting options so it can be used in a CROSS APPLY to quickly format any column of dates into any format you need. See additional example usage with a CROSS APPLY at bottom of post.
CREATE FUNCTION [dbo].[itvfFormatDateWithMask]
(
@date AS DATETIME
,@format_string VARCHAR(50)
)
RETURNS TABLE
AS
RETURN
(
/*
SELECT * FROM dbo.itvfFormatDateWithMask(GETDATE(),'YYYY-MM-DD HH:mm:ss.fff')
SELECT * FROM dbo.itvfFormatDateWithMask(GETDATE(),'MMMM Dx, YYYY hh:mmtt')
SELECT * FROM dbo.itvfFormatDateWithMask(GETDATE(),'MMM Dx, YYYY')
'YYYY' - full year with century
'YY' - year without century
'Y' - last digit of year
'MMMM' - month name
'MMM' - month name first 3 chars only
'MM' - month number with leading zero
'M' - month number without leading zero)
'DDDD' - day name
'DD' - day number with leading zero
'D' - day number without leading zero
'HH' - hour with leading zero (24 hr format)
'H' - hour without leading zero
'hh' - hour with leading zero (12 hr format)
'h' - hour without leading zero (12 hr format)
'mm' - minutes with leading zero
'm' - minutes without leading zero
'ss' - seconds with leading zero
's' - seconds without leading zero
'fff' - milliseconds with leading zeros
'f' - milliseconds without leading zeros
'tt' - AM or PM
't' - A or P
'x' - st, nd, or th
*/
WITH fourRows(N)
AS (
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
)
,cteTally(N)
AS (
SELECT TOP (50)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum
FROM
fourRows AS A
CROSS JOIN fourRows AS B
CROSS JOIN fourRows AS C
ORDER BY 1
)
,tokenizedString
AS (
SELECT
N
,REPLACE(C,SUBSTRING(CAST(C AS NVARCHAR(50)),CHARINDEX('""',CAST(C AS NVARCHAR(50)),1),CHARINDEX('""',CAST(C AS NVARCHAR(50)),2)),'ZZZZZZZ') AS C
,groupId = DENSE_RANK() OVER (ORDER BY C, _groupId)
FROM
(
SELECT
N
,SUBSTRING(@format_string COLLATE Latin1_General_CS_AS,N,1) AS C
,_groupId = N - ROW_NUMBER() OVER (PARTITION BY SUBSTRING(@format_string COLLATE Latin1_General_CS_AS,N,1) ORDER BY N)
FROM
cteTally
WHERE
N <= LEN(@format_string)
) AS fs)
SELECT
(SELECT
CASE REPLICATE(MIN(C),COUNT(*))
WHEN 'YYYY' THEN RIGHT('0000' + CAST(YEAR(@date) AS NVARCHAR(4)),4)
WHEN 'YY' THEN RIGHT('00' + CAST(YEAR(@date) AS NVARCHAR(4)),2)
WHEN 'Y' THEN CAST(CAST(RIGHT('00' + CAST(YEAR(@date) AS NVARCHAR(4)),1) AS INT) AS NVARCHAR(2))
WHEN 'MMMM' THEN DATENAME(month,@date)
WHEN 'MMM' THEN LEFT(DATENAME(month,@date),3)
WHEN 'MM' THEN RIGHT('00' + CAST(MONTH(@date) AS NVARCHAR(2)),2)
WHEN 'M' THEN CAST(MONTH(@date) AS NVARCHAR(2))
WHEN 'DDDD' THEN DATENAME(weekday,@date)
WHEN 'DD' THEN RIGHT('00' + CAST(DAY(@date) AS NVARCHAR(2)),2)
WHEN 'D' THEN CAST(DAY(@date) AS NVARCHAR(2))
WHEN 'HH' THEN RIGHT('00' + CAST(DATEPART(hour,@date) AS NVARCHAR(2)),2)
WHEN 'H' THEN CAST(DATEPART(hour,@date) AS NVARCHAR(2))
WHEN 'hh' THEN RIGHT('00' + CAST(
CASE DATEPART(hour,@date)
WHEN 12 THEN 12
ELSE DATEPART(hour,@date) % 12
END AS NVARCHAR(2)),2)
WHEN 'h' THEN
CASE WHEN LEFT(
RIGHT('00' + CAST(
CASE DATEPART(hour,@date)
WHEN 12 THEN 12
ELSE DATEPART(hour,@date) % 12
END AS NVARCHAR(2)),2),1) = 0 THEN
RIGHT('00' + CAST(
CASE DATEPART(hour,@date)
WHEN 12 THEN 12
ELSE DATEPART(hour,@date) % 12
END AS NVARCHAR(2)),1)
ELSE
RIGHT('00' + CAST(
CASE DATEPART(hour,@date)
WHEN 12 THEN 12
ELSE DATEPART(hour,@date) % 12
END AS NVARCHAR(2)),2)
END
WHEN 'mm' THEN RIGHT('00' + CAST(DATEPART(minute,@date) AS NVARCHAR(2)),2)
WHEN 'm' THEN CAST(DATEPART(minute,@date) AS NVARCHAR(2))
WHEN 'ss' THEN RIGHT('00' + CAST(DATEPART(second,@date) AS NVARCHAR(2)),2)
WHEN 's' THEN CAST(DATEPART(second,@date) AS NVARCHAR(2))
WHEN 'fff' THEN RIGHT('000' + CAST(DATEPART(millisecond,@date) AS NVARCHAR(3)),3)
WHEN 'f' THEN CAST(DATEPART(millisecond,@date) AS NVARCHAR(3))
WHEN 'tt' THEN
CASE
WHEN DATEPART(hour,@date) >= 12 THEN N'PM'
ELSE N'AM'
END
WHEN 't' THEN
CASE
WHEN DATEPART(hour,@date) >= 12 THEN N'P'
ELSE N'A'
END
WHEN 'x' THEN
CASE
WHEN CAST(DAY(@date) AS INT) IN (1,21,31) THEN N'st'
WHEN CAST(DAY(@date) AS INT) IN (3,23) THEN N'rd'
ELSE N'th'
END
ELSE
MIN(C)
END
FROM
tokenizedString
GROUP BY
groupId
ORDER BY
MIN(N)
FOR
XML PATH('')
,TYPE
).value('(./text())[1]','nvarchar(50)')
AS FormattedDate
)
/*
TESTING SCRIPTS
IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
DROP TABLE #TempTable
CREATE TABLE #TempTable (
[ID] INT IDENTITY(1,1) NOT NULL,
[RandomDate] DATETIME NULL,
PRIMARY KEY (ID))
INSERT INTO #TempTable
SELECT RandomDate
FROM (
SELECT TOP 100
RandomDate = DATEADD(second,ABS(CHECKSUM(NEWID()))%36000, DATEADD(day,ABS(CHECKSUM(NEWID()))%3653+36524,'2000-01-01 00:00:01'))
FROM Master.dbo.SysColumns t1
)d
SELECT *
FROM #TempTable AS tt
CROSS APPLY
dbo.itvfFormatDateWithMask(tt.RandomDate,'MMMM Dx, YYYY h:mtt') AS d1
CROSS APPLY
dbo.itvfFormatDateWithMask(tt.RandomDate,'MM-DD-YYYY HH:mm') AS d2
SELECT * FROM dbo.itvfFormatDateWithMask(GETDATE(),'YYYY-MM-DD HH:mm:ss.fff')
SELECT * FROM dbo.itvfFormatDateWithMask(GETDATE(),'MMMM Dx, YYYY h:mtt')
*/
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply