| Author |
Topic |
|
dzirkelb
Yak Posting Veteran
53 Posts |
 Posted - 2009-01-08 : 10:36:56
|
| I have a date field that I want to default in this format in sql server 2000:mm/dd/yyyyI currently have a field with date type smalldatetime and the default value as getdate()However, it shows up as mm/dd/yyyy mm:hh:ss(I may be wrong on the time portion, but i don't wany any time portion at all).So, any ideas on how I can remidy this? Maybe something like Left(getdate(),10) or something? |
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2009-01-08 : 10:41:33
|
| dont worry about how sql stores it. if you dont want the time stamp, just remove it when you return the data.[Signature]For fast help, follow this link:http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspxLearn SQL or How to sell Used CarsFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
 |
|
|
dzirkelb
Yak Posting Veteran
53 Posts |
Posted - 2009-01-08 : 10:44:30
|
| the reason I am worrying about how it is stored is because I just made a migration from access to sql on this table. And, there are about 50 queries that join invoice date to another field...and they don't match up when there is a timestamp in teh field. So, I wish to just get rid of the timestamp portion when it is placed into the invoice date field.I have backup idea of running a query to pull all the invoices with a timestamp, and updating them to just a date, but I'd rather not do that if possible. |
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2009-01-08 : 10:47:11
|
would something like this help ?Select DateAdd(Day, DateDiff(Day, 0, GetDate()), 0) |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2009-01-08 : 10:47:19
|
Well, that is what i would do. just change the timestamp on all of them to zeros.SELECT dateadd(dd,datediff(dd,0,[DateTime Column Name]),0) as DateOnly from TblTableName just use that in an update on the tables. should run fairly fast.[Signature]For fast help, follow this link:http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspxLearn SQL or How to sell Used CarsFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
|
dzirkelb
Yak Posting Veteran
53 Posts |
Posted - 2009-01-08 : 10:50:37
|
| You guys aren't understanding what I'm wanting. I do not want to deal with teh sql statements...I Just want the default value to be mm/dd/yyyy...thats it, nothing else. If it isn't possible, then I'll make a workaround. |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2009-01-08 : 10:59:04
|
| We understand what you want. We are trying to keep you from making the mistake of changing your data type to varchar so you can see what you want to see, instead of storing the data correctly. You may just have to change all of your queries to pull the data correctly. OR, change how data is INSERTED into the table. OR, maybe a trigger that will change the timestamp on insert. There are plenty of options. Let us know which one sounds the most feasible, and we can try and help.[Signature]For fast help, follow this link:http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspxLearn SQL or How to sell Used CarsFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
|
dzirkelb
Yak Posting Veteran
53 Posts |
Posted - 2009-01-08 : 11:13:51
|
| Definatly dont' want to change to a varchar; however, I have an idea.What I can do is run a query in access to get all the invoices of the day, then update them to just a date...that will change them to just the date without the timestamp. That will be fine as the many queries run in a daisy chain and I'll just put it at the beginning. Thanks for your help guys :) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-08 : 11:23:36
|
quote:
Originally posted by dzirkelb
Definatly dont' want to change to a varchar; however, I have an idea.What I can do is run a query in access to get all the invoices of the day, then update them to just a date...that will change them to just the date without the timestamp. That will be fine as the many queries run in a daisy chain and I'll just put it at the beginning. Thanks for your help guys :)
nope. sql server will store the timepart as 00:00:00.000 because thats way it stored datetime value (always has a timepart. If this is enough, then even solution posted by Sakets ie. Select DateAdd(Day, DateDiff(Day, 0, GetDate()), 0) will be enough |
|
|
|
dzirkelb
Yak Posting Veteran
53 Posts |
Posted - 2009-01-08 : 11:44:19
|
| My workaround process seems to work.It takes the date()-1 to get the date needed (in access, it shows as dd/mm/yyyy). I then update all dates in my sql table where the date is >=date()-1 to date()-1...so it updates it to dd/mm/yyyy. My links now all work correctly with the dates.It may have the 00:00:00.000 timestamp in there, but it doesn't show. I don't really care if it shows or not, I'm just happy that it links correctly now. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-08 : 11:48:55
|
quote:
Originally posted by dzirkelb
My workaround process seems to work.It takes the date()-1 to get the date needed (in access, it shows as dd/mm/yyyy). I then update all dates in my sql table where the date is >=date()-1 to date()-1...so it updates it to dd/mm/yyyy. My links now all work correctly with the dates.It may have the 00:00:00.000 timestamp in there, but it doesn't show. I don't really care if it shows or not, I'm just happy that it links correctly now.
in that case, what was problem you faced with suggestion made on 01/08/2009 : 10:47:11? |
|
|
|
dzirkelb
Yak Posting Veteran
53 Posts |
Posted - 2009-01-08 : 12:18:03
|
| That is basically what i did, but not what I was wanting to do...I wanted to avoid that step. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-08 : 12:19:25
|
quote:
Originally posted by dzirkelb
That is basically what i did, but not what I was wanting to do...I wanted to avoid that step.
oh..ok |
|
|
|
|
Default Date for Field