September 21, 2016 at 8:11 pm
Got a Column "funding_date DATETIME" ( has date as well as timestamp).
Why? Does it really go down to seconds and nanoseconds question? Should not you be using the DATE instead? Will save you space and make your queries so much easier
CONVERT(CHAR (10), L.funding_date, 101) <=CONVERT(CHAR(10), CURRENT_TIMESTAMP-90, 101)
The old Sybase convert () function was put in the language for COBOL programmers. That language stores dates as character strings, and lets programmer determine how to format it. The COBOL programmers really miss this so Sybase gave in. Today, we actually have a real temporal datatypes! We also have the current_timestamp and DATE data type as per ANSI/ISO standards. DATE is small, runs fast and should be used whenever possible
shouldn't the first statement be the correct one, as it would be better to convert L.funding_date
No, the data should have been stored in the correct format in the database in the first place. Taking it from a temporal data type to a string back to temporal data type, etc. is a waste of time and shows needlessly bad design. You can play safe by using CAST(CURRENT_TIMESTAMP AS DATE) until Microsoft adds the ANSI/ISO standard CURRENT_DATE to their offering.
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
September 22, 2016 at 3:25 pm
SQLPain (9/20/2016)
Thanks, so L.FundingDate <= CAST(GETDATE()-30 AS DATE) that's the best way to do it.
I am just wondering if
L.fundingDate < dateadd(m, -1, cast(getdate() as date))
Would not serve your purpose better.
Consider the following results, and how one falls in the prior month and the other returns a value still in the current month.
select Cast('20160831' as datetime) -30
select Cast('20160228'as datetime) -30
----------------------------------------------------
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply