0

I have a system that takes a view and allows a user to build an adhoc query but it relies on the data type of the field in order to provide the proper options (before, after, between, etc)

I have a date time field that I am using in a view, when I output the field in the system it looks like you'd expect a date time field to be formatted yyyy-mm-dd hh:mm:ss

What I want to be displayed is mm/dd/yyyy so I use

convert(varchar,myDate,101)

which works great but now my end system doesn't give me date options, it gives me varchar options (does not contain, begins with, etc)

So I try

cast(convert(varchar,myDate,101) as date)

I get the proper options again but that changes the format back to yyyy-mm-dd

My question is, how can I format the date to be mm/dd/yyyy and still maintain the date data type?

genericHCU
  • 4,394
  • 2
  • 22
  • 34
  • I think you should avoid conversion via sql server and get and send dates as date datatype. You should work on client regional settings or you application. Is your application a dot net app? Wich datatype do you use client side? – user_0 Jun 03 '15 at 12:13
  • 5
    A `datetime` has no format. So if you want a format you have to convert it to `varchar` (or `string` if you're formatting it with a language like C#). – Tim Schmelter Jun 03 '15 at 12:13
  • 2
    You can't do it in t-sql, since all date data types has no format. Use the presentation layer to display the dates in whatever format you want. – Zohar Peled Jun 03 '15 at 12:14
  • @TimSchmelter regardless that's the format being presented and not the one I'm after. – genericHCU Jun 03 '15 at 12:14
  • @ZoharPeled unfortunately the presentation layer is out of my control which is why i was hoping to find a sql solution. so be it. I'll see what I can do. thanks. – genericHCU Jun 03 '15 at 12:16
  • 1
    @Travis: you are a human being with eyes, so sql-server shows you the value of the datetime as text with seems to have a format. But actually it has no format and how it is displayed depends on database settings like the language. You could change it with `SET LANGUAGE` or `SET DATEFORMAT `. – Tim Schmelter Jun 03 '15 at 12:17
  • @TimSchmelter ah, thanks. – genericHCU Jun 03 '15 at 12:19
  • Sorry, but you are asking for something that is impossible. you can read in [this answer](http://stackoverflow.com/questions/30032915/how-to-cast-the-datetime-to-time/30033028#30033028) the short version of why it's impossible. – Zohar Peled Jun 03 '15 at 12:19
  • @ZoharPeled I didn't see that question when I searched. I suppose I need to close this question as it is essentially a duplicate. Thank you all for your time and helping me understand my situation. – genericHCU Jun 03 '15 at 12:22
  • @TimSchmelter is it possible to do a `set dateformat 'mm/dd/yyyy'` command before the view query? or a way to set it for the view in the create statement somehow? it looks like no and the questions I find always point to alternate solutions. thanks. – genericHCU Jun 03 '15 at 12:33
  • `set dateformat` will not help, neither will `set language`. – Zohar Peled Jun 03 '15 at 12:40

0 Answers0