SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Building a date
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vmon
Yak Posting Veteran

63 Posts

Posted - 05/22/2006 :  12:07:07  Show Profile  Reply with Quote
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

United Kingdom
12543 Posts

Posted - 05/22/2006 :  12:10:23  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

vmon
Yak Posting Veteran

63 Posts

Posted - 05/22/2006 :  12:16:17  Show Profile  Reply with Quote
They are just integers. How would I make them yyyy and dd format?

Thanks for you reply.
vmon
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 05/22/2006 :  12:33:59  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

RyanRandall
Flowing Fount of Yak Knowledge

United Kingdom
1074 Posts

Posted - 05/22/2006 :  12:42:11  Show Profile  Reply with Quote
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.
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 05/22/2006 :  12:45:40  Show Profile  Reply with Quote
- 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

Edited by - Kristen on 05/22/2006 12:59:56
Go to Top of Page

RyanRandall
Flowing Fount of Yak Knowledge

United Kingdom
1074 Posts

Posted - 05/22/2006 :  13:04:11  Show Profile  Reply with Quote
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.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 05/22/2006 :  14:04:22  Show Profile  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22777 Posts

Posted - 05/23/2006 :  03:04:09  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

RyanRandall
Flowing Fount of Yak Knowledge

United Kingdom
1074 Posts

Posted - 05/23/2006 :  06:33:45  Show Profile  Reply with Quote
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.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22777 Posts

Posted - 05/23/2006 :  09:27:21  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Yes it is

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 05/23/2006 :  11:15:56  Show Profile  Reply with Quote
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

Edited by - Michael Valentine Jones on 05/24/2006 07:00:36
Go to Top of Page

RyanRandall
Flowing Fount of Yak Knowledge

United Kingdom
1074 Posts

Posted - 05/23/2006 :  11:34:42  Show Profile  Reply with Quote
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.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 05/23/2006 :  12:17:23  Show Profile  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22777 Posts

Posted - 05/24/2006 :  03:27:00  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Thanks MVJ for the tests

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 05/24/2006 :  05:13:59  Show Profile  Reply with Quote
I wonder if I did a test if my result would have the best time?
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 05/24/2006 :  06:40:04  Show Profile  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17682 Posts

Posted - 05/24/2006 :  06:44:06  Show Profile  Reply with Quote
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))



KH

Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 05/24/2006 :  11:41:10  Show Profile  Reply with Quote
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))



KH





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


CODO ERGO SUM
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 01/27/2007 :  16:26:50  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000