Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Declaring a date with a format for prompting

Declaring a date with a format for prompting

Declaring a date with a format for prompting

I have the below stored procedure to which I want to add a date variable (@LAST_DTE) with a format of 'mm/dd/yy'. These @variables are user prompts and I want the user to enter the date in this format.

What is the proper way to format this declared @variable? Thank you.

USE [TmsEPly]
/****** Object: StoredProcedure [dbo].[TEST2_CUST_LOI_BASIC] Script Date: 7/26/2017 8:32:22 AM ******/
@LAST_DTE <<<<<<<<<< need formatting for user prompting
if @YR_CDE is not NULL and @TRM_CDE is not null ad @LAST_DTE is not null

RE: Declaring a date with a format for prompting

This is job for your front end, not for SQL Server SP.
Just declare it as Date or Datetime.

Borislav Borissov
VFP9 SP2, SQL Server

RE: Declaring a date with a format for prompting

I agree with Boris.

To clarify, when thinking about dates in SQL Server, there should not be any concept of "formatting". Internally, sql server stores DateTime as 2 byte integers. This is how the data is stored. Whenever you see a Date or DateTime value, it is always formatted in the display layer. For example, if you are using SQL Server Management Studio and you look at the values in a Date or DateTime column, SQL Server Management Studio will format that date in a certain way. Other applications (including yours) may format the date is other ways.

There are a lot of ways to format dates, and there's lots of ways to interpret dates. The best way to handle dates in SQL Server (and most other languages) is to store the data in a DateTime data type. When passing values to a stored procedure from your front end, you should use a command object. The stored procedures parameter data type should be Date (or DateTime).

By the way, it's usually up to the command object (on the front end) that interprets dates correctly. Presumably, it uses the language settings to do this. For example 1/2/2017 in the UK is interpreted as Feb 1 but in the US, it's interpreted as Jan 2. The command object handles this correctly. You should not try to do this in the stored procedure.

Microsoft SQL Server MVP
My Blogs
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

RE: Declaring a date with a format for prompting

There's not much to add, so to solve your problem you would rather consult a forum about the frontend you use.

Ideally you can take the user input as a date type in your front end language already, then pass that in as date type parameter and the point to care for the formatting then is when in your frontend the input string in 'mm/dd/yy' format is converted to a C# date variable, for example in a scenario you use an HTML form within an ASP.NET website.

If your front end doesn't even have a date type or isn't capable of parameterizing an ad-hoc SQL requests to MSSQL you'll write an SQL command as a string literal, then you best format your parameter as 'yyyymmdd', this is a string format MSSQL will implicitly (automatically) convert to a date, if you define @LAST_DTE as a T-SQL variable with date as data type.

So something like commandstring="exec [dbo].[TEST2_CUST_LOI_BASIC]('abcd','ab','20170727')" would work, when used as the core command to send to MSSQL for execution. Note: abcd and ab are just reflecting the data type definition of your first two parameters, I don't know their meaning, the advice is just about the string formatting of what to pass to the third parameter @LAST_DTE: '20170727' as 'yyyymmdd' will mean today's date, and that will work no matter how the locale of your frontend or SQL Server is configured. Any frontend language will be capable to reformat user input strings in 'mm/dd/yy' format to 'yyyymmdd'. The ttrickiest part is to turn the two digit year to a date with century, you'll have a turnover value like 50 or above meaning 19yy and 49 or below meaning 20yy or you simply ask for 'mm/dd/yyyy'.

Bye, Olaf.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close