SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


given a date, tell me how many seconds from 1900


given a date, tell me how many seconds from 1900

Author
Message
Tobar
Tobar
SSC-Addicted
SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)

Group: General Forum Members
Points: 465 Visits: 758
Steven, Glad you got some exercise. :-P

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

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". Blink

<><
Livin' down on the cube farm. Left, left, then a right.
Steven Willis
Steven Willis
SSC Eights!
SSC Eights! (837 reputation)SSC Eights! (837 reputation)SSC Eights! (837 reputation)SSC Eights! (837 reputation)SSC Eights! (837 reputation)SSC Eights! (837 reputation)SSC Eights! (837 reputation)SSC Eights! (837 reputation)

Group: General Forum Members
Points: 837 Visits: 1721
Laugh 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.

 
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search