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 |
|
syedripon
Starting Member
9 Posts |
 Posted - 2005-09-18 : 02:41:14
|
| I tried to insert current date into a column that is varchar(8). I don't need the time part of getdate() and my out put from table query should be like 20050917.How is possible |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-18 : 02:55:25
|
| Hi syedripon, welcome to SQL Team!There are a number of ways to strip off the time part of a date in SQL Server.If you just want a VARCHAR value then using CONVERT(varchar(nn), MyDate, mm) will do - where "nn" is the width you want and "mm" is a conversion "style" as shown in Books Online.However, if you want to process dates en masse then a "calculated date" is faster - i.e. converting from DateTime to Varchar and back to DateTime is slower.In that instance the method I use isDATEADD(Day, DATEDIFF(Day, 0, MyDate), 0)which gives the date, without the time.Some examples:SELECT CONVERT(varchar(8), GetDate(), 112)SELECT DATEADD(Day, DATEDIFF(Day, 0, GetDate()), 0)But it begs the question: Why on earth would you be storing a DATE in a VARCHAR column? You won't be able to do any useful comparison and manipulation of the data in that column without converting it to a datetime datatype first, and you will have a hard time ensuring that the column only contains values that are valid dates.Kristen |
 |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-09-18 : 13:53:58
|
| As Kristen alluded to, just change the table design. It's wrong. Just because you're storing the time doesn't mean you have to use it. It's best to store dates as dates, which in SQL Server means datetime. If you want to exclude time, have the stored procedure (hopefully, you're using stored procedures) or the app insert the exact same "time" for every single date. You can then use the CONVERT and CAST functions to show datetime as needed. This will allow you to take advantage of the builtin date functions in SQL Server, such as DATEPART, DATEADD, and DATEDIFF. Here is a little script that will give you all the possible combinations of date output using convert, although typically you want to do your formatting (displaying date as 20050808 for example) in the presentation layer.http://weblogs.sqlteam.com/derrickl/archive/2005/01/08/3959.aspxMeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
|
|
|
|
Date in SQL