SQL SERVER DATE/TIME FORMATS

metta0_3
metta0_3 used Ask the Experts™
on
Hi,

I'm trying to create a table using SQL Server. Along the lines of

CREATE TABLE Data
(
      Start_Date date NULL,
      Start_Time time(7) NULL,
                      Cost money NULL,
);

I want to be able to ensure that only dates in the format of DD/MM/YYYY, and times in the format of hh:mm:ss ( like that as in the UK) are placed in the above derived columns. Can anybody provide me with the solution, check constraint... etc.

Also the money in UK pounds.

Thanks

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Adam MenkesC# ASP.NET Developer
Top Expert 2010

Commented:
You don't really store data in a format. For example, 1/1/1900 is stored as 0. You format it for output.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
In a Date datatype column, internally it would be stored in the format depending upon your Regional setting and Language setting configured in SQL Server. But you can always use CONVERT(varchar(10),  Start_Date, 103) to make sure that date format is retrieved in the format DD/MM/YYYY

Author

Commented:
I just want to store date and time in a format of dd/mm/yyyy and hh:mm:ss. Thats it, i don't need anything else at all. If you know the answer then great if not then don't worry.

regards,
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples


>>only dates in the format of DD/MM/YYYY, and times in the format of hh:mm:ss ( like that as in the UK) are placed in the above derived columns. Can anybody provide me with the solution, check constraint<<
You don't do that at the database level. You do this in your application. Also, how would SQL know if 01/03/2010 is January 3 or March 1?
SQL will interpret the dates based on the SET DATEFORMAT option.
http://msdn.microsoft.com/en-us/library/ms189491.aspx 
But, there's no constraint to check for the correct order. Instead, you create for example a calendar object in your application and let the user select the correct one.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
You can store both date and time as a single column and use CONVERT as mentioned above to fetch the required values.

Author

Commented:
Sorry rrjegan17 that wasn't directed at you. I just get fed up with some of the strange comments people leave that do not reflect anything I am trying to do. I know with Oracle you can set the checkconstraint format. Thats all.

Something like:

CREATE TABLE Data
(
      Start_Date date FORMAT dd/mm/yyyy NULL,
      Start_Time time(7) FORMAT hh:mm:ss NULL,
);

Just so the data will always be in this correct format. Otherwise by default with go to SQL Servers default in the american format

Author

Commented:
Anyway thanks for the advice ralmada.

Author

Commented:
Thanks
Adam MenkesC# ASP.NET Developer
Top Expert 2010

Commented:
Data stored internally is not how it is presented. For example, the only way to store money to a specific (UK) format, would be as a string (char/varchar). Storing 0, 1, 2, etc is all SQL cares about. You format it for output, so that 1.23 would appear as $1.23 or 1,23 or whatever your regional settings require. But internally, it is not stored with formatting.

Take a look at the example (for dates).

set nocount on
declare @date date
set @date = GETDATE()
-- or
-- set @date = DATEADD(d, 0, 0) -- 1/1/1900

select @date

SELECT convert(varchar, @date, 100) 
SELECT convert(varchar, @date, 101) 
SELECT convert(varchar, @date, 102) 
SELECT convert(varchar, @date, 103) 
SELECT convert(varchar, @date, 104) 
SELECT convert(varchar, @date, 105) 
SELECT convert(varchar, @date, 106) 
SELECT convert(varchar, @date, 107) 
--SELECT convert(varchar, @date, 108) 
SELECT convert(varchar, @date, 109) 
SELECT convert(varchar, @date, 110) 
SELECT convert(varchar, @date, 111) 
SELECT convert(varchar, @date, 112) 
SELECT convert(varchar, @date, 113) 
--SELECT convert(varchar, @date, 114) 
SELECT convert(varchar, @date, 120) 
SELECT convert(varchar, @date, 121) 
SELECT convert(varchar, @date, 126)

Open in new window

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
No, you can't do it this way..

CREATE TABLE Data
(
      Start_Date date FORMAT dd/mm/yyyy NULL,
      Start_Time time(7) FORMAT hh:mm:ss NULL,
);

By default SQL Server stores date value in mm/dd/yyyy and it varies depending upon the OS Regional settings and DATEFORMAT option set at SQL Server. And you can ensure that you always get DD/MM/YYYY option using CONVERT.
Adam MenkesC# ASP.NET Developer
Top Expert 2010

Commented:
I realize the question is closed, but I wanted to address this:
>> I just get fed up with some of the strange comments people leave that do not reflect anything I am trying to do.<<

When you ask a question, be clear. It may have been crystal clear in your mind, but not in your question. Your question was how to store data in a particular format. Without knowing your skill level or exactly what you are after, you will get various responses to the question posed.

Data is not STORED in a particular format. A bit field can be 1 or 0, and in SSMS show as true/false and if you want it to appear as Y/N you format it.

Regional settings can control validation and how it appears (so that 6/30/10 is valid in some countries and not in others), but internally it is stored as a number.

So, when you talk about storing in a particular format, there is a reason you will get responses to your question that you felt were "off the mark", when in fact, addressed the actual question as posed.

From http://www.sqlmag.com/article/tsql3/datetime-calculations-part-1.aspx

The storage format that SQL Server uses internally to represent datetime values is two 4-byte integers for DATETIME and two 2-byte integers for SMALLDATETIME. One integer is an offset in terms of days from the base date January 1, 1900, and the other is an offset from midnight (in terms of clockticks for DATETIME—in which one clocktick is 31/3 milliseconds—and in terms of minutes for SMALLDATETIME).

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial