Author |
Topic |
Mohamed Faisal
Yak Posting Veteran
51 Posts |
Posted - 2013-08-04 : 04:59:34
|
Hi, I need help how to i create table if i want DD-MM-YYYY format and and another table with the YYYY. And also how could I use them to subtract both tables to fine a value. Thanks allot. |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-08-04 : 05:32:08
|
If you are using dates, don't bother about the presentation format. Internally, a date is nothing more than a serial number starting with the day of January 1st 1900. Why are you bothered about the presentation format? Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
 |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-08-04 : 05:33:18
|
quote: Originally posted by Mohamed Faisal Hi, I need help how to i create table if i want DD-MM-YYYY format and and another table with the YYYY. And also how could I use them to subtract both tables to fine a value. Thanks allot.
Did you mean two different tables, or two columns in one table?If it is one table, I would recommend having just one column and make that a column of data type DATE (or DATETIME if you are on SQL 2005 or earlier). Once you do that, you can get the DD-MM-YYYY format and the YYYY format as and when required. |
 |
|
Mohamed Faisal
Yak Posting Veteran
51 Posts |
Posted - 2013-08-04 : 05:35:30
|
cause whenever i insert the date in column, it always shows the wrong date. So how to insert a data when column is declared as datetime |
 |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-08-04 : 05:38:07
|
quote: Originally posted by Mohamed Faisal cause whenever i insert the date in column, it always shows the wrong date. So how to insert a data when column is declared as datetime
When you insert, use the YYYYMMDD format to insert the date. For example, to insert today's date (August 4, 2013), use this:INSERT INTO YourTable (DateColumn) VALUES ('20130804') You can/should add additional columns that are in your table in the columns list and in the values list.Once you have done that, if you want to select and get the data in the dd-mm-yyyy format, simply do this:SELECT CONVERT(CHAR(10),DateColumn,105) FROM YourTable When you do that you are converting the DATE datatype to a string datatype using a specific style. All the styles are listed here: http://technet.microsoft.com/en-us/library/ms187928.aspxIf you just want to get the year,SELECT YEAR(DateColumn) FROM YourTable |
 |
|
Mohamed Faisal
Yak Posting Veteran
51 Posts |
Posted - 2013-08-04 : 05:38:59
|
It this the correct syntax:[DateOfBirth] [date] NOT NULL, [YearEnrolled] [year] NOT NULL, |
 |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-08-04 : 05:43:16
|
quote: Originally posted by Mohamed Faisal It this the correct syntax:[DateOfBirth] [date] NOT NULL, [YearEnrolled] [year] NOT NULL,
Ah, you are storing two different pieces of information. Yes, in that case, you will need two columns. It should be[DateOfBirth] [date] NOT NULL, [YearEnrolled] [INT] NOT NULL, There is no data type called year, so you should use an int for that (assuming you only want to store the year and not the actual date of enrollment |
 |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-08-04 : 05:43:58
|
If you are using a slash date format when inserting dates, how do you expect SQL Server to tell wether 10/5/13 is1) October 5th 20132) May 5 20133) May 13 2010The only way to make sure the date is stored the way you expect it too, is to either use ISO dateformat or use the SET DATEFORMAT setting.And beware, using hyphens when using ISO (2013-08-04) will give you wron result if using german language. Remove the hyphens (20130804) and you are safe.Or use the SET DATEFORMAT setting. However, this may force a recompilation of your code everytime you execute it.SET DATEFORMAT DMYINSERT dbo.Table1 VALUES('10/5/13')Now SQL Server will trust you and insert the date as May 5th 2013 and nothing else. Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
 |
|
Mohamed Faisal
Yak Posting Veteran
51 Posts |
Posted - 2013-08-04 : 05:45:07
|
then how can i subtract the two values to find the age |
 |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-08-04 : 05:46:11
|
quote: Originally posted by Mohamed Faisal It this the correct syntax:[DateOfBirth] [date] NOT NULL, [YearEnrolled] [year] NOT NULL,
YearEnrolled] [year] NOT NULL, There is no datatype YEAR in SQL Server. Use a numeric representative such as SMALLINT.The year can be 0001-9999 in SQL Server since version 2008. The datatype SMALLINT will do and also save you 2 bytes per row. Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
 |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-08-04 : 05:47:54
|
quote: Originally posted by Mohamed Faisal then how can i subtract the two values to find the age
If you have two dates, you can find the age using a query like in the example below.DECLARE @BirthDate DATETIME;SET @Birthdate='20110225'SELECT(CAST(CONVERT(CHAR(8), GETDATE(), 112) AS INT)- CAST(CONVERT(CHAR(8), @BirthDate, 112) AS INT))/10000 |
 |
|
Mohamed Faisal
Yak Posting Veteran
51 Posts |
Posted - 2013-08-04 : 05:50:56
|
when i type in [DateOfBirth] [date] NOT NULL, I am getting the following error: Msg 2715, Level 16, State 7, Line 2Column, parameter, or variable #5: Cannot find data type date |
 |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-08-04 : 06:15:38
|
Use datetime instead of date. You are on SQL 2005 or an earlier version of SQL that does not support the date data type |
 |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-08-04 : 06:16:07
|
Which version of SQL Server are you using? DATE comes with SQL Server 2008 and later. Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
 |
|
Mohamed Faisal
Yak Posting Veteran
51 Posts |
Posted - 2013-08-04 : 06:18:06
|
I am using MS SQL Server 2008 |
 |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-08-04 : 06:37:29
|
Post the entire create statement. If you are on SQL 2008, DATE is a valid data type, so you should not have gotten that error. So the source of the error is something else; can't tell what it is without seeing the entire query. |
 |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-08-04 : 06:58:19
|
Which compatibility level do you have for the current database?Even if the server is 2008, you may have compatibility level 90 (which means SQL Server 2005) and that's why the parser doesn't allow DATE. Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
 |
|
Mohamed Faisal
Yak Posting Veteran
51 Posts |
Posted - 2013-08-04 : 07:14:00
|
i am using sql server 9.0.1399, how do i check for the compatibility level? |
 |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-08-04 : 07:40:54
|
quote: Originally posted by Mohamed Faisal i am using sql server 9.0.1399, how do i check for the compatibility level?
SQL Server 9.0xxxx is SQL 2005. SQL 2005 does not support DATE data type. So use DATETIME instead of DATE. |
 |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-08-04 : 07:48:03
|
quote: Originally posted by SwePeso Which compatibility level do you have for the current database?Even if the server is 2008, you may have compatibility level 90 (which means SQL Server 2005) and that's why the parser doesn't allow DATE. Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
You can have a database of compatibility level 90 on a SQL 2008 (or later) server and still use DATE data type. Setting compatibility level to a level below the server's version preserves the behavior that existed in the older version rather than prevent newer features from being available (in most cases) |
 |
|
|