Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

GetDate() 5 Years ago without time formated 'YYYY-MM-DD' Expand / Collapse
Author
Message
Posted Sunday, June 23, 2013 10:40 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, July 27, 2014 6:10 AM
Points: 4,179, Visits: 4,260
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/

Post #1466535
Posted Sunday, June 23, 2013 10:58 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 6:36 PM
Points: 1,886, Visits: 18,565
SELECT convert(varchar(10), DateAdd(yy, -5, GetDate()),120)



__________________________________________________________________
you can lead a user to data....but you cannot make them think !
__________________________________________________________________
Post #1466537
Posted Sunday, June 23, 2013 3:03 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, July 27, 2014 6:10 AM
Points: 4,179, Visits: 4,260
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/

Post #1466545
Posted Monday, June 24, 2013 1:45 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:47 AM
Points: 2,422, Visits: 7,443
Since we're talking about SQL Server 2008 here, any issue with using the date type?

SELECT CONVERT(DATE, DateAdd(yy, - 5, GetDate()));




Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1466612
Posted Monday, June 24, 2013 10:36 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, July 27, 2014 6:10 AM
Points: 4,179, Visits: 4,260
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/

Post #1466835
Posted Monday, June 24, 2013 10:38 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, July 27, 2014 6:10 AM
Points: 4,179, Visits: 4,260
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/

Post #1466836
Posted Monday, June 24, 2013 10:52 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:39 AM
Points: 1,232, Visits: 9,678
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.
Post #1466839
Posted Friday, July 12, 2013 12:18 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, July 28, 2014 11:45 AM
Points: 317, Visits: 823
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.
Post #1473206
Posted Friday, July 12, 2013 12:28 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 13,138, Visits: 11,977
mmartin1 (7/12/2013)
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.


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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1473213
Posted Friday, July 12, 2013 12:31 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, July 28, 2014 11:45 AM
Points: 317, Visits: 823
Right. It seems intuitive. I've found that has not always been the case with my queries.
Post #1473214
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse