How to return time & number format that has set in the regional setting using stored proce

How to return time & number format that has set in the regional setting using stored procedure.

Following is my sp for getting current date format from Sql Server.

if exists

(select*fromsysobjectswhereid =object_id(N'[HSP_GetDateFormat]')andOBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure[HSP_GetDateFormat]

create procedure

HSP_GetDateFormat

(

@.strDateFormat nvarchar(64) out,

@.iErr

intout

)

as

begin

set nocount on

set@.strDateFormat = (selectdateformat frommaster..syslanguageswherelangid = (selectvaluefrommaster..sysconfigureswherecomment = 'default language'))

set@.iErr = @.@.Error

set nocount off

end

Now, I want to know what would I write if I want to get currenttime &numberformat from Sql Server.

Hi,

From your description, it seems that you want to get the current date time in stored procedure, right?

TSQL has provided several built-in functions which is used for datetime and datetiem calculating. You can get the current date and time by using GetDate() function, also, you can use such functions like DATEADD,DATEDIFF to calculate the date and time. See the following codes:

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE() ))

Besides, if you want to output the value, just create a datetime typed variable and set it as OUTPUT, and then create a parameter in .NET side, which shares the same type, set the direction as OUTPUT, then you can receive the datetime from your stored procedure. As for the number type, you can use INT,FLOAT,DOUBLE and etc in your stored procedure.

Thanks.

How to return current hour of week?

One week have 168 hours.
How do you create SQl statement that return hour value for specific timestamp??
eg.
If week starts on system from Monday.
Monday 01:00 is hour 1
Tuesday 01:00 is hour 25
Sunday 23:00 is hour 167
etc.
Ideas??This should be a good start:

DECLARE @.startDate SMALLDATETIME
DECLARE @.endDate SMALLDATETIME

--Set Test value for Thursday Feb 22nd
SET @.endDate = ('02/22/2004 15:21:20')
--Set the start date to first day of the week
SET @.startDate = DATEADD(Day, -(DATEPART(WeekDay, @.endDate) - 1), @.endDate)
--Remove the time component of the start date
SET @.startDate = CONVERT(VARCHAR(10), @.startDate, 101)
--Count the number of hours from start of week (Mon) to @.endDate
SELECT DATEDIFF(Hour, @.startDate, @.endDate)

This could be converted to run as part of a select where @.endDate is provided by a column with Date data. If you use this verbatim you will want to test boundary conditions to assure they meet your requirements and I'm sure it could be optimized.

The key is in the setting of @.startDate [DATEADD(Day, -(DATEPART(WeekDay, @.endDate) - 1), @.endDate)].
Evaluation:
DATEPART(WeekDay, @.EndDate) = Day Code for Thursday (4)
(4) - 1 = 3*
-(3) = -3
DATEADD(Day, (-3), @.endDate) = Sets Date To Monday (*determined by the 1 subtracted from the original day code)|||One more try...

(24 * DatePart("dw",'2004-03-8 1:00:00')-1) + {fn Hour('2004-03-8 1:00:00')}

Is it correct?|||You need another set of parenthesis for proper order of operations:

declare @.TimeStamp datetime
set @.TimeStamp = getdate()
select (24 * (DatePart(dw,@.TimeStamp)-1)) + {fn Hour(@.TimeStamp)}|||set datefirst 1
declare @.timestamp datetime
set @.timestamp = getdate()
select (datepart(dd,@.timestamp)-1)*24+datepart(hh,@.timestamp)|||Just an observation that may not affect you, but there aren't always 168 hours in the week. If you observe Daylight Savings Time (http://webexhibits.org/daylightsaving/) then one week has 167 and one has 169 each year. If you need to keep Sidereal (http://tycho.usno.navy.mil/sidereal.html) time, then a different week each year can have a smidgeon more or less than 168.

These don't affect everyone, but they are the basis behind my always using date functions instead of "roll your own" functions like these. I'm not exactly sure how I'd handle this case, but I just wanted to raise the issue before folks go merrily trooping off with a solution that might not always work for them.

-PatP|||declare @.dt datetime
set @.dt = '20040309 1:00'
select DATEDIFF(hh,DATEADD(d,-(DATEPART(w,@.dt)+@.@.DATEFIRST-2)%7,LEFT(@.dt,13)),@.dt)

Hans.|||Just like skinning a cat, there is more than one way to do this and whatever you choose, the result is not very attractive.

How to retrieve timestamp

Can any one please tell me how to retrieve values from timestamp column.
I am getting 1900-01-01 00:02:09.457. It is not storing current timestamp when record is created or modified.
Is there anything I need to set it up.
thanksCan any one please tell me how to retrieve values from timestamp column.
I am getting 1900-01-01 00:02:09.457. It is not storing current timestamp when record is created or modified.

Is there anything I need to set it up.

thanks

I don't believe the timestamp datatype actually contains a Date/Time, but instead, a unique binary number which represents an update to the row.

timestamp is a data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.

If you need a field to hold date/time information, use the datetime or smalldatetime datatypes.|||If you need a field to hold date/time information, use the datetime or smalldatetime datatypes.
=============================================

System is in production now. Even though I create a column with datetime the program will not update the column. So I thought setting up a column which is being automatically updated.

I like to delete some records based on the date last modified.

Thanks|||This is right out of the SQL Server help guide:

timestamp
Is used to indicate the sequence of SQL Server activity on a row, represented as an increasing number in a binary format. As a row is modified in a table, the timestamp is updated with the current database timestamp value obtained from the @.@.DBTS function. timestamp data is not related to the date and time of an insert or change to data. To automatically record times that data modifications take place in a table, use either a datetime or smalldatetime data type to record the events and triggers.

You'll never get actual date/time values from the timestamp datatype. You're going to have to change something if you want to know exactly when a row was last touched.

how to retrieve the largest value from a column?

lets say a column using int as datatype

for eg:

the current method i used to retrieve the largest value is by...

select TOP 1 ...from....order by desc

so for eg:
let say the column contains
10
60
40
25
40

i will only retrieve 60. are there any loophole in the above method?Why not try this:

SELECT MAX(<field>) as MaxValue <from <TABLE> where <CLAUSE>|||Originally posted by Bascy
Why not try this:

SELECT MAX(<field>) as MaxValue <from <TABLE> where <CLAUSE>

Thanks. :)|||TOp n gives u the number of rows.
Max gives u the maximum value in the column|||Originally posted by cyrus
TOp n gives u the number of rows.
Max gives u the maximum value in the column

That is not quite correct...

TOP n gives you the first n rows of the resultset, so TOP 1 is the same as MAX as long as the order is on the same field.|||Originally posted by Bascy
That is not quite correct...

TOP n gives you the first n rows of the resultset, so TOP 1 is the same as MAX as long as the order is on the same field.

i am saying the same thing without order by

How to retrieve name of current database in TSQL query

How can I get the name of the current database within a TSQL query (SQL
2000)? There doesn't seem to be an @.@.variable for it.Use the DB_NAME() function.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:uDJEBSofGHA.2188@.TK2MSFTNGP04.phx.gbl...
> How can I get the name of the current database within a TSQL query (SQL
> 2000)? There doesn't seem to be an @.@.variable for it.
>|||SELECT DB_NAME()
Denis the SQL Menace
http://sqlservercode.blogspot.com/

How to retrieve name of current database in TSQL query

How can I get the name of the current database within a TSQL query (SQL
2000)? There doesn't seem to be an @.@.variable for it.Use the DB_NAME() function.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:uDJEBSofGHA.2188@.TK2MSFTNGP04.phx.gbl...
> How can I get the name of the current database within a TSQL query (SQL
> 2000)? There doesn't seem to be an @.@.variable for it.
>|||SELECT DB_NAME()
Denis the SQL Menace
http://sqlservercode.blogspot.com/

How to retrieve name of current database in TSQL query

How can I get the name of the current database within a TSQL query (SQL
2000)? There doesn't seem to be an @.@.variable for it.Use the DB_NAME() function.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:uDJEBSofGHA.2188@.TK2MSFTNGP04.phx.gbl...
> How can I get the name of the current database within a TSQL query (SQL
> 2000)? There doesn't seem to be an @.@.variable for it.
>|||SELECT DB_NAME()
Denis the SQL Menace
http://sqlservercode.blogspot.com/

How to retrieve current date and time from SQL2005 server.

well i'm using java to connect to SQL 2005 server. i need to retrieve it's current time and date on the sql 2005 server and use it on my remote desktop

thanks.

Hi,

You can use the following query :

SELECT getdate() as myDate

wich will give you somthing like "2006-10-27 11:29:15.373"

Regards