| Author |
 Topic  |
|
|
vmon
Yak Posting Veteran
63 Posts |
 Posted - 05/22/2006 : 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
United Kingdom
12543 Posts |
Posted - 05/22/2006 : 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 - 05/22/2006 : 12:16:17
|
They are just integers. How would I make them yyyy and dd format?
Thanks for you reply.
vmon |
|
|
|
nr
SQLTeam MVY
United Kingdom
12543 Posts |
Posted - 05/22/2006 : 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
Flowing Fount of Yak Knowledge
United Kingdom
1074 Posts |
Posted - 05/22/2006 : 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
United Kingdom
22191 Posts |
Posted - 05/22/2006 : 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 |
Edited by - Kristen on 05/22/2006 12:59:56 |
|
|
|
RyanRandall
Flowing Fount of Yak Knowledge
United Kingdom
1074 Posts |
Posted - 05/22/2006 : 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)
USA
7007 Posts |
Posted - 05/22/2006 : 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
India
22469 Posts |
Posted - 05/23/2006 : 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
Flowing Fount of Yak Knowledge
United Kingdom
1074 Posts |
Posted - 05/23/2006 : 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
India
22469 Posts |
Posted - 05/23/2006 : 09:27:21
|
Yes it is 
Madhivanan
Failing to plan is Planning to fail |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7007 Posts |
Posted - 05/23/2006 : 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 |
Edited by - Michael Valentine Jones on 05/24/2006 07:00:36 |
|
|
|
RyanRandall
Flowing Fount of Yak Knowledge
United Kingdom
1074 Posts |
Posted - 05/23/2006 : 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)
USA
7007 Posts |
Posted - 05/23/2006 : 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
India
22469 Posts |
Posted - 05/24/2006 : 03:27:00
|
Thanks MVJ for the tests
Madhivanan
Failing to plan is Planning to fail |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 05/24/2006 : 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)
USA
7007 Posts |
Posted - 05/24/2006 : 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)
Singapore
16769 Posts |
Posted - 05/24/2006 : 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))
KH
|
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7007 Posts |
Posted - 05/24/2006 : 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))
KH
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)
USA
7007 Posts |
Posted - 01/27/2007 : 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 |
|
|
| |
Topic |
|
|
|
Building a date
