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

given a date, tell me how many seconds from 1900 Expand / Collapse
Author
Message
Posted Thursday, May 09, 2013 4:25 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 9:51 AM
Points: 230, Visits: 692
Steven, Glad you got some exercise.

Let me guess, you turned 46 earlier this year ('2/5/1967').

The funny thing is, this request was me anticipating a requirement based on observed values. Turns out what is needed is already canned within Sql Server, albeit, canned obtusely, and "what where you thinkingly".


<><
Livin' down on the cube farm. Left, left, then a right.
Post #1450994
Posted Thursday, May 09, 2013 9:51 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
I wish I was still 46!

When I saw your question last night I remembered a similar scenario concerning UniData date conversions on which I made a post.

UniData date function.

UniData dates use a boundary of 31 Dec 1967 so I guess that year stuck in my head. UniData dates are based on the number of seconds before or after 31 Dec 1967 so that earlier post would have been pretty close to a solution as it was by just adding two sets of numbers.

What got me going this time though really didn't have much to do with the number of seconds since 1900. Rather, I wanted to see if I could incorporate SET commands within a function. The original UniData function is an inline TVF. So I took a dive to see if there was some method for making SET DATEFORMAT and SET LANGUAGE work in an inline TVF. (Alas, I think the answer is no.) So the script I had to settle for required a stored procedure.

But I learned a new trick in the process using QUOTENAME as a method for executing a SET command in a SELECT statement like this:

SELECT QUOTENAME('SET DATEFORMAT '+@DateFormat,'''')

So perhaps it really is possible to do a SET within a function.

 
Post #1451219
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse