Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 how to create data

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
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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.aspx

If you just want to get the year,
SELECT YEAR(DateColumn) FROM YourTable
Go to Top of Page

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,
Go to Top of Page

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
Go to Top of Page

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 is

1) October 5th 2013
2) May 5 2013
3) May 13 2010

The 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 DMY

INSERT 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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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


Go to Top of Page

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 2
Column, parameter, or variable #5: Cannot find data type date
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

Mohamed Faisal
Yak Posting Veteran

51 Posts

Posted - 2013-08-04 : 06:18:06
I am using MS SQL Server 2008
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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?
Go to Top of Page

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.
Go to Top of Page

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)
Go to Top of Page
   

- Advertisement -