CAN we?
-
I have a table(dates) with one column(Todate), I used to store date.The format of the date should be 'DD-MM-YYYY'
ex:
data stored in the table in the following format
Todate
-----------
01-02-1988
03-06-1980
select * from dates
out put is:
Todate
-----------
01-02-1988
03-06-1980
can we do?
-
raghavender.2369 (10/4/2012)
I have a table(dates) with one column(Todate), I used to store date.The format of the date should be 'DD-MM-YYYY'ex:
data stored in the table in the following format
Todate
-----------
01-02-1988
03-06-1980
select * from dates
out put is:
Todate
-----------
01-02-1988
03-06-1980
can we do?
I don't really get your question!
-
raghavender.2369 (10/4/2012)
I have a table(dates) with one column(Todate), I used to store date.The format of the date should be 'DD-MM-YYYY'ex:
data stored in the table in the following format
Todate
-----------
01-02-1988
03-06-1980
select * from dates
out put is:
Todate
-----------
01-02-1988
03-06-1980
can we do?
The fact that you mention that your date has a format leads me to think you are storing your data in a varchar column. You need to use the datetime datatype. It will make your life so much easier is so many ways.
Other than that I second that your question doesn't make much sense as it is presented.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
by default date is stored in table 'yyyy-mm-dd' format , but I want to store date in 'dd-mm-yyyy' format, am i clear?
-
raghavender.2369 (10/4/2012)
by default date is stored in table 'yyyy-mm-dd' format , but I want to store date in 'dd-mm-yyyy' format, am i clear?Yes....don't use varchar. Use datetime. Then there is no "format". The formatting is done when you pull the data out.
Or are you using a datetime field and you see it presented as yyyy-mm-dd HH:MM:ss.mmm? By default SSMS will always display in this order unless you format your output.
If that is the issue then I would suggest that formatting should be done in the front end. But if you have to use sql for formatting your date then you can take a look at CONVERT. http://msdn.microsoft.com/en-us/library/ms187928.aspx
You can have it display in just about any format you can think of. If there isn't one style that works for exactly what you want you may have to resort to DATEPART and DATENAME to get the exact format you want.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
create table dates(todate date)
insert into dates values('01-02-1988') (or) insert into dates values('1988-02-01')
select * from dates
output:
01-02-1988
I want the output in the above mentioned format,
-
Thanq for your suggestion, but there is a requirement to store date in 'dd-mm-yyyy' in this format only(use only date datatype, dont use varchar), i googled about it , someone suggested that ' change the database language to britesh english
I did that language setup but no use 🙁
-
raghavender.2369 (10/4/2012)
Thanq for your suggestion, but there is a requirement to store date in 'dd-mm-yyyy' in this format only(use only date datatype, dont use varchar), i googled about it , someone suggested that ' change the database language to britesh englishDate datatype does NOT store the format. It is actually stored internally as a numeric datatype. You are talking about presentation which has nothing to do with how the information is stored internally.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
raghavender.2369 (10/4/2012)
create table dates(todate date)insert into dates values('01-02-1988') (or) insert into dates values('1988-02-01')
select * from dates
output:
01-02-1988
I want the output in the above mentioned format,
I assume you want to store date format like in Vietnam, that is the case your database collation needs to be configured for that region, and not English.
-
Sean Lange (10/4/2012)
raghavender.2369 (10/4/2012)
Thanq for your suggestion, but there is a requirement to store date in 'dd-mm-yyyy' in this format only(use only date datatype, dont use varchar), i googled about it , someone suggested that ' change the database language to britesh englishDate datatype does NOT store the format. It is actually stored internally as a numeric datatype. You are talking about presentation which has nothing to do with how the information is stored internally.
Like Sean said,
use this, but if that is not an option you can take, then you are going to have to deal at database colation level or using different datatype like varchar.
declare @yourtableName table (yourDate datetime)insert into @yourtableName(yourDate)
select GETDATE()
--retrieve
select convert(varchar(20),yourDate,103) as MyDate
from @yourtableName
-
I agree with you, we can display the result in any desired format using convert or someother functions,
my friend said it is possible to store date in this way '01-02-1988' .
really is it possible or not?
he asked me to store date in DD-MM-YYYY format, can we do?
-
raghavender.2369 (10/4/2012)
I agree with you, we can display the result in any desired format using convert or someother functions,my friend said it is possible to store date in this way '01-02-1988' .
really is it possible or not?
he asked me to store date in DD-MM-YYYY format, can we do?
again...NOT if your database is in SQL_Latin1_General_CP1_CI_AS
-
haiao2000 (10/4/2012)
raghavender.2369 (10/4/2012)
I agree with you, we can display the result in any desired format using convert or someother functions,my friend said it is possible to store date in this way '01-02-1988' .
really is it possible or not?
he asked me to store date in DD-MM-YYYY format, can we do?
again...NOT if your database is in SQL_Latin1_General_CP1_CI_AS
The collation does not matter as far as what is stored. Datetime datatypes are stored as numerics no matter what collation you have established. Collation can define how the datetime datatype is presented.
To answer your question, NO. You can't store any kind of formatting in a datetime datatype. If you MUST store this in that format you can't use the correct datatype.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Sorry... should not use convert to retrive data from date column,
the date should be appear in the table in 01-02-1988 only, not in 1988-02-01.
-
haiao2000 (10/4/2012)
raghavender.2369 (10/4/2012)
I agree with you, we can display the result in any desired format using convert or someother functions,my friend said it is possible to store date in this way '01-02-1988' .
really is it possible or not?
he asked me to store date in DD-MM-YYYY format, can we do?
again...NOT if your database is in SQL_Latin1_General_CP1_CI_AS
Ah ok.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply