-2

Ok this is how stupidly i am keeping my date as string

23.12.2012 21:24:31

Now at a select query i want to cast them as BigInt and do a search

casted version : "20121223212431"

like below

select * from myTable where cast(datestring as bigint) > 20111223212431

How can i cast that kind of string to BigInt at sql server ?

Edit - why this query is not working

    select (convert(datetime,LastMoveTime,104)) as myTime from myTable
where myTime < DATEADD(day,-366,GETDATE())

error : Invalid column name 'myTime'.

Charles
  • 50,943
  • 13
  • 104
  • 142
Furkan Gözükara
  • 22,964
  • 77
  • 205
  • 342

2 Answers2

5

Firstly, it would be best to just fix the schema to use DATETIME instead.

If you can't do that, you can use a CONVERT expression to convert to DATETIME instead. You just need to find the right format. (If your string value isn't already in a supported format, you may need to do some work...) That's what the value is, after all - why use BigInt at all?

Note that if you correct the schema to use an appropriate data type, it's likely to be much more efficient than having to convert it all over the place. For example, an index on the string column won't help much for that sort of query, whereas if you can change it to an indexed DATETIME value, the index can do most of the work.

Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194
  • You are right about fixing the scheme. But it is not possible just right now. So for temporary i need to cast it. It can be int too. After all i will do day search. DateTime would also work but can you also show how to do day based search ? – Furkan Gözükara Dec 24 '12 at 15:09
  • if i try this it fails : convert(datetime,datestring). yes i know i should keep it as datetime. but for temporary i need to cast :D – Furkan Gözükara Dec 24 '12 at 15:11
  • @MonsterMMORPG: Why cast to an *int* though? Why convert from one type which doesn't represent the real data to another type which doesn't represent the real data? See the linked documentation for the supported formats - you may need to perform some string operations on your data to get the conversion right. – Jon Skeet Dec 24 '12 at 15:11
  • cast date would work too. i just don't know how to query datetime at sql server :) – Furkan Gözükara Dec 24 '12 at 15:11
  • (Oh, and I would *really* suggest doing the work to fix the schema straight away. Any temporary measures you need to do will be *entirely wasted* work.) – Jon Skeet Dec 24 '12 at 15:12
  • @MonsterMMORPG: As I said before, read the linked documentation. You can probably take the first 10 characters as a substring, replace "." with "/" and then use format 103. – Jon Skeet Dec 24 '12 at 15:13
  • yes i just noticed gearman working thanks. i should fix scheme as you said. you are right. – Furkan Gözükara Dec 24 '12 at 15:14
  • thanks for answer. i edited the question can you check again please ? – Furkan Gözükara Dec 24 '12 at 15:36
  • @MonsterMMORPG: Well you're not doing any string operations as far as I can tell - you need to take a substring, and unless you're using the German format (which appears to require a 2-digit year, not 4) you'll need to replace `.` with `/` as well. Basically, you've got to convert your unfortunate string format into one that SQL server understands. Or work *now* on fixing the schema, to save yourself all this extra work... – Jon Skeet Dec 24 '12 at 15:37
  • thanks for answer again. well i meant this one : error : Invalid column name 'myTime'. otherwise it is converting to date when i gave it german time – Furkan Gözükara Dec 24 '12 at 15:41
  • @MonsterMMORPG: I'm not sure whether you can use the "new" column name like that - you may need to repeat the conversion expression in the `where` clause instead (and trust the optimizer to only do the conversion once). – Jon Skeet Dec 24 '12 at 15:43
1

You should not do that, see John Skeet's post, but

declare @dt datetime
select @dt='20121223 21:24:31'

Select Cast(Convert(Varchar(25),@dt,112)+Replace(Convert(Varchar(25),@dt,108),':','') as BigInt)
bummi
  • 27,123
  • 14
  • 62
  • 101