Building a date - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Building a date

Author

Topic

vmon
Yak Posting Veteran

63 Posts

Posted - 2006-05-22 : 12:07:07

How can I build a date. I am trying this but gettting a date of 7/2/1905 for the values 2006, 01, 01

SELECT @txtProductLineUsrFld2, @txtYear, @txtForecast/12, CAST(((@txtYear) + '-' + ('01') + '-' + (@Counter) ) AS DATETIME)

Thanks,
vmon

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-22 : 12:10:23

What datatypes and values are in your variables?
convert(datetime, @txtYear + '01' + @Counter)

should work if the two variables are yyyy and dd and '01' is the month.
It will end up with yyyymmdd

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

vmon
Yak Posting Veteran

63 Posts

Posted - 2006-05-22 : 12:16:17

They are just integers. How would I make them yyyy and dd format?

Thanks for you reply.
vmon

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-22 : 12:33:59

how about
convert(datetime, convert(varchar(4),@txtYear) + '01' + right('00' + convert(varchar(2),@Counter),2))

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-22 : 12:42:11

Or maybe...

declare @year int, @month int, @day int
select @year = 2006, @month = 1, @day = 1
select dateadd(day, @day - 1, dateadd(month, @month - 1, dateadd(year, @year-1900, 0)))

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Kristen
Test

22859 Posts

Posted - 2006-05-22 : 12:45:40

- Dead spooky that!

I expect this is wildly inefficient, but:


DECLARE	@year int, @month int, @day int
SELECT	@year=2006, @month=12, @day=31

SELECT	DATEADD(Day, @day-1, DATEADD(Month, @month-1, DATEADD(Year, @year-1900, 0)))

It will allow invalid dates though - although that might be beneficial - you could add "365 days and 0 months" to a year,

Kristen

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-22 : 13:04:11

You're scaring me, Kristen

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-05-22 : 14:04:22

quote:
Originally posted by Kristen

- Dead spooky that!

I expect this is wildly inefficient, but:

DECLARE @year int, @month int, @day int
SELECT @year=2006, @month=12, @day=31

SELECT DATEADD(Day, @day-1, DATEADD(Month, @month-1, DATEADD(Year, @year-1900, 0)))

It will allow invalid dates though - although that might be beneficial - you could add "365 days and 0 months" to a year,

Kristen

I doubt that your solution is really "wildly inefficient", but this is almost the same with one less function call.


declare @year int, @month int, @day int
select	@year = 2006, @month = 1, @day = 16

select dateadd(day,@day-1,dateadd(month,((@year-1900)*12)+@month-1,0))

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-23 : 03:04:09

Other approaches (Not sure about performance)

declare @year int, @month int, @day int
select @year = 2006, @month = 1, @day = 16
Select cast(cast(@year*10000+@month*100+@day as char(8)) as datetime)
Select cast(left(@year*10000+@month*100+@day ,8) as datetime)

Madhivanan

Failing to plan is Planning to fail

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-23 : 06:33:45

Nice idea Madhivanan. We can get that a tiny bit shorter (the importance of which cannot be underestimated

)...

select cast(str(@year*10000+@month*100+@day) as datetime)

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-23 : 09:27:21

Yes it is

Madhivanan

Failing to plan is Planning to fail

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-05-23 : 11:15:56

This thread wouldn't be complete without a test.

I used the following script to test the runtime of the various methods posted against a table with one million test dates. I ran the tests a number of times, and got consistant results.

As I suspected, the methods with dateadd were faster, and the ones that used character strings were much slower, taking 2.5 to 7 times as long.


-- Load test data
go
drop table #t
set nocount off
go
print 'Load 1 million dates in random order'
select
	top 100 percent
	DT,
	[YEAR]	= year(a.DT),
	[MONTH]	= month(a.DT),
	[DAY]	= day(a.DT)
into #t
from
(
select
	DT=dateadd(dd,aa.NUMBER,'17530101')
from
	dbo.F_TABLE_NUMBER_RANGE(0,999999) AA
) a
order by
	newid()
go
set nocount on
go
declare @start datetime, @end datetime, @count int

select @start = getdate()
select
	@count= count(*)
from
	#t a
where
	a.DT <>
	dateadd(day,a.day-1,dateadd(month,((a.year-1900)*12)+a.month-1,0))

select [Elapsed MS - MVJ] = datediff(ms,@start,getdate())
go
declare @start datetime, @end datetime, @count int

select @start = getdate()
select
	@count= count(*)
from
	#t a
where
	a.DT <>
	DATEADD(Day, a.day-1, DATEADD(Month, a.month-1, DATEADD(Year, a.year-1900, 0)))

select [Elapsed MS - Kristen & Ryan] = datediff(ms,@start,getdate())
go
declare @start datetime, @end datetime, @count int

select @start = getdate()
select
	@count= count(*)
from
	#t a
where
	a.DT <>
	cast(cast(a.year*10000+a.month*100+a.day as char(8)) as datetime)

select [Elapsed MS - madhivanan 1] = datediff(ms,@start,getdate())
go
declare @start datetime, @end datetime, @count int

select @start = getdate()
select
	@count= count(*)
from
	#t a
where
	a.DT <>
	cast(left(a.year*10000+a.month*100+a.day ,8) as datetime)

select [Elapsed MS - madhivanan 2] = datediff(ms,@start,getdate())
go
declare @start datetime, @end datetime, @count int

select @start = getdate()
select
	@count= count(*)
from
	#t a
where
	a.DT <>
	cast(str(a.year*10000+a.month*100+a.day) as datetime)

select [Elapsed MS - Ryan 2] = datediff(ms,@start,getdate())
go

Results:


Load 1 million dates in random order

(1000000 row(s) affected)

Elapsed MS - MVJ 
---------------- 
1076

Elapsed MS - Kristen & Ryan 
--------------------------- 
1220

Elapsed MS - madhivanan 1 
------------------------- 
2703

Elapsed MS - madhivanan 2 
------------------------- 
5050

Elapsed MS - Ryan 2 
------------------- 
7153

CODO ERGO SUM

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-23 : 11:34:42

Well done Michael! No surprises about the results, but good to confirm what we'd probably all have suspected.

You truly are the 'date master'

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-05-23 : 12:17:23

quote:
Originally posted by RyanRandall
...No surprises about the results, but good to confirm what we'd probably all have suspected...

Nothing like actual test with real numbers.

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-24 : 03:27:00

Thanks MVJ for the tests

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-05-24 : 05:13:59

I wonder if I did a test if my result would have the best time?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-05-24 : 06:40:04

quote:
Originally posted by Kristen

I wonder if I did a test if my result would have the best time?

Why not give it a shot?

CODO ERGO SUM

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-24 : 06:44:06

Any difference between

SELECT DATEADD(Day, @day - 1, DATEADD(Month, @month - 1, DATEADD(Year, @year - 1900, 0)))

and this ?

SELECT @day - 1 + DATEADD(Month, @month - 1, DATEADD(Year, @year - 1900, 0))

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-05-24 : 11:41:10

quote:

Originally posted by khtan

Any difference between

SELECT DATEADD(Day, @day - 1, DATEADD(Month, @month - 1, DATEADD(Year, @year - 1900, 0)))

and this ?

SELECT @day - 1 + DATEADD(Month, @month - 1, DATEADD(Year, @year - 1900, 0))

I ran some tests and didn't see any difference in performance between them.

CODO ERGO SUM

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-01-27 : 16:26:50

I decided to beat this long dead horse one last time.

I found an even shorter piece of code to build a date, which is also faster than any of methods that I tested above. It uses only one DATEADD function call, so that is probably why it is faster.


declare @year int, @month int, @day int
select	@year = 2006, @month = 1, @day = 16

select dateadd(month,((@Year-1900)*12)+@Month-1,@Day-1)

I tested with this code to compare it to the fastest method from the tests I posted on 2006/5/23:


drop table #t
set nocount off
go
print 'Load 3 million dates in random order'
create table #t (
DT datetime not null,
[YEAR]	int not null,
[MONTH]	int not null,
[DAY]	int not null
)

insert into  #t
select
	top 100 percent
	DT,
	[YEAR]	= year(a.DT),
	[MONTH]	= month(a.DT),
	[DAY]	= day(a.DT)
from
(
select
	DT=dateadd(dd,aa.NUMBER,'17530101')
from
	dbo.F_TABLE_NUMBER_RANGE(0,2999999) AA
) a
order by
	newid()
go
set nocount on
go
declare @start datetime, @end datetime, @count int

select @start = getdate()
select
	@count= count(*)
from
	#t a
where
	a.DT <>
	dateadd(day,a.day-1,dateadd(month,((a.year-1900)*12)+a.month-1,0))

select [Elapsed MS - MVJ Old] = datediff(ms,@start,getdate())
go
declare @start datetime, @end datetime, @count int

select @start = getdate()
select
	@count= count(*)
from
	#t a
where
	a.DT <>
	dateadd(month,((a.year-1900)*12)+a.month-1,a.day-1)

select [Elapsed MS - MVJ New] = datediff(ms,@start,getdate())
go

It gave small but consistent difference in run time:


Elapsed MS - MVJ Old 
-------------------- 
5340

Elapsed MS - MVJ New 
-------------------- 
4616


Elapsed MS - MVJ Old 
-------------------- 
5260

Elapsed MS - MVJ New 
-------------------- 
4586

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources