x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

Fun with Dates (Date Conversion examples)

Update: 11/19/2009

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. How Do I get the Last Friday of the Month

 Well, we've been hacking away in this thread

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=136027

But so far, this is the only Hack I could come up with

 

DECLARE @dateCol datetime
SELECT @dateCol = GetDate()

SELECT LastWeekDay
  FROM (
SELECT DATEADD(mm,1,@dateCol - DAY(@dateCol)+1)-1 AS LastWeekDay
UNION ALL
SELECT DATEADD(mm,1,@dateCol - DAY(@dateCol)+1)-2 AS LastWeekDay
UNION ALL
SELECT DATEADD(mm,1,@dateCol - DAY(@dateCol)+1)-3 AS LastWeekDay
UNION ALL
SELECT DATEADD(mm,1,@dateCol - DAY(@dateCol)+1)-4 AS LastWeekDay
UNION ALL
SELECT DATEADD(mm,1,@dateCol - DAY(@dateCol)+1)-5 AS LastWeekDay
UNION ALL
SELECT DATEADD(mm,1,@dateCol - DAY(@dateCol)+1)-6 AS LastWeekDay
UNION ALL
SELECT DATEADD(mm,1,@dateCol - DAY(@dateCol)+1)-7 AS LastWeekDay
) AS XXX
WHERE DATENAME(WeekDay,LastWeekDay) = 'Friday'

Print | posted on Thursday, June 02, 2005 12:07 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: Fun with Dates (Date Conversion examples)

All the colors and fonts on your post are hurting my eyes !!! :)
6/2/2005 3:39 PM | Jeff
Gravatar

# re: Fun with Dates (Date Conversion examples)

What? you don't work with shades on?

Hey I even thew in a plug your way...
Gravatar

# re: Fun with Dates (Date Conversion examples)

Brett's posts are always lively. He's a DBA, not a front end guy. ;-)
6/2/2005 4:36 PM | Tara
Gravatar

# re: Fun with Dates (Date Conversion examples)

Thanks Tara...it's been added.

Front end guy? God forbid it.

Without data, there is no Front end.

MOO

Gravatar

# re: Fun with Dates (Date Conversion examples)

You still remember that Hours_till_Margaritaville thing ... i had almost forgotten about it ... :)
6/5/2005 1:28 PM | Enigma
Gravatar

# re: Fun with Dates (Date Conversion examples)

I find that

WHERE OrderDate > @datecol1 AND OrderDate < @datecol2

most often means

WHERE OrderDate >= @datecol1 AND OrderDate < MIDNIGHT(@datecol2)

and the "rounding up" for MIDNIGHT() is a bit of a pig in SQL:

DATEADD(Day, DATEDIFF(Day, 0, @MyDate)+1, 0)
6/8/2005 9:26 AM | Kristen
Gravatar

# re: Fun with Dates (Date Conversion examples)

HI, I've been busting my brains with this stupid project...
And I cracked and decided to look for help.
I'm suppose to have a coulmn (PEN_DATE) with a date like: mm/dd/yyyy no time stamp HH-MM-ss, I've been trying this:
CREATE TABLE tblPLAYER (
PLAYERNO INT NOT NULL PRIMARY KEY,
PEN_DATE CHAR(10)NOT NULL
CONSTRAINT CK_PEN_DATE CHECK (PEN_DATE LIKE '[0-1][0-9][/][0-3][0-9][/][1-9][7-9][0-9][0-9]'
OR PEN_DATE LIKE '[0-1][0-9][/][0-3][0-9][/][2-9][0-9][0-9][0-9]'),)

because the date has to be bigger than 1970.
Can you please tell me what to do.
THANKS
6/22/2005 9:55 AM | Vlad
Gravatar

# re: Fun with Dates (Date Conversion examples)

Thanks for all this helpfull informations
8/31/2005 6:01 PM | Rob
Gravatar

# Henry

Your colors are usefull but it is hard for the etez when we see the page for the first time ;)
9/7/2005 1:37 PM | henrydess@gmail.com
Gravatar

# Henry

Your colors are usefull but it is hard for the eyes when we see the page for the first time ;)
9/7/2005 1:38 PM | henrydess@gmail.com
Gravatar

# What Colors

What Colors? They look like the colors in QA for the most part.

9/13/2005 1:20 PM | Brett
Gravatar

# re: Fun with Dates (Date Conversion examples)

I like the colors :D

I would like to see how others have converted dates into days of the week. I need to keep the dates, but manglement wants to know what kind of OT we're running on weekends vs. weekdays.

Any ideas?

Thanks
Kell
10/3/2005 11:44 AM | WhoaNellie
Gravatar

# What Colors?

The colors should appear as what shows up in QA.

Weekend vs. weekdays shouldn't be too hard. Are you thinging os something specific?

Manglement...funny.

10/3/2005 12:09 PM | Brett
Gravatar

# re: Fun with Dates (Date Conversion examples)

I'm using SQL 8.0 and Excel 2000 for front end. It shouldn't be hard, but I'm having problems b/c when I do the wday function, it doesn't store it as a Monday it stores it as 10/3/05. I want it to store it as a Monday so that I can pivot and say "We worked 350 hrs OT on Mondays in 2005". KWIM? The VP's are using a pivot table, so I can't really have them input anything as a query so to speak. I'm lucky they can even refresh the thing correctly.

Kell
10/3/2005 1:42 PM | WhoaNellie
Gravatar

# re: Fun with Dates (Date Conversion examples)

Hello? Anyone?

Please tell me this can be done?!?!?!

Sigh...

Kell
10/5/2005 3:50 PM | WhoaNellie
Gravatar

# re: Fun with Dates (Date Conversion examples)

Wow thanks for this, it was what I needed
11/17/2005 3:49 PM | greg
Gravatar

# re: Fun with Dates (Date Conversion examples)

Thanks for the sweet solution for an Oracle-head learning SQL Server like me. Oracle just seems so much more graceful about datemath and manipulation. But I could be wrong...
2/1/2006 11:29 AM | JP
Gravatar

# re: Fun with Dates (Date Conversion examples)

Oracle and graceful in the same sentence...don't get me wrong, my background goes all the way back to DB2 2.3 OS/360, with quite a few years of Oracle 8.i...

But graceful?

Bullet-proof perhaps, but graceful?

2/1/2006 8:37 PM | Brett
Gravatar

# re: Fun with Dates (Date Conversion examples)

How do you call a "C" routine(function) from sql? How about, a "C" function(s) from a WIndows DLL file??

Thanks, Thad
3/14/2006 11:51 PM | Thad
Gravatar

# re: Fun with Dates (Date Conversion examples)

i am facing a problem i want to get the previous month for a given date.
how can i get it
4/16/2006 12:00 PM | vishnu
Gravatar

# re: Fun with Dates (Date Conversion examples)

i am facing a problem i want to retrieve the previous month for a given date
and to get a monthly end balance for that month

how can i get it in DB2
its very urgent i have been stucked for 4 days
please mail me the possible solution
in vishnunarayan1983@yahoo.co.in
or cal me at (india)09945100587
4/16/2006 12:06 PM | vishnu
Gravatar

# re: Fun with Dates (Date Conversion examples)

no comments
12/18/2006 6:24 AM | rajesh
Gravatar

# I'm new here..

Hello, i love weblogs.sqlteam.com! Let me in, please :)
12/18/2006 1:35 PM | Lusidvicel
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET