How to change the default Dateformat for Database

Hi,

I want to change the default date format from 'mdy' to 'dmy'. I can do this for session by using

SET DATEFORMAT 'dmy'

but I want to this permanent on Database level (preferred) or SQL instance level how can I do this?

Thanks in advance

Try the link below for the correct SQL Server DateTime guide.

http://www.karaszi.com/SQLServer/info_datetime.asp

sql

How to change MM/DD/YYYY format to DD/MM/YYYY format by using SQL query.

I have a database table named EMP and I have a column named
DOB(datetime) .I want to retrieve just the MM/DD/YYYY part by using
the CONVERT command.And also I need to change the MM/DD/YYYY to DD/MM/
YYYY format by using the CONVERT command by SQL query.What is the
solution ?
Thank you.
Amritendu Paul
Hello,
For mm/dd/yyyy
Select CONVERT(CHAR(10),columns_name,101) from Table_name
For DD/MM/YY
Select CONVERT(CHAR(10),columns_name,103) from Table_name
Thanks
Hari
<amripaul@.gmail.com> wrote in message
news:1181557806.349855.6210@.x35g2000prf.googlegrou ps.com...
>I have a database table named EMP and I have a column named
> DOB(datetime) .I want to retrieve just the MM/DD/YYYY part by using
> the CONVERT command.And also I need to change the MM/DD/YYYY to DD/MM/
> YYYY format by using the CONVERT command by SQL query.What is the
> solution ?
> Thank you.
> Amritendu Paul
>
|||Thanx for your help.

How to change display format of my meaure?

Hi,

Is it possile to definea custom format for the 'display format' property of a measure so that the results are shown in Thousands or Millions or Billions e.g

100,000 can be shown either as 100T
OR
1,000,000 can beshown as 10M
OR
10,000,000 1B

Sorry if I got my numbers wrong but I hope you get the idea?

Looking forward to your help. ThanksLook, you can write code or a function using conversion and text manipulation functions to display the value however you want, but a general principle of database application design is that data formatting is the job of the interface, not the database server. I strongly suggest that you use your interface or reporting tool to format your results (and Query Analyzer is not a user interface!).

How to change default format

By default reports are in HTML format.Can we change the default format to EXCEL

There is no way to change the default rendering format.

Incidently, how would you see this working? Would you expect report links from report manager to download as Excel files? Accessing reports via URL you can specify the rendering format. So why do you need to do this?

How to change date formats in stored procedure

I need help on how to change the date format in a stored procedure. I am using the GetDate() function but need to convert it to short date format.
thanks
mikeLook up CAST AND CONVERT in Books Online. But be aware that this changes the datatype to a string, and should be used for output formatting only. And it is preferable to let your interface or reporting tool handle formatting of output.
Why do you think you need to convert it to short date? Are you trying to truncate the value?|||I am inserting a date value into a table and I dont want the timestamp portion included.|||Thanks! I figured it out using the convert function|||A very similar question was answered yesterday.|||Heck, cascred, this is one of those questions that gets asked every WEEK.

musicmikem, this is a more efficient method of truncating a datatime value, if less intuitive: dateadd(d, datediff(d, 0, [YourDate]), 0)|||Weekly? Hell sometimes it's hourly|||Weekly? Hell sometimes it's hourly Well, it is ASKED hourly, but just wanted to truncate it to daily or weekly for my post.|||Well, it is ASKED hourly, but just wanted to truncate it to daily or weekly for my post.

You make things so complicated. Why didn't you just say that today the question will be asked at:

create table Numlist (num int identity(1,1) not null primary key)
go
insert Numlist default values
while scope_identity() < 24 insert numlist default values
go
select dateadd(hh, num, '10/4/2005') from numlist
go
drop table numlist

Bill|||Because as any good DBA knows, that method requires a Brain Scan instead of a Clock Seek.|||I'm actually in favor of the simpler:SELECT DateAdd(hour, o0 + o1 * 8, dateadd(d, datediff(d, 0, GetDate()), 0))
FROM (SELECT 0 AS o1 UNION SELECT 1 UNION SELECT 2) AS a
CROSS JOIN (SELECT 0 AS o0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7) AS bBonus points for the first person to describe what bit of deviance led to my choices of values (pre-"Release V" users have an advantage here).

-PatP|||Bonus points for the first person to describe what bit of deviance led to my choices of values (pre-"Release V" users have an advantage here).
-PatP

I like it. I have never seen this approach before.

You used a base 8 system instead of base 10 since 8*3 = 24. Nifty.

Bill|||You used a base 8 system instead of base 10 since 8*3 = 24. Nifty.Gold star!

Old Unix machines (especially the DEC ones) used to do nearly everything in octal. Three full octets (00-27 octal is 0-23 decimal) will exactly hold all of the hours in a day.

-PatP|||Old people. Sheesh. Next you're going to ask if we want to see your hernia scar?|||Old people. Sheesh. Next you're going to ask if we want to see your hernia scar?

Hey. Be careful what you suggest. Things weren't pretty in the days before a relational DBMS came along. We used to do this stuff in COBOL ... without SQL! There are some scars, but not from hernias.|||try dis one.
select convert(varchar,datefield,101) from tablename

how to change date format in a select statement

when i use this command in a aspx file

"SELECT DISTINCT Format$([dbo.classgiven.classdate], 'mm/yyyy') AS monthyear,{.......................

'Format$' is not a recognized function name.

so how do i change date from mm/dd/yyyy to mm/yyyy

Check out the CAST and CONVERT functions in SQL BOL. They have a listing of all the possible combinations of formatting you can do for datetime values.|||

Hi~

Try this:

SELECTRIGHT(CONVERT(VARCHAR(10), Column_Name, 103), 7)AS [MM/YYYY]from Table_Name

Hope it helps.

How to carry forward inventory balances

I have data stored in a table in the following format.
Product# Month Year Inventory Balance
For example the inventory balances for a Product A exists in the
table as follows:
Month Qty on Hand
======= ===========
001.2004 120
004.2004 235
006.2004 89
011.2004 42
003.2005 980
======= ===========
Now if the user is trying to report all inventory balances as of
001.2005 even though I do not have a value in the cube for product A I
expect the system to go and pick the last know stock on hand which is
is 42 the known stock on hand qty in 011.2004
Please advice how I can implement these kind of query to solve this
problem. Bottom line what I expect is for a given product A if there is
data in periods 1, 5 and 9 the system carry forward the balance in period
1 to periods 2,3,4 similarly the balance in period 5 must be carried
over to periods 6,7 & 8 similarly the balance in period 9 must be carried
forward to period 10, 11 and 12
Please help me coding the query SQL or the algorithm to arrive at this
kind of reporting.
Thanks
KarenTry,
use northwind
go
create table t (
colA datetime,
quantity int
)
go
insert into t values('20040101', 120)
insert into t values('20040401', 235)
insert into t values('20040601', 89)
insert into t values('20040701', 42)
insert into t values('20050301', 980)
go
select top 12
identity(int, 1, 1) as number
into
number
from
sysobjects as a
go
create view my_view
as
select
*
from
(
select
ltrim(col_year) + right('00' + ltrim(number.number), 2) + '01'
from
(
select distinct
year(colA)
from
t
) as a(col_year)
cross join
number
) as ym(col_date)
left join
t
on t.colA = ym.col_date
go
select
a.col_date,
coalesce(a.quantity, b.quantity)
from
my_view as a
left join
my_view as b
on a.quantity is null and b.col_date = (select max(c.colA) from t as c
where c.colA < a.col_date and c.quantity is not null)
go
drop view my_view
go
drop table number, t
go
AMB
"Karen Middleton" wrote:

> I have data stored in a table in the following format.
> Product# Month Year Inventory Balance
>
> For example the inventory balances for a Product A exists in the
> table as follows:
>
> Month Qty on Hand
> ======= ===========
> 001.2004 120
> 004.2004 235
> 006.2004 89
> 011.2004 42
> 003.2005 980
> ======= ===========
>
> Now if the user is trying to report all inventory balances as of
> 001.2005 even though I do not have a value in the cube for product A I
> expect the system to go and pick the last know stock on hand which is
> is 42 the known stock on hand qty in 011.2004
> Please advice how I can implement these kind of query to solve this
> problem. Bottom line what I expect is for a given product A if there is
> data in periods 1, 5 and 9 the system carry forward the balance in period
> 1 to periods 2,3,4 similarly the balance in period 5 must be carried
> over to periods 6,7 & 8 similarly the balance in period 9 must be carried
> forward to period 10, 11 and 12
>
> Please help me coding the query SQL or the algorithm to arrive at this
> kind of reporting.
>
> Thanks
> Karen
>

How to capture the only "time" into the database

I've a textbox that displays the current time in this format "hh:mm:ss tt" but when it is save into the database it'll display the date and time together. So how do I save only the time into the database? My codes is as shown below:

txtTime.Text = DateTime.Now.ToLongTimeString()

Dim

parameterDateAs SqlParameter =New SqlParameter("@.Date_5",SqlDbType.DateTime)

parameterDate.Value = txtDate.Text

objCommand.Parameters.Add(parameterDate)

I've tried using Format() but it still get the same results. Can someone help me out? Thanks!

The SQL Datetime datatype will always store a date portion. as effectively all you are doing is storing a time difference since an initial base date/time.

Perhaps change your SQL datatype to varchar and store the time as a string, or else use a constant date along with your time value (IE 1/1/1900).

Dave

How to Bulk Insert string data into a money colum?

How to Bulk Insert string data into a money colum?

Format files work great to import string data into a char column.

But I cannot convert a char column to either a money or numeric data type.

I get datatype errors when bulk inserting string data into a money column.

The string data is 22 characters long, no decimal point, trailing negative sign.

Here is an example: 000000000000007898384-

My goal: Bulk Insert the above sample string into a money column.

Often, for situations like this, it is useful to first import into a 'staging' table (same datatypes as import data),

AND then execute 'clean-up' routines

BEFORE moving the data into the production tables.

This allows correcting alot of data anomolies that cannot be handled with a format file.

|||

Thanks, Arnie, for the quick response. What clean-up routines did you have in mind?

Using Enterprise Manager to change the destination column's data type from "char" to "money" or "int" won't work.

Are you talking about concatenating a decimal point to the end of the source string, in hopes that the decimal point will help me get past the conversion issue?

|||

John,

Something like this could work for your situation:

Code Snippet

DECLARE
@.MyString varchar(25),
@.MyMoney money

SET @.MyString = '000000000000007898384-'

IF right( @.MyString, 1 ) = '-'
SET @.MyMoney = cast( ( '-' + left( @.MyString, ( len( @.MyString ) - 1 ))) AS money )
ELSE
SET @.MyMoney = cast( @.MyString AS money )

SELECT @.MyMoney

-7898384.0000

|||

So, it looks like you're putting the negative sign at the front as needed and leaving the positive numbers alone, before casting the result as money. This is fine.

My SQL table has 23 columns and millions of rows.

Column 10 is char, 22 characters wide. Negative signs are trailing.

How would I apply the code to all the rows of column 10 in that table?

|||

You would build a CASE structure (something like this):

DECLARE @.MyTable table
( RowID int IDENTITY,
Column10 varchar(50)
)

INSERT INTO @.MyTable VALUES ( '000000000000007898384-' )
INSERT INTO @.MyTable VALUES ( '000000000000007898385' )

SELECT
RowID,
MyMoney = cast( stuff( CASE
WHEN right( Column10, 1 ) = '-'
THEN ( '-' + left( Column10, ( len( Column10 ) - 1 )))
ELSE Column10
END
, len( CASE
WHEN right( Column10, 1 ) = '-'
THEN ( '-' + left( Column10, ( len( Column10 ) - 1 )))
ELSE Column10
END ) -1
, 0, '.' ) AS money )
FROM @.MyTable

RowID MyMoney
--
1 -78983.8400
2 78983.8500

For illustration purposes, I've also added a decimal point two places from the right. If you don't need that, then remove the STUFF() function. (I thought that just 'might' come up next...)

You 'should' be able to use a query like this to INSERT the staging table data into your production table.

|||

Ok, Arnie, I'll give it a shot. Thanks again.

John

|||

My brain wasn't operating on all cylinders this morning.

You can do without the STUFF() function -just divide by 100 if you need the partial money, remove the [ / 100 ] if you have whole money.

Code Snippet

SELECT
RowID,
MyMoney = cast( CASE
WHEN right( Column10, 1 ) = '-'
THEN ( '-' + left( Column10, ( len( Column10 ) - 1 )))
ELSE Column10
END AS money ) / 100

FROM @.MyTable

|||This is great, Arnie, thanks.