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 |
|
BadBoy House
Starting Member
41 Posts |
 Posted - 2012-01-05 : 09:51:01
|
| Hi all.I need to create a query that will display date fields as follows:-1. Display the date as dd/mm/yyyy hh:mm:ss. For example, 01/01/2012 09:30:002. At any given time during the month, have separate query fields that show the date (in the format above) as at the first day of the month and the date of the last day of the month.Is this possible? I can't seem to find the correct code that will display the date in the format above. |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-01-05 : 10:01:39
|
1.select convert(varchar(20), getdate(), 103)+ ' ' + convert(varchar(20), getdate(), 108) 2.select dateadd(month, datediff(month, 0, getdate()), 0), dateadd(month, datediff(month, 0, getdate()) + 1, -1)
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
BadBoy House
Starting Member
41 Posts |
Posted - 2012-01-05 : 10:25:36
|
| That's almost perfect.The second portion of code just displays 01-Jan-2012 and 31-Jan-2012I need it to display the two dates in the following format:01/01/2012 hh:mm:ss31/01/2012 hh:mm:ss(where the date is relevant for the current month) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-01-05 : 10:31:25
|
| why do you need to do this in sql end? this is a presentation issue and you should be handling it in your front end application using date formatting functions.for second portion what should be time part value?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
BadBoy House
Starting Member
41 Posts |
Posted - 2012-01-05 : 10:48:18
|
| for the second portion, the time part value would really need to be 00:00:00I need this at the SQL end as we use with a third party system that the query results are passed to.As such, it requires the date format to be as above.So for example, if the query is run in January, it must show the date/time as follows:01/01/2012 00:00:0031/01/2012 00:00:00Thanks for your help. |
|
|
|
FischMan2
Yak Posting Veteran
59 Posts |
Posted - 2012-01-05 : 11:19:46
|
quote:
Originally posted by BadBoy House
for the second portion, the time part value would really need to be 00:00:00I need this at the SQL end as we use with a third party system that the query results are passed to.As such, it requires the date format to be as above.So for example, if the query is run in January, it must show the date/time as follows:01/01/2012 00:00:0031/01/2012 00:00:00Thanks for your help.
Why don't you just run the results of the datesadd function back through the convert? In other words, wrap the dateadd f(x) in a convert f(x). Just like with item 1.FischMan |
|
|
|
BadBoy House
Starting Member
41 Posts |
Posted - 2012-01-05 : 11:44:06
|
cheers. looking good now. below is the code I've used.thanks for your help!convert(varchar(20), dateadd(month, datediff(month, 0, getdate()), 0), 103) + ' ' + '00:00:00' 'CURRENT MONTH START',convert(varchar(20), dateadd(month, datediff(month, 0, getdate()) + 1, -1), 103) + ' ' + '00:00:00' 'CURRENT MONTH START' |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-01-05 : 11:54:06
|
quote:
Originally posted by BadBoy House
cheers. looking good now. below is the code I've used.thanks for your help!convert(varchar(20), dateadd(month, datediff(month, 0, getdate()), 0), 103) + ' ' + '00:00:00' 'CURRENT MONTH START',convert(varchar(20), dateadd(month, datediff(month, 0, getdate()) + 1, -1), 103) + ' ' + '00:00:00' 'CURRENT MONTH START'
why do you need to append 00:00:00 to end? anyways you're not considering time part right?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
FischMan2
Yak Posting Veteran
59 Posts |
Posted - 2012-01-05 : 11:59:59
|
quote:
Originally posted by visakh16
quote:
Originally posted by BadBoy House
cheers. looking good now. below is the code I've used.thanks for your help!convert(varchar(20), dateadd(month, datediff(month, 0, getdate()), 0), 103) + ' ' + '00:00:00' 'CURRENT MONTH START',convert(varchar(20), dateadd(month, datediff(month, 0, getdate()) + 1, -1), 103) + ' ' + '00:00:00' 'CURRENT MONTH START'
why do you need to append 00:00:00 to end? anyways you're not considering time part right?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
Glad it's working! (I contributed oh so much...  )@visakh16: He stated earlier that he has a legacy system the he has to send this data over to, and it requires the data to be in that format, or it won't work. What can you do in that situation except to comply with the requirements, no matter how silly they might seem?  Hands shaking. Too much coffee.FischMan |
|
|
|
|
|
|
|
|
How to get date format as dd/mm/yyyy hh:mm:ss?