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 |
|
giszzmo
Starting Member
29 Posts |
 Posted - 2015-01-22 : 12:29:39
|
I am writing a query where I need to display the max (most current) date, however, it also needs to be converted. I am not sure if I'm writing it wrong or putting in the wrong place. having MAX(convert(varchar(10), FileDate, 102)) |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-01-22 : 13:17:21
|
| HAVING what? It needs to be an expression, such as HAVING COUNT(*) > 1.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
giszzmo
Starting Member
29 Posts |
Posted - 2015-01-22 : 14:23:08
|
| Is this part at least correct? MAX(convert(varchar(10), FileDate, 102)) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-01-22 : 14:48:24
|
| I do not like the idea of using CONVERT for date/time columns since it's no longer date/time. Instead, use this to remove the time portion: DATEADD(dd, DATEDIFF(dd, 0, yourcolumn), 0)So instead, I would use this: MAX(DATEADD(dd, DATEDIFF(dd, 0, yourcolumn), 0))Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
giszzmo
Starting Member
29 Posts |
Posted - 2015-01-22 : 14:55:18
|
| That didn't work. It didn't get the max value. Its datetime in this format: 2015-01-12 06:30:00 |
|
|
|
giszzmo
Starting Member
29 Posts |
Posted - 2015-01-22 : 15:07:08
|
| Got it! I needed to make a subquery. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-01-22 : 15:11:19
|
quote:
Originally posted by giszzmo
That didn't work. It didn't get the max value. Its datetime in this format: 2015-01-12 06:30:00
But your convert is using style 102, which chops off the time portion. It's equivalent to what I posted, but what I posted doesn't change the data type. If it didn't work, then my assumption is that yours didn't either.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
viggneshwar
Yak Posting Veteran
86 Posts |
Posted - 2015-01-23 : 05:53:45
|
| convert(varchar(10), MAX(FileDate), 102)RegardsViggneshwar A |
|
|
|
|
|
|
|
|
Max Convert