No! Not that kind of fun with your date. It seems lately a lot of Date conversion questions have been popping up. So I'd thought I'd list them all here with their solutions and the author that has supplied them. I will be adding to this list, everytime we come across other date manipulation requests.
1. If I only have the week and Year how do I displayJAN, FEB, MAR, ect Asked by Chandra78.
I suggested:
DECLARE @week int, @year int
SELECT @week = 23, @year = 2005
SELECT CONVERT(char(3)
,DATEADD(ww,@week-1
,CONVERT(datetime,'01/01/'+CONVERT(char(4),@Year)
)
),100)
2. How Do I get the last day of the Month. Was Asked by AskSQLTeam (as well as many others in the past)
Rob and Corey offer their suggestions as:
There's Just a slight type-o in Rob's post in the thread...he was missing the trailing parenthesis
DECLARE @datecol datetime
SELECT @datecol = '06/01/2005'
SELECT DateAdd(day,-1,DateAdd(month,DateDiff(month,0,@dateCol)+1,0))
Corey's Solution Appears to be a little simpler
SELECT dateadd(mm,1,@dateCol - day(@dateCol)+1)-1
3. How do I only Get the date portion of a datetime value
Alway the great question. So much so that M$ decided to put it in SQL Server 2005 as 2 distinct datatypes. Like every other RDBMS that I've ever worked with. Unfortunatley it proved too much for the developers, and was pulled from the release. Jeff, however discuss a work around to date and time isolationism in SQL Server in his blog. After several thread back and forth (it sometimes is very difficult to asertain what the real question is) was "For example if I wanted to see all data for dates greater or equal to 01/01/2005“. The short answer From Rob and madhivanan is:
SELECT * FROM myTable WHERE dateCol>='1/1/2005'
But I suspect the real question to be the comparison of two dates and eliminating the time Componet. While Jen's Answer with DATDIFF :
SELECT * FROM myTable WHERE datediff(day,@d1,@d2)=0
Does this, I believe it's a stage II (NonSargable) Predicate and will incur a scan. The question usually is how do I see my Data between these 2 dates. While this also shows an Index scan, it does mention that it may be a full scan, or only a range, which is what I believe it's doing.
USE Northwind
GO
DECLARE @datecol1 datetime, @datecol2 datetime
SELECT @datecol1 = '1996-09-01', @datecol2 = '1996-09-30'
SELECT *
FROM Orders
WHERE OrderDate > @datecol1 AND OrderDate < @datecol2
4. The Counting Days and Excluding Weekend and Holidays recurring question. I think Nigel was the first time I saw a solution to this problem. Nigel's Site, has some of the most invaluable stuff you will ever find.
5. Hours Till Margaritaville was a thing I messed around with that counted down the hours until 5:00 Friday. Enigma (and his Sql Gladiators) however took it to a whole other level in his link.
6. Date of first day of week for current week was just asked recentley by steve_o and was answered by thombpil. He suggested:
select dateadd(dd, (datepart(dw, GetDate()) * -1) + 2, GetDate())
7. Win32 FILETIME values. This is a must entry here. Arnold came to the rescue that day Almost 2 years ago (man it's been 2 years?). It's amazing what third party vendors will think up for storing data in SQL Server. Just painful....I'm not sure if they just wanted to store the data that way, or they thought they were saving space.
SELECT CAST(((
CAST(CAST(SUBSTRING(@d, 1, 2) AS int) AS float) * 16777216.0
+ CAST(CAST(SUBSTRING(@d, 3, 3) AS int) AS float)) * 16777216.0
+ CAST(CAST(SUBSTRING(@d, 6, 3) AS int) AS float))/ 864000000000.0 - 109207.0 AS datetime)
8. Help: Date in field table and Select condition...well this was just plain silly...As Tara points out
9. To get first and last day of a week as clboren asks, in their very first SQLTeam post. Tara suggests the use of a numbers table (which, after many years doing sql I never even thought about before until reading about here at SQL Team) or Michael who employees a user defined function F_START_OF_WEEK. Thanks again to Tara for pointing this out. Since is was 6:30 EST, it was way past Margaritaville time (OK, it was on Thursday and I had to coach little league), and I would have never seen it.
10. Date as Int ...which seems to be, as Arnold point out as a “Wild guess: it's seconds from UNIX epoch.“ Seems to hold true. We'll keep an eye out if lassew ever comes back to confirm this, BUT Arnolds Formula seems pretty well to hold true.
DECLARE @dok_dt int
SELECT @dok_dt = 1047034683
SELECT DATEADD(s, @dok_dt, '19700101 00:00:00')
11. Start of week Functions by Micheal
12. A very nice background about Dates can be found in this thread by Tibor Karaszi
13. Jeff Smith's very good article in his blog, Date Only and Time Only User Defined Dataypes in SQL Server 2000
14. How do I convert the current date in to a Julian Date? Assuming ccyyddd
CREATE FUNCTION udf_Julian (
@d datetime
)
RETURNS varchar(7)
BEGIN
RETURN (SELECT CONVERT(char(4),YEAR(@d))+CONVERT(varchar(3),DATEPART(dy,@d)))
END
SELECT GetDate(), dbo.udf_Julian(GetDate())
GO
15. How do I create a DB2 Date from SQL Server
Create Function SQLDateToDB2Date(@TargetDate datetime)
returns varchar(30)
as
begin
return replace(replace(convert(varchar(30), @TargetDate, 121),':', '.'), ' ', '.')+'000'
end
16. ...and how do I turn DB2 dates into SQL Server datetime
well you can't really, but you can fake it
DECLARE @x varchar(26)
SELECT @x = '2001-01-01-12:59:59:123456'
SELECT CONVERT(datetime,SUBSTRING(STUFF(@x,11,1,' '),1,23))
17. Stay tuned...more to come I'm sure