Date Format - SQL Server Forums

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

Date Format

Author

Topic

1sqlover
Yak Posting Veteran

56 Posts

Posted - 2006-04-28 : 15:01:04

I have set up a SQL DB that contains the following fields: RecDate, RecTime, RecLogDT as separate fields.

RecDate |RecTime |RecLogDT
2006-04-27 00:00:00.000 |2006-01-01 07:21:09.000 |2006-04-27 07:21:09.000
2006-04-27 00:00:00.000 |2006-01-01 07:23:11.000 |2006-04-27 07:23:11.000

How can I get just the date in RecDate, and just the time in RecTime?

All fields use the date/time (8) format.

Thanks

epoh

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-28 : 15:13:05

You need to modify your table so that your date and time aren't in two separate columns. CONVERT function with style 101-107, or 110-112 will get you just the date. CONVERT function with style 108 or 114 will get you just the time. Check out CONVERT in SQL Server Books for details. Here's an example:

SELECT CONVERT(varchar(10), GETDATE(), 110)

Tara Kizer
aka tduggan

Kristen
Test

22859 Posts

Posted - 2006-04-28 : 15:24:07

SQL Server only supports a Date/Time datatype.

If you just store a Date it will be recorded as "midnight" - i.e. time will be 0:00:00.000

Nonetheless you should store the Date in a DATETIME datatype (because you will be able to compare/sort dates chronologically, do date manipulations like adding one month to 31-Jan-2006 and getting 28-Feb-2006 [rather than 31st Feb!], and so on).

You can format dates in SQL Server, but basically that will mean that they are converted to char Strings, and then your application is going to receive them as char String, so its not going to be able to do anything useful with them.

However, ship them to your application as DATETIME native DataType and your application will be able to do whatever cool things you build into your application, or whatever the native language of your application supports.

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-04-28 : 15:39:17

See:

http://weblogs.sqlteam.com/jeffs/archive/2004/12/02/2954.aspx

for some ideas about adding a constraint to be sure that a time is not stored with your dates. You could also use a trigger to strip off any times as well.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-28 : 18:39:23

By convention, a time only column is usually stored in SQL Server as the time on 1900-01-01. The reason for this is that you can add your date only and time only columns together to get the original date and time. The code below shows this.

You could consider not having the date only and time only columns and just store the original datetime. You could use a view with the transformed data below for when you need them.


select
	*,
	DATE_AND_TIME = DATE_ONLY+TIME_ONLY
from
(
select
	DATE_ONLY = dateadd(dd,datediff(dd,0,DT),0),
	TIME_ONLY = DT-dateadd(dd,datediff(dd,0,DT),0)
from
(
select DT = convert(datetime,'2006-04-27 07:23:11.247')
union all
select DT = convert(datetime,'2006-04-27 07:21:09.333')
) a
) aa


Results:
DATE_ONLY               TIME_ONLY               DATE_AND_TIME 
----------------------- ----------------------- -----------------------
2006-04-27 00:00:00.000 1900-01-01 07:23:11.247 2006-04-27 07:23:11.247
2006-04-27 00:00:00.000 1900-01-01 07:21:09.333 2006-04-27 07:21:09.333

(2 row(s) affected)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources