| Author |
Topic |
|
bradshjw
Starting Member
4 Posts |
 Posted - 2009-06-05 : 09:56:12
|
| I am creating a variable @transDate by using the getdate() funcition. However, i need to have the date converted to an integer and remove the dashes, colons, decimals, and spaces of the datetime. How would go about removing those items? I need to keep the time on there so that each record will have a unique @transDate. |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-06-05 : 09:57:37
|
what not just use @transDate as datetime ? Why integer ?
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
bradshjw
Starting Member
4 Posts |
Posted - 2009-06-05 : 10:03:05
|
| because @transDate is going to be used to append to a specific to a unique integer which is then going to be inserted into a table column TransID which is an integer. And each record of TransID needs to be unique, so I thought that using the specific date and time that it was inserted would make each record unique |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-06-05 : 10:06:57
|
you want the date in YYYMMDD in integer or just a number ? select @transDate = convert(int, convert(varchar(10), getdate(), 112)) select @transDate = datediff(day, 0, getdate())
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
bradshjw
Starting Member
4 Posts |
Posted - 2009-06-05 : 10:22:50
|
| yes, the first query is the one I needed, but I also need the time included (hour, minute, second, millisecond) |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-06-05 : 10:29:59
|
Here's one ugly way:select left(replace(replace(replace(convert(varchar(23), getdate(), 121),'-',''),':',''),' ',''),14) Be One with the OptimizerTG |
|
|
|
bradshjw
Starting Member
4 Posts |
Posted - 2009-06-05 : 10:35:15
|
| Thanks, that works |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2009-06-05 : 12:03:14
|
quote:
Originally posted by bradshjw
<SNIP> And each record of TransID needs to be unique, so I thought that using the specific date and time that it was inserted would make each record unique
All I can say is be VERY carefull with this assumption. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-06-05 : 21:04:36
|
true . .. might as well us a GUID or identity column or maybe row_number() depending on the actual requirement
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
|
Removing dashes and colons from datetime