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.
| Author |
Topic |
|
icw
Constraint Violating Yak Guru
378 Posts |
 Posted - 2010-07-13 : 09:40:08
|
| Hi I am converting a date to an int and then to a string, inorder to join it to other character fields to make a uniqueid.I think this is working ok.CAST(Cast(Sales_data.InvDate as int) as Varchar) as "DateNum"Can anyone verify that this will give a unique value for each date?Thanks |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-07-13 : 10:25:46
|
No. You will have rounding for PM times of dates.Use DATEDIFF(DAY, 0, InvDate) instead. It will give a numeric value equal to your's, but without rounding.
N 56°04'39.26"E 12°55'05.63" |
 |
|
|
Llycas
Starting Member
4 Posts |
Posted - 2010-07-13 : 10:34:29
|
| Sorry I think I misunderstood. Are you wanting to create unique id's based off the date?I may be going in the wrong direction, I guess it depends on what the purpose is ultimately for. But try this,datepart(month,[somedatefield]) AS [month]datepart(year,[somedatefield]) AS [year]Then select,([Year] * 100) + [month] AS [monthyear]it will create 6 digit numbers that are always going to be unique and are great for ordering and sorting purposes also.Sorry if I totally misunderstood your problem :-) |
|
|
|
icw
Constraint Violating Yak Guru
378 Posts |
Posted - 2010-07-13 : 11:03:30
|
| Thanks Llycas and Peso.I don't quite understand what is "Rounding of PM Times"thanks |
|
|
|
icw
Constraint Violating Yak Guru
378 Posts |
Posted - 2010-07-13 : 11:07:44
|
| I understand what you mean now.But I don't think its a problem as this app doesn't use times just the date |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-13 : 11:10:32
|
quote:
Originally posted by icwI don't quite understand what is "Rounding of PM Times"
Try it?select Cast(CONVERT(datetime, '20100713 00:00:00') as int) select Cast(CONVERT(datetime, '20100713 12:00:00') as int) select Cast(CONVERT(datetime, '20100714 00:00:00') as int) |
|
|
|
icw
Constraint Violating Yak Guru
378 Posts |
Posted - 2010-07-13 : 11:15:24
|
| Thanks KristenI get it now - cheers |
|
|
|
|
|
|
|
|
Converting a date to an int then to a string