Last post Apr 27, 2015 02:28 PM by limno
Apr 23, 2015 11:06 AM|StrangerMike|LINK
From a date I have to format another date that represents the first day of the month for that date.
So if the date is 12/31/2014, I have to also return a date of 12/01/2014.
Here is my string to do it, but it is a string, not a date data type.
How can I do this and return a date datatype.
SUBSTRING(CONVERT(varchar(10), A.[date email sent], 101), 1, 2) + '/01/' + SUBSTRING(CONVERT(varchar(10), A.[date email sent], 101), 7, 4) AS [month email sent]
Apr 23, 2015 11:17 AM|gimimex|LINK
DATEADD(DAY, 1 - DAY([date email sent]), [date email sent])
Hope this helps.
Apr 23, 2015 11:19 AM|Philosophaie|LINK
Here is an example of a SubString. Hope it helps.
aString = “0123456789”
first digit count from the left 0,1,2,3,…
second digit number of places to the right
Apr 23, 2015 11:21 AM|PatriceSc|LINK
This is a DATE (without time ?). DATEADD(day,-DAY(SomeDate)+1,SomeDate) should return the date for the first day of the month (it just substract the number of days and add 1 so it's back at day 1 for the same month).
Apr 23, 2015 11:32 AM|StrangerMike|LINK
it did work, but returned the date looking like: ' 2015-03-01 00.00.00.000'
Can I convert it further but return only the date portion and have it formatted like this and still maintain it as a date? 03/01/2015
Apr 23, 2015 11:35 AM|StrangerMike|LINK
this worked also, but is returning the date as 2015-03-01 00.00.00.000,
Can it be formatted to return the date like: 03/01/2015 ?
Apr 23, 2015 11:37 AM|StrangerMike|LINK
I tried this
email sent]), 0),101)
but not formatting the way it should.
Apr 23, 2015 11:51 AM|StrangerMike|LINK
this worked ( returns 03/01/2015), but is it still considered a date or is now a string?
email sent]), 0),101)
Apr 23, 2015 12:05 PM|PatriceSc|LINK
The point is that a date is just a number stored in a database or in memory. When you want to see the value for a date you necessarily have to convert this to a string to get a human understable value but then it is a string not a date.
I would suggest to not use convert. Just send the date back to your app and only when you are about to show the date value to a user you'll convert it to a string (and if needed you could show the same date value to users according to their own country convention
In short the date doesn't have a format as long as you keep it as a date. This is when you convert it to a string that you have to choose a convention suitable to a particuler user. So the point is to do this conversion as late as possible (ie to keep the
date as a date to format it as a string only when you are about to show the value).
Apr 23, 2015 01:06 PM|StrangerMike|LINK
It sounds like I will need to find another way. The date is actually being read internally by third party software that is looking to do a date comparison with another date.
The information I am feeding is not formatted the same. Thank you all for your time.
Apr 23, 2015 01:11 PM|PatriceSc|LINK
I would still format the date client side. You are writing the date to a text file? Just pick the format that is expected by the this 3rd party software ie yourDate.ToString(YourFormatString);
Or could it be an API or whatever? Starts rather from what this 3rd party software needs. Does it expect a date (ie you can pass the date native value to an API or whatever) or really a string?
If a string then you have to know which format is expected. For example .NET have the "InvariantCulture" which is intended to be universal (for example unlike other cultures it can't be customized, doesn't depend on OS or .NET updates) so that you are always
able to read back data formatted as strings using this culture.
Apr 24, 2015 02:26 PM|StrangerMike|LINK
the 3rd party software is ingesting a view created in Sql Server from a table to use as input for an analytics report.
Apr 27, 2015 02:28 PM|limno|LINK